PRB: Performance Loss When Upgrading Jet ODBC Driver

A Microsoft Visual C++ database project that uses the Microsoft Access 97 (Jet 3.5) ODBC Driver has a noticeable drop in performance compared to similar projects that use the Microsoft Access 95 (Jet 3.0) ODBC Driver.

The Microsoft Access 97 ODBC driver correctly reads and uses the MaxBufferSize performance setting in the ODBC data source. For applications that deal with large amounts of database activity, the default MaxBufferSize setting of 512K may be too small and result in a performance loss. The default MaxBufferSize setting of 512 was designed for optimal performance when using Jet databases under normal conditions but will restrict the amount of memory allotted to the driver and impact performance when using the driver more aggressively.

Increase the MaxBufferSize setting to reduce the amount of swapping that is caused by the more restrictive high watermark of 512K. The MaxBufferSize setting can be pre-set in the ODBC datasource or set programmatically starting in Visual C++ version 5.0 using the SetOption method documented in the DAO SDK. Increasing the MaxBufferSize setting greatly improves performance when the database activity level is high by preventing frequent page swapping. Setting MaxBufferSize to 0 activates Jet's built-in mechanism to determine the high watermark. Please look at the MORE INFORMATION section below for details.

This behavior is by design.

The Microsoft Access 95 ODBC driver distributed with Microsoft Visual C++ 4.x and Microsoft Office 95 (version 3.00.xxxx) ignores the MaxBufferSize setting in the ODBC data source. Instead, it uses the Jet engine's default setting, which is determined based on the amount of physical RAM on the computer using the following formula:



For a computer with 32 MB of RAM:



So when you use this driver, the MaxBufferSize setting is actually well over the default 512 KB in the data source.

The Microsoft Access ODBC driver distributed with Visual C++ 5.0 and Microsoft Office 97 (version 3.50.xxxx) correctly reads and uses the 512 KB default setting from the ODBC data source. This means that the 3.50.xxxx driver is going to use a considerably smaller buffer than the 3.00.xxxx driver when used with the default settings. The impact on performance shows up after the driver has used up the 512 KB of memory and starts a cleanup thread to flush out the oldest buffer pages. If you are submitting a large amount of database requests (constantly inserting records, running queries or leaving open a large number of recordsets), the cleanup thread will eventually not manage to keep up with the amount of page swapping that has to happen. As a result, Jet begins to react more slowly to additional requests.

To change the MaxBufferSize setting manually, double-click your Access database DSN in the ODBC Administrator. In the data source setup dialog box, click the "Options>>>" button to display the "Buffer Size" edit box and type in the new desired MaxBufferSize value there.

For additional information on Jet performance settings such as MaxBufferSize, see Chapter 13 of the Microsoft Jet Database Engine Programmer's Guide (Microsoft Press).

For information on setting the MaxBufferSize setting programmatically, search for the "SetOption Method" in the DAO SDK online help in Visual Studio 97.