ACC2000: Optimizing for Client/Server Performance

This article lists tips to optimize the performance of Open Database Connectivity (ODBC) data sources in Microsoft Access.

NOTE: This article assumes that you are familiar with client/server environments and architectures.

Methods of Accessing Data

To access server data with a client/server application, you can use one or more of the following methods:

• Linked tables and views
• SQL pass-through queries to send SQL statements directly to the server
• An Access project

Linked Tables and Views

The most efficient way to handle server data is to link tables and views from your server. Microsoft Access stores field and index information for linked tables. This improves performance when you open the tables.

NOTE: If you make changes to fields or indexes on the server, you must relink the remote tables in Access.

Running SQL Pass-Through Queries

In many applications, you can create Microsoft Access queries based on remote tables and also create SQL pass-through queries. Pass-through queries offer the following advantages:

• Microsoft Access does not compile a pass-through query. It sends the query directly to the ODBC server. This speeds up the application.
• Access sends the pass-through query to the server exactly as it was entered. Therefore, more processing occurs on the server and less data is exchanged over the network.
• Forms, reports, and Microsoft Access queries can be based on pass-through queries using ODBC linked tables.
• You can use SQL Server-specific functionality (such as stored procedures and server-based intrinsic functions) that has no equivalent in code.
• Update, delete and append pass-through action queries are much faster than action queries based on linked remote tables, especially when many records are affected.

Pass-through queries have the following disadvantages:

• An SQL pass-through query always returns a snapshot that cannot be updated. A Microsoft Access query usually returns a dynaset that reflects other users' changes and can be updated.
• You are required to type the commands directly into the SQL Pass-Through Query window with the syntax that your server requires. You cannot design pass-through queries in the query design grid.
• If you require a parameter with a pass-through query, you must run the query in code and modify the query's SQL property. For additional information and an example of how to modify the SQL property, click the article number below to view the article in the Microsoft Knowledge Base:
  232493  (http://support.microsoft.com/kb/232493/EN-US/ ) ACC2000: How to Simulate Parameters in an SQL Pass-Through Query

Request Less Data from the Server

Requesting data from the server costs processing time. To optimize performance, request only those records and fields that you need.

Reduce the number of bound controls, such as text boxes, combo boxes, list boxes, and subforms. When a form is opened, each of these controls sends a separate query to the server.

If the linked ODBC tables have Memo or OLE object fields, you can set the Visible property of the fields to No and add a toggle button to set the Visible property to Yes so that users can choose to view it.

Some linked tables do not change frequently. For example, a table containing the names and abbreviations of the 50 states would not change often. You can speed up form loading and reduce server load by storing a local copy of these tables. You can also provide a way to update the local copy with more recent data from the server.

Optimizing Queries

The principal method for optimizing queries (after adding appropriate indexes on the server) is ensuring that processing takes place on the server. You should avoid functionality that is not supported by the server, such as functions specific to Microsoft Access or user-defined functions. For detailed information about what must be processed locally, see the Microsoft Jet Database Engine ODBC Connectivity white paper (for more information about this white paper, see the "Microsoft Jet Database Engine ODBC Connectivity White Paper" section later in this article).

To see the Select statement that is being sent to the server, you can set the TraceSQLMode setting. To set this, follow these steps:

1. Click Start menu, and then click Run.
2. In the Open dialog box, type Regedit.
3. Expand the HKEY_LOCAL_MACHINE subtree until you locate the SOFTWARE\Microsoft\Jet\4.0\Engines subkey.
4. Click Engines.
5. If the TraceSQLMode value does not appear in the right pane, click New on the Edit menu, and then click DWORD Value.
6. Under Name in the right pane, type TraceSQLMode for the new value.
7. With the new value still selected, click Modify on the Edit menu.
8. Under Base, select Decimal. In the Value data box, type 1, and then click OK.

For more information about the TraceSQLMode setting, click Microsoft Access Help on the Help menu, type "TraceSQLMode" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

TryJetAuth Setting

The TryJetAuth setting controls whether Microsoft Access first attempts to log on to your server using the Microsoft Access login ID and password. (By default, the Microsoft Access login ID is "admin" and the password is blank.) If this fails, Microsoft Access prompts you for a logon ID and password. Unless you have set up Microsoft Access to use the same logon ID and password as your ODBC server, add the line "TryJetAuth=0" to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ODBC\TryJetAuth key of the registry. This prevents Microsoft Access from attempting to log on with the wrong ID. To do this, follow these steps:

1. Click Start, and then click Run.
2. In the Open dialog box, type Regedit.
3. Locate the HKEY_LOCAL_MACHINE subtree.
4. Expand the HKEY_LOCAL_MACHINE subtree until you locate the following subkey:
   SOFTWARE\Microsoft\Jet\4.0\ODBC\TryJetAuth
5. Click Engines.
6. If the TryJetAuth value is not there, click New on the Edit menu, and then click DWORD Value.
7. Under Name in the right pane, type TryJetAuth for the new value.
8. With the new value still selected, click Modify on the Edit menu.
9. Under Base, select Decimal. In the Value data box, type 1, and then click OK.

Timestamps

On servers that support them such as Microsoft SQL Server, timestamp fields make updating records more efficient. Timestamp fields are maintained by the server and are updated every time that the record is updated.

If you have a timestamp field, Microsoft Access needs to check only the unique index and the timestamp field to see whether the record has changed since it was last retrieved from the server. Otherwise, Microsoft Access must check all the fields in the record. If you add a timestamp field to an linked table, relink the table in order to inform Microsoft Access of the new field.

Transactions

Using transactions in Visual Basic for Applications (VBA) when you update or insert records on linked tables can improve performance. Transactions enable the Jet database engine to accumulate multiple updates and write them as a single batch. With Microsoft SQL Server, keep transactions short because they generate locks that prevent other users from reading data affected by the current transaction. Although you can nest transactions with VBA, most servers do not support nested transactions. Microsoft Access sends only the first-level transaction to the server.

Optimizing Recordsets and Code

You can store all or part of the data contained in Recordset objects of the Dynaset type in local memory by setting the CacheSize and CacheStart properties.

The cache size can be between 5 and 1200 records. If the size of the cache exceeds available memory, the excess records spill into a temporary disk file. Applying the FillCache method fills the cache with server data. To recover the cache memory, set the CacheSize property to zero.

With Recordset variables, implement only the functionality that you need. For example, add the dbAppendOnly option with the OpenRecordset method if you only need to add new records to a recordset. If you do not need editing or updating ability, base your recordsets on SQL pass-through queries. The fastest method to insert and update records is to use SQL pass-through queries in code.

MSysConf Table

You can create a table named MSysConf on your server to control background population (the rate at which Microsoft Access reads records from the server during idle time). With this table, you can set the number of rows of data that are retrieved at one time and the number of seconds of delay between each retrieval. If you experience excessive read-locking on the server, you can adjust the table settings to increase background population speed. If too much network traffic is generated by background population, you can adjust the settings to slow it down.

Microsoft Jet Database Engine ODBC Connectivity White Paper

An important source for additional information is the "Jet Database Engine ODBC Connectivity" white paper. This document was written for Jet 3.0; however, this information still applies to Jet 4.0. This document discusses the Microsoft Jet 3.0 database engine and how it uses ODBC. The white paper can be downloaded from the following Microsoft Web site:

An Access Project

An Access project uses OLEDB to link to Microsoft SQL Server 6.5 or 7.0. If all of the tables in your Access database are linked tables to SQL Server, to enhance performance you can instead create an Access project. However, if your database contains any Access tables, this option is not possible.