Improve Microsoft Access project performance

In a client/server application, the general rules of thumb for reducing network traffic and optimizing application performance are:

• Do your data processing work on the server.

• Minimize roundtrips to the server to access the data.

• Limit the data you retrieve from the server.

Understanding OLE DB and the client/server environment

A Microsoft Access project uses OLE DB to connect and communicate with a Microsoft SQL Server database. Microsoft designed the OLE DB architecture to work efficiently in a client/server environment.

When you access data through a form or datasheet, OLE DB retrieves an updateable snapshot recordset from the SQL server database (up to the maximum record limit) and caches the data on the client ù making one roundtrip to the server. Access retrieves the data asynchronously, which means that you can continue to interact with Access while the data is being loaded. When you work with the data on the form or datasheet, whether browsing, filtering, sorting, finding or updating data, you are working with the data that is cached on the client.

Processing data on the server

To process data on the server, you can use stored procedures, triggers, user-defined functions, and SQL SELECT statements. You can also sort data on the server before retrieving it.

Stored procedures are a precompiled collection of one or more SQL statements and optional control-of-flow statements that can accept parameters and are processed as a single unit by Microsoft SQL Server. A stored procedure resides in the SQL Server database. Because a stored procedure is compiled on the server when it is created, it executes faster than individual SQL statements. Because a stored procedure runs on the server, it takes advantage of the extra processing power of the server.

Triggers are a special kind of stored procedure that automatically execute when you update, insert, or delete data. Use triggers to enforce business rules and restrictions that are more complex than those defined with check constraints. Unlike check constraints, triggers can reference columns in other tables. For example, a trigger can roll back updates that attempt to apply a discount (stored in a discounts table) to books (stored in a titles table) with a price of less than $10.

User-defined functions combine the best features of stored procedures and views into a single query that you can nest, pass parameters to, sort, and return values. In many cases, user-defined functions are a better alternative to stored procedures because you can return a single table of data or scalar value, hide the business logic and details of how that data or scalar value was created from the user, and simplify the complexity of your SQL statement syntax.

Sort records on the server by using a stored procedure, user-defined function, or SQL statement stored in the RecordSource property of a form or report (which is stored in the Access project, but executes on the server). Sorting records on the server is another way to take advantage of the extra processing power of the server.

Limiting data and property information retrieved from the server

As much as possible, limit the amount of data your application retrieves from the SQL Server database. Use views, stored procedures, user-defined functions, server filters, and SQL statement WHERE clauses to limit the data you or your users see in a form or datasheet. In general, avoid designing application scenarios where users browse the database in unrestricted fashion.

You can use the maximum record limit button or the MaxRecords property to limit the data you or a user can see in a form or datasheet.

You can also use server filters to limit records before they are retrieved from the database on the server, and filters to further limit records on the client. You can define a server filter in the ServerFilter property of a form or report. You can also optimize the performance of lists of values in Server Filter by Form and Filter by Form windows by modifying the FilterLookup property to control the number of values displayed if the values are retrieved from either the server or client.

Disable automatic row fix-up if not required. After you update a record in a SQL Server 2000 database, Access normally resynchronizes the data to reflect the changed values. However, these operations require additional round trips to the server. You can disable row fix-up by setting the PerformResync property to No.

Disable pre-fetching of default values if not required. By default, when you display a form or datasheet, Access shows the default values in the new row of the datasheet and the form controls for new records on forms. This requires a round-trip to the server. You can disable the pre-fetching of default values by setting the Fetch Defaults property to No.

Optimizing the SQL Server database

The following are general guidelines for optimizing SQL Server database performance. For detailed information on improving SQL Server database performance, see the SQL Server documentation.

• Design tables without redundant data. A well-designed database is a prerequisite for fast data retrieval and updates.
• Enforce referential integrity, which preserves the defined relationships between tables when you add, update, or delete the rows in those tables by defining primary key and foreign key constraints in the related tables.
• Choose an appropriate data type for fields. This can save space in your database and improve join operations. When defining a field, choose the smallest data type or field size that's appropriate for the data in the field.
• Create indexes for fields that you sort, join, or set criteria for. You can make dramatic improvements in the speed of a query by indexing fields that are on both sides of joins, and fields that are used to set criteria for the query. Indexes, however, can slow down the speed of record insertions, updates, and deletes.
• Use indexed views in SQL Server 2000 databases. Indexed views can greatly improve the performance of views found in data warehouse or other decision support applications where large amounts of data are aggregated into sums and averages. An indexed view forces the view's results to be stored in the database, so statements that reference the view will have substantially better response times. When using indexed views, you should be aware that modifications to the base data are automatically reflected in the view, which may cause additional maintenance overhead.
• Add a timestamp field to your tables to improve performance of record deletions and updates, especially if your table contains many fields. SQL Server uses a timestamp field to indicate that a record was changed (not when it was changed) by creating a unique value field, and then updating this field whenever a record is updated. SQL Server uses the value in timestamp fields to determine whether a record has been changed before updating it. In general, a timestamp field provides the best performance and reliability, especially in a multiuser environment. Without a timestamp field, SQL Server must check all the fields in the record to determine if the record has changed, and this check can slow performance.
• Periodically compact your SQL Server database to save disk space and reorganize indexes and data for faster access.

Optimizing linked tables created in the Link Table Wizard

 If the SQL Server database you are linking to is on another SQL Server, create a linked server (select Linked SQL on the first page of the Link Table Wizard). SQL Server, in most cases, will optimize joing operations.

Create an OLE DB data source that uses Transact SQL functions (select Transact SQL on the first page of the Link Table Wizard) if you are linking to Excel, text, or HTML files because the performance is generally better than creating a linked server.

Optimizing forms

When you design a form, determine the data and functionality needed, and then delay asking the server for this data and functionality until the information is requested by the user. For example, create a form so that it doesn't require data to be retrieved from the server during the form-opening process. For example, you can add a command button to the form to load data on the form.

Use batch transactions which are more efficient when you are updating related rows at once. For example, instead of making four round trips to the server to enter one order and three line items, a batch transaction enters the order and line items in a single round trip to the server.

Use SQL UPDATE and DELETE statements with the appropriate WHERE clause to update or delete multiple records that meet the same criteria. This is more efficient than opening a recordset and performing the updates or deletions one record at a time.

Display fields that take a long time to retrieve data from the server, such as text and image fields, only when requested. You can use the following techniques:

• Place text and image fields on a subform that uses the same record source. Access only retrieves the text or image values for the current record.
• Display the most important fields on a main form, and provide a button labeled "More Information" that opens another form containing other fields. Base the second form on a query that is passes as a parameter the primary key field on the main form.
• If your application is data-entry intensive, such as an order entry system, create a specific form for adding records that has the DataEntry property set to Yes. When you open the form to enter new data, Access doesn't display any existing records. This saves time because Access doesn't have to retrieve all the records from the server.

Optimizing reports

Instead of printing a long report, break up reports into smaller subsets of data by using views, stored procedures, user-defined functions, and SQL statements as your record source, or by using filters.

Use layout preview instead of print preview when you design your reports. You can take a quick look at the layout, which includes just a sample of the data in layout preview. When you are done working on the layout of the report, verify the data by opening the report in print preview.

You typically create and distribute reports on a schedule according to regular business cycles, such as weekly stock-level reminders, monthly sales reports, or quarterly financial statements. Create and distribute these reports as report snapshots in a batch file. Users can use the Snapshot Viewer to view and print the reports. When your users need up-to-the-minute reporting, use grouped data access pages.