Optimizing Client/Server Performance

In addition to functional differences between local and remote tables, there are often considerable performance differences. Some parts of your application that perform well with local data may be significantly slower when the data is on a server, and may cause too much network traffic or use excessive server resources. This section discusses techniques you can use to improve the performance of client/server applications.

See Also For information on optimizing any application, see Chapter 13, “Optimizing Your Application.”

Speeding Up Queries

The best way to improve the performance of queries on remote data is to have the server run as much of the query as possible. Microsoft Access attempts to send the entire query to the server, but evaluates locally any query clauses and expressions that aren’t supported by servers in general or by your server in particular. Some information about server capability is available when you link a remote table.

If a query contains expressions that can’t be evaluated by your server, more or less of the query is evaluated locally, depending on where the expressions occur. Queries with such expressions in the SELECT clause are still evaluated on the server, unless they occur in a totals query, a union query, or a query that uses the DISTINCT predicate, in which case they are evaluated locally. Such expressions in other clauses (for example, WHERE, ORDER BY, and GROUP BY clauses) cause at least part of the query to be evaluated locally.

This section describes how to design queries that maximize the use of the server and thereby improve query performance.

Using Supported Functionality

If possible, don’t design queries that use functionality not generally supported by servers. The following sections identify some areas of functionality that aren’t generally supported and others that usually are.

Functionality Not Supported by Most Servers

Most servers don’t support:

Operations that can’t be expressed in a single SQL statement—for example, when a query’s FROM clause contains a totals query or a query that uses the DISTINCT predicate. Often, you can rearrange your queries so that they calculate totals or apply the DISTINCT predicate after all other operations.
Operations that are Microsoft Access–specific extensions to SQL, such as complex crosstab queries, queries that use the TOP predicate or the TopValues property, and reports that have multiple levels of grouping and totals.
Expressions that contain operators or functions specific to Microsoft Access. For example, the Microsoft Access financial and domain aggregate functions have no server equivalents.
User-defined functions in Visual Basic that take remote fields as arguments. These functions don’t exist on the server, but must process remote field data.
Heterogeneous joins between local tables and remote tables, or between remote tables in different ODBC data sources.
See Also For more information, see “Heterogeneous Joins” later in this section.
Expressions or union queries that mix text and numeric data types. Most servers lack the data type leniency of Microsoft Access. If you need to mix data types, use explicit conversion functions.

See Also For a list of DAO objects and methods that aren’t supported for use with external data sources that don’t use Microsoft Jet, see “Unsupported Objects and Methods” in Chapter 18, “Accessing External Data.”

Functionality Supported by Some Servers

Servers differ in certain areas of functionality. Some servers support:

Outer joins. (Note that Microsoft Access doesn’t send multiple outer joins to a server, although many inner joins can accompany a single outer join.)
Numeric, string, and date/time functions (such as the Log, Mid$, and DatePart functions).
Conversion functions (such as the CInt, CStr, and CVDate functions).

If the operation is supported, it’s sent to the server for processing; otherwise, it’s performed locally.

Splitting WHERE Clause Restrictions

If a query’s SQL statement includes a WHERE clause that the server can’t evaluate completely, Microsoft Access splits the clause into several parts (linked by the AND operator), and sends as many of these parts to the server as possible. For example, suppose you write a function in Visual Basic called ProcessData and run the following query:

SELECT field1, field2, field3
	FROM table1
	WHERE (field1 = 100 OR field1 = 200)
	AND ProcessData(field2) > 100
	AND field3 LIKE "A*"

Microsoft Access sends the following query to the server and evaluates ProcessData(field2) > 100 locally on the records returned.

SELECT field1, field2, field3
	FROM table1
	WHERE (field1 = 100 OR field1 = 200)
	AND field3 LIKE "A*"

If you need to use a WHERE clause restriction that your server can’t process, you can limit the amount of data Microsoft Access requests by providing additional restrictions that your server can process. For maximum efficiency, these restrictions should involve fields indexed on the server.

Optimizing Expressions That ContainMicrosoft Access–Specific Elements

If expressions in your queries contain user-defined functions, domain aggregate functions, and other elements specific to Microsoft Access (such as the IIf and Choose functions), they can’t be completely run by a server. If an expression that uses one of these elements involves remote data fields, Microsoft Access runs it locally and retrieves all necessary data from the server. If such an expression involves only constants and query parameters, however, Microsoft Access evaluates it just once and sends the result to the server as a query parameter.

Whenever possible, optimize your queries by avoiding references to remote fields in expressions that can’t be evaluated by the server. For example, suppose you have a remote Tasks table with a Status field that contains code numbers (1 means unfinished and 2 means complete). The following query requires a user to type 1 or 2, which isn’t very intuitive.

SELECT * FROM Tasks
	WHERE Status = [What Status?]

In contrast, the following query allows the user to type Unfinished or Complete when prompted.

SELECT * FROM Tasks
	WHERE [What Status?] = IIf(Status=1,'Unfinished','Complete')

However, because the IIf function uses the remote Status field, Microsoft Access sends the first part of the statement (SELECT * FROM Tasks) to the server and evaluates the restriction on each record locally.

The following query also allows the user to type words instead of numbers.

SELECT * FROM Tasks
	WHERE Status = IIf([What Status?]='Unfinished',1,2)

However, because the return value of the IIf function is effectively constant, Microsoft Access evaluates it once locally and sends the following statement to the server, supplying this part of the expression as a parameter value.

SELECT * FROM Tasks
	WHERE Status = ?

This method is more efficient and reduces network traffic.

Heterogeneous Joins

To perform a heterogeneous join—a join between local and remote tables—Microsoft Access either:

Requests all the records in the remote table or tables and performs the join locally.
Performs a remote index join. This means that for each key in the local table, Microsoft Access asks the server only for the records with a matching key value.

Microsoft Access uses a remote index join only if the remote field being joined is indexed and if the local table is considerably smaller than the remote table. For example, to join a local table called Employees that contains 10 records and a remote table called Tasks that contains 50 records on the EmployeeID field, Microsoft Access retrieves the 50 records from the Tasks table on the server and processes the join locally. However, if the remote table contains 1,000 records, Microsoft Access performs a remote index join. It sends 10 of the following queries to the server, supplying each EmployeeID value from the local Employees table as a parameter value.

SELECT EmployeeID, TaskID
	FROM Tasks
	WHERE EmployeeID = ?

This is generally much faster than retrieving all 1,000 records.

Always index the remote join field so that Microsoft Access can perform a remote index join when appropriate. Regardless of whether or not Microsoft Access can perform a remote index join, you can improve the performance of a query that uses a heterogeneous join by supplying additional restrictions on remote fields. Microsoft Access sends these restrictions to the server, limiting the amount of data requested for the join.

Speeding Up Forms

When you design a form based primarily on server data, take a minimalist approach for the best performance. Determine what data and functionality you need, and design forms that delay asking for this data and functionality until requested by the user. This section presents several minimalist techniques for improving the performance of your forms.

Requesting Less Data

The more data you request from the server, the more processing time you use and the more network traffic you create. To request less data in your forms:

Request as few records as necessary. For example, rather than opening a large Recordset object and navigating with one of the Find methods, use a filter or query to limit the size of the Recordset, being careful to use restrictions that the server can process.
Base your forms on queries that select as few remote fields as necessary. That way, only fields used on the form are actually retrieved from the server. If you need to add fields to the form later, you can use the Query Builder (available from the RecordSource property box in the property sheet for the form).
Use fewer bound controls, such as text boxes, list boxes, and combo boxes. Each form control that is bound to a remote field requires a separate query to be sent to the server when the form is opened. In particular, avoid controls that contain totals, and list boxes and combo boxes with large row sources for the list.

Displaying Fields Only on Request

In some cases, you may want a form to display all the fields in a record. In other cases, you may not need to make all the fields available to the user. To speed up the processing of your forms, you can use the following techniques to avoiding retrieving unnecessary data:

Display the most important fields on a main form and provide a button to open another form that contains the other fields. Base the second form on a query that uses the primary key value on the main form as criteria. For example, suppose you have a main form based on the following query:
```
SELECT OrderID, CustomerID, OrderDate, ShipVia, ShippedDate
	FROM Orders
```
You could base the second form on the following query, which runs only when the user clicks a button to request more information:
```
SELECT Orders.*, Customers.*, Employees.*
	FROM (Orders INNER JOIN Customers
		ON Orders.CustomerID = Customers.CustomerID)
		INNER JOIN Employees
		ON Orders.EmployeeID = Employees.EmployeeID
	WHERE OrderID = Forms!Orders!OrderID
```
Display Memo and OLE Object fields on a form only when the user requests to see the information. Because Microsoft Access doesn’t retrieve Memo and OLE Object fields until they are displayed on the screen, waiting to display this data until it is needed improves performance. You can place Memo and OLE Object fields off screen on another form page. Add a label to the form, such as “Scroll down to see notes and pictures,” that tells the user how to display the information.
Set the Visible property to No for controls bound to Memo and OLE Object fields, and add a toggle button or command button that sets the property to Yes when the user wants to view the contents of these controls.

Downloading Data You Use in More Than One Form

Often, an application contains several forms that use the same remote table—for example, as the source for a list box or combo box. If the data in the table doesn’t change frequently, you can speed up form loading and reduce server load by using one of the following techniques:

If the data in the table never changes (such as the names and abbreviations of states or regions), store the table in the local Microsoft Access database. If the table is joined in queries with remote tables, also keep a copy of it on the server to avoid heterogeneous joins.
If the data in the table changes rarely (such as a list of the buildings that a company owns), store the table both on the server and in the local Microsoft Access database. Your application must provide a way for the user to download the current data when it changes.
If the data in the table changes occasionally (that is, less than daily, such as the employees in a small company or department), store the table both on the server and in the local Microsoft Access database. When your application starts, it must check to see if the tables are out of sync and download the current data from the server. This method uses extra time when your application starts, but your queries run faster.

In the following example, the LclDepts and LclEmps tables are local versions of the linked server tables RmtDepts and RmtEmps. The example empties the LclDepts and LclEmps tables and then repopulates them with the contents of the tables on the server.

Dim wrkRemote As Workspace, dbsRemote As Database

Set wrkRemote = DBEngine.Workspaces(0)
Set dbsRemote = wrkRemote.Databases(0)

wrkRemote.BeginTrans
	With dbsRemote
		.Execute "DELETE FROM LclDepts"
		.Execute "INSERT INTO LclDepts SELECT * FROM RmtDepts"
		.Execute "DELETE FROM LclEmps"
		.Execute "INSERT INTO LclEmps SELECT * FROM RmtEmps"
	End With
wrkRemote.CommitTrans

This example uses the BeginTrans and CommitTrans methods to make the downloading operation a transaction. This delays writing any data to disk until the CommitTrans method runs.

See Also For more information on using transactions, see “Using Transactions” later in this chapter.

Using Less Functionality

To improve the performance of your client/server application, you may want to avoid using some of the more powerful features of Microsoft Access. This section discusses when you can improve performance by using less functionality in your forms.

Snapshots vs. Dynasets

Microsoft Access provides two types of Recordset objects on which you can base your forms: dynasets, which can be updated, and snapshots, which cannot. If you don’t need to update data with your form, and the Recordset contains fewer than 500 records, you can reduce the time it takes the form to open by setting the form’s RecordsetType property to Snapshot. This causes Microsoft Access to use a snapshot-type Recordset object as the record source for the form, which is generally faster than using a dynaset.

Note If you’re opening a snapshot in Visual Basic and need to make only one pass through the results, you can make the snapshot even more efficient by using the dbForwardOnly constant of the OpenRecordset method. This constant opens a forward-only-type Recordset object. For more information on forward-only-type Recordset objects, see Chapter 9, “Working with Records and Fields.”

However, if the Recordset you’re using is large, or if it contains Memo or OLE Object fields, using a dynaset is more efficient. If you use a dynaset, Microsoft Access retrieves only the primary key of each record; if you use a snapshot, it retrieves the entire record. A dynaset is also more efficient because Memo and OLE Object fields are retrieved only if they are displayed on the screen or directly referenced in your code.

Multiple-Table Queries vs. Subforms

Two methods are available for displaying information from multiple tables in a form: you can either base the form on a query that joins the tables, or you can place one or more subforms on the form. The first approach usually sends a single query to the server, which creates the join. The second approach sends at least two queries: one to retrieve the data for the main form and one for each embedded subform. This second approach requires more time for the form to load as well as more overhead.

Because you can update and insert data in almost every field of a multiple-table query, a subform is often unnecessary. If you don’t need the convenient display capabilities of a subform, base your form on a single query for better performance.

Improving the Performance of Updates and Deletions

Probably the simplest way to improve the performance of updates and deletions—especially if your remote table has many fields—is to add a version field (sometimes called a timestamp) to the remote table. A version field is maintained by the server, and its value automatically changes each time the record is updated. Typically, you can’t read the contents of a version field, but Microsoft Access uses it to detect changes in records.

When updating or deleting a record, Microsoft Access checks to see if the version field has changed. If it has, the update or deletion is canceled to avoid overwriting another user’s changes. If the table doesn’t have a version field, Microsoft Access compares all the old field values to their current values in order to determine whether data has changed. This is less efficient and generates more network traffic. Moreover, when Microsoft Access checks values, data that hasn’t changed occasionally appears as if it had, either because of inaccuracies inherent in comparing floating-point numbers, or because Memo and OLE Object fields aren’t compared at all.

To add a version field to a remote table, you can use the ALTER TABLE statement. For example, the following command adds a version field to a SQL Server table:

ALTER TABLE RemoteTable ADD VersionCol TIMESTAMP

You can run this statement as a pass-through query in Microsoft Access or use the administration tools available on your server to add the field. If you add fields to a remote table, relink the table to inform Microsoft Access of the new field’s existence.

Note Not all servers support version fields. To see if yours does, check the server’s documentation.

See Also For more information on the ALTER TABLE statement, search the Help index for “ALTER TABLE statement.”

Using Transactions

A powerful way to improve the performance of updates as well as multiuser concurrency is to use transactions. With transactions, Microsoft Jet accumulates multiple updates and writes them as a single batch. By performing operations as a batch, transactions ensure that operations either succeed or fail as a group, shielding other users from partially completed updates.

You can control transactions in Visual Basic only by using the BeginTrans, CommitTrans, and Rollback methods of the Workspace object. To improve the performance of your application, use a transaction with:

A sequence of Recordset updates, insertions, or deletions, or any combination of the three.
A sequence of action queries.
A sequence of pass-through queries that change data.
Any combination of these operations in a sequence.

Tip On most servers, transactions generate locks that prevent other users from updating or even reading data affected by the transaction until it’s committed or rolled back. Therefore, keep your transactions as short as possible, and avoid placing code sequences that wait for a user’s input inside a transaction.

Batching Updates

The following example demonstrates how to use a transaction in Visual Basic to perform multiple updates in a batch. It uses three pass-through queries in a transaction to transfer money from a savings account to a checking account. The Microsoft Access application (the client) sends queries and updates to the server and retrieves the data it needs.

Sub TransferFunds()
	Dim wrkBatchUpdate As Workspace, dbsTransaction As Database
	Dim qdfUpdateQuery As QueryDef

	On Error GoTo TransferFailed
	
	Set wrkBatchUpdate = DBEngine.Workspaces(0)
	Set dbsTransaction = wrkBatchUpdate.Databases(0)
	wrkBatchUpdate.BeginTrans		' Begin transaction.

	' Create pass-through query.
	Set qdfUpdateQuery = dbsTransaction.CreateQueryDef("")

	With qdfUpdateQuery
		.Connect = "ODBC;DSN=Bank;UID=teller;DATABASE=access"
		.ReturnsRecords = False
		.SQL = "UPDATE Accounts SET Balance = Balance - 100 " & _
			"WHERE AccountID = 'SMITH_SAV'"
		.Execute							' Subtract from savings account.
		.SQL = "UPDATE Accounts SET Balance = Balance + 100 " & _
			"WHERE AccountID = 'SMITH_CHK'"
		.Execute							' Add to checking account.
		.SQL = "INSERT INTO LogBook " & _
			"(Type, Source, Destination, Amount)" & _
			"VALUES ('Transfer', 'SMITH_SAV', 'SMITH_CHK', 100)"
		.Execute							' Log transaction.
	End With
	wrkBatchUpdate.CommitTrans	' Commit transaction.
	Exit Sub

TransferFailed:
	MsgBox Err
	wrkBatchUpdate.Rollback			' Roll back if any Execute fails.
	Exit Sub
End Sub

Note the use of the CreateQueryDef method to create a temporary pass-through query. This technique introduces the minimum possible overhead for Microsoft Jet, while still making use of transactions transparently through Visual Basic. However, it limits functionality to what the server provides, and it is more difficult to define a parameter query this way.

Batching Insertions

If your application provides data-entry forms for multiple records of data, such as an order entry system, you can improve performance and robustness by saving new records in a local holding table and then transferring batches of records from the holding table to the server all at once within a transaction.

Û To insert a batch of records on a server by using a transaction

Create empty local tables that contain the same fields as your remote tables.
Create a data-entry form based on these local tables.
On the form, create a command button labeled “Post Records” for sending records to the server.
Create an event procedure for the button’s Click event that copies the local records to the server tables and deletes the records from the local tables. The event procedure should perform all insertions and deletions within a transaction.

For example, suppose you have an Orders form that contains an Order Details subform based on two local tables, LclOrders and LclOrderDetails. The user enters a new order and a group of detail records in the subform. When the user clicks the PostRecords command button on the Orders form, the following procedure runs:

Private Sub PostRecords_Click
	Dim wrkTransaction As Workspace, dbsPosting As Database

	On Error GoTo TransferFailed
	
	Set wrkTransaction = DBEngine.Workspaces(0)
	Set dbsPosting = wrkTransaction.Databases(0)

	wrkTransaction.BeginTrans			' Begin transaction.
	With dbsPosting
		.Execute "INSERT INTO RmtOrdersEmpty SELECT * " & _
			"FROM LclOrders", dbFailOnError
		.Execute "INSERT INTO RmtOrderDetailsEmpty SELECT * " & _
			"FROM LclOrderDetails", dbFailOnError
		.Execute "DELETE FROM LclOrders"
		.Execute "DELETE FROM LclOrderDetails"
	End With
	wrkTransaction.CommitTrans		' Commit transaction.
	Me.Requery								' Clear form for next order entry.
	Exit Sub

TransferFailed:
	MsgBox Err

	wrkTransaction.Rollback				' Roll back if any Execute fails.
	Exit Sub
End Sub

This procedure doesn’t insert the records directly into the tables on the server. RmtOrdersEmpty and RmtOrderDetailsEmpty aren’t the remote tables; instead, they are the following queries, which are based on the linked remote tables but return no records.

SELECT * FROM RmtOrders WHERE False
SELECT * FROM RmtOrderDetails WHERE False

For these append queries, inserting records into empty queries on linked remote tables provides the fastest possible speed, similar to opening a Recordset by using the OpenRecordset method with the dbAppendOnly constant.

After this code deletes the records from the local tables, it uses the Requery method to present the user with a blank form for the next entry. In addition, it uses the dbFailOnError constant with the two append queries so that an error occurs if any part of either query fails. The error handler then rolls back any changes made.

Limitations of Transactions

Although using transactions can improve the performance of your client/server applications, they do have several limitations:

Although you can nest transactions in Visual Basic, servers generally don’t support nested transactions. Microsoft Access sends only the first-level transaction to a server.
Depending on your server, some pass-through queries may not be allowed within a transaction. For example, some servers don’t allow data-definition queries within transactions. To determine the limits for your server, check its documentation.

Avoid sending server-specific transaction commands in pass-through queries because these commands may confuse the internal tracking of server transactions performed by Microsoft Jet. Instead, use the BeginTrans, CommitTrans, and Rollback methods. Microsoft Access translates these methods into the appropriate server commands.

When you work with local data, each Workspace object represents an isolated transaction space. However, this isn’t the case for remote data used within the Workspace object. You can force a Workspace object to have a distinct remote transaction space by setting the IsolateODBCTrans property of the Workspace object to True. This prevents the Workspace object from sharing connections with other Workspaces, thus guaranteeing transaction isolation. You need to do this only if you use multiple concurrent transactions on your server (which isn’t very common).

See Also For more information on the IsolateODBCTrans property, search the Help index for “IsolateODBCTrans property.” For more information on transactions, see Chapter 9, “Working with Records and Fields,” or search the Help index for “transactions in DAO.”

Using Remote Data Caching in Recordsets

Another way to improve your application’s performance is to cache remote data. Datasheets and forms based on remote data automatically cache data, but dynasets created in Visual Basic do not. If you’ll be moving around in and retrieving data from a small range of records (less than 200), you should explicitly use remote data caching. You can use the CacheStart and CacheSize properties to specify the range you want within the Recordset. Use the FillCache method to quickly fill all or part of this range with data from the server.

Caching also helps when you simulate an action query by using a Recordset. You may want to do this if the operations involved are too complex for an action query. Typically, to simulate an action query, you advance through each record in the Recordset, analyze the record’s contents, and possibly update or delete the record. You can greatly improve performance if you move through the Recordset and fill the cache before processing every 100 records, even if you make only one pass through the Recordset.

See Also For more information on data caching, search the Help index for “caching data.”

Using the MSysConf Table to Control Background Population

You can control background population, which is the rate at which Microsoft Access reads records from the server during idle time, by creating a table named MSysConf on your server. In the MSysConf 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’re experiencing 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, adjust the settings to slow it down.

Note Unlike Microsoft Access forms and datasheets, Recordset objects you create in Visual Basic code aren’t populated during idle time. If your server places read locks on records as they are retrieved, you shouldn’t remain on a record or page for an extended period of time. Partially populated Recordset objects in your Visual Basic code can prevent other users from updating data. If the Recordset is small, you can use the MoveLast method to fully populate the Recordset and clear any such locks. If the Recordset is large, you may want to use a Timer event procedure to implement your own version of background population.

See Also For more information on the MSysConf table, search the Help index for “MSysConf table.”