PRB: Performance Loss When Upgrading Jet ODBC Driver

MORE INFORMATION

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:

   ((Total Physical RAM in MB - 12 MB) / 4) + 512 KB
				

For a computer with 32 MB of RAM:

   ((32 MB - 12 MB / 4) + 512 KB = 5632 KB
				

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.