Tips and Tricks

This section describes some simple, yet often overlooked, ways of speeding up ODBC data access.

Dynasets versus Snapshots

Microsoft Jet provides two kinds of recordsets: dynasets, which can be updated, and snapshots, which cannot. If you don't need to update data and the recordset contains fewer than 500 records, you can reduce the time it takes the recordset to open by requesting a snapshot using DB_OPEN_SNAPSHOT. This causes Jet to use a snapshot, which is generally faster than using a dynaset.

However, if the recordset you're using is large or contains Memo or OLE Object fields, using a dynaset is more efficient. If you use a dynaset, Jet 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 referenced.

Rather than transferring the data in each column of the row during the fetch , Jet builds a dynaset by fetching the key values that uniquely identify each of the rows. The data from the bookmarked rows is fetched only when needed to populate the table or query datasheet view. Using key values speeds the chunking operation and minimizes network traffic, especially when browsing large tables. Data retrieval is also optimized in that Memo and OLE objects are not retrieved unless they need to be displayed.

A snapshot Recordset does not use bookmarks; instead, the data contained in each of the rows is fetched. Creating snapshot Recordset objects can be faster with small tables, but can be quite time-consuming when large tables with many columns are involved, and network traffic increases drastically. This is especially true if the table or query includes Memo fields and OLE objects. (Note, however, that .MDB snapshots containing Memo or OLE fields will not retrieve the data into the snapshot until it is actually referenced.) You can further minimize the performance hit with snapshot Recordsets by ensuring you are returning only the columns you need.

Using Forward-Only Snapshot Recordsets

Recordset objects of the Snapshot type provide bi-directional scrolling capability by default. If you only need to make a single pass through the records of a Snapshot, You can Or the DB_FORWARDONLY flag with any other existing flag in OpenRecordset()'s intOptions argument. This makes the Snapshot more efficient, because the result set is not copied to a scrollable buffer area.

Appending Data

If you need to append small amounts of data to an ODBC table, open the recordset by ORing in the DB_APPENDONLY flag. This will avoid the population of the dynaset with the bookmarks of all the records in the table.

If you're trying to append a lot of data to an ODBC table that is already stored in an .MDB table, then use the technique suggested under Batching Insertions elsewhere in this document.

Jet Query Optimization and Partitioning

Jet processes as much of a query as possible on the server for two reasons: 1) Filtering data on the server returns the minimum number of rows, which minimizes network traffic and delays; and 2) Under most conditions, processing queries on the server is faster than processing a query on the client. Conventional SELECT and action (append, update, and delete) queries can be processed entirely by the database server. Exceptions are:

Under the preceding conditions, Jet attempts to partition the query into components that can be executed on the server and those that must be executed locally. An example of "smart partitioning" is Jet's execution of crosstab queries. If you use a conventional crosstab query, Jet sends a SELECT query with a GROUP BY clause to the server, then transforms the result set to a crosstab query locally. Jet cannot optimize processing the query remotely if any one of the following conditions occur in the crosstab query:

For more complete and detailed information on how Jet determines its query execution strategy, see the "Jet Database Engine ODBC Connectivity" paper listed in the references.

To make absolutely certain that a given read-only query or action query executes on the server, you could alternatively create a pass-through query (see below).