How To Improve Performance of Applications Using Jet 4.0

The Microsoft OLE DB Provider for Jet version 4.0 is installed by Microsoft Data Access Components (MDAC) versions 2.1 and 2.5 which are in turn installed by various applications. In several areas, Jet 4.0 provides additional features and better performance than its predecessor; however some ActiveX Data Objects (ADO), ODBC, and OLE DB applications may see their performance degraded.

This article provides possible reasons for poor application performance and offers tips on how to improve it.

Possible Reasons for Poor Performance with Jet 4.0

• Check the DSN
  
  If your data source name (DSN) was created with an older version of the Microsoft Jet ODBC driver, the default buffer size may still be 512 KB. The default buffer size of Jet 4.0 is 2048 KB and with anything less than that, your application may be spending most of its time swapping virtual memory.
  
  
• Is the database (.mdb) file on a network share?
  
  The Microsoft Access ODBC Driver that ships with MDAC 2.1 or later interprets the PageTimeout setting in milliseconds, whereas the MDAC 2.0 versions of the Access ODBC Driver interpret PageTimeout in seconds. Modify the default value of PageTimeout from 5 to 5000 when creating the DSN, or modify the value through the connection string. When the database file is placed on a network share, having a low timeout value (such as 5 milliseconds) causes excessive network traffic and can degrade the performance. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
  246560 BUG: Excessive Network Activity When Using Access ODBC Driver
• Is the database in Access 9x format?
  
  The Jet 4.0 engine uses Unicode strings internally. Because Access 9x databases are not Unicode, the new Jet driver is slowed due to the conversions on all strings coming from and going to the Jet database. Because Access 2000 databases store strings in Unicode, no conversion is necessary; for optimum performance, convert all databases to Access 2000.
  
  
• Are you certain that your database is not damaged?
  
  Although a damaged database may be able to open successfully, data access is much slower due to damaged indexes or table rows. Run the Repair and Compact Database utilities in the Microsoft Access development environment to repair a damaged database. Programmatically, you can use the Jet and Replication Objects (JRO) method CompactDatabase, the Jet OLE DB provider's IJetCompact::Compact method, or the Data Access Objects (DAO) CDaoDatabase::CompactDatabase method to accomplish this.
  
  
• Does your application change locales?
  
  Version 4.0.2115.25 of Jet 4.0 (Msjet40.dll) has a problem that causes queries to slow down after an application changes the locale identifier (LCID); for example, by using the CRT setlocale function. Changing the default locale can slow down certain C Runtime String functions, such as strcmp(), that are used by Jet. This problem is fixed in later versions; see the "References" section for information on downloading the latest version of Jet.
• Is the UnicodeCompression flag on?
  
  

When storing data on a disk, Jet compresses its Unicode strings. Compression can be turned off by setting the UnicodeCompression property to No; this can cause database bloating and as a result, queries become slower due to the extra disk I/O to fetch more data. Remember that disk I/O is usually slower than decompression algorithms. For more information, see the following article in the Microsoft Knowledge Base:

Tips on Improving the Performance of your Jet Application

• Compile in Unicode and use an Access 2000 format database file.
  
  As mentioned earlier, Jet 4.0 uses Unicode strings internally, so a non-Unicode application runs slower because Jet must perform conversions on all strings. Unicode applications do not need to go through this conversion. For best performance, convert your database to Access 2000 (Unicode) format and compile your application in Unicode.
  
  
• Optimize your query with the help of JETSHOWPLAN.
  
  Use the JETSHOWPLAN registry flag to see Jet's plan of execution for your query. See "The ShowPlan Function" in Microsoft Jet Engine Programmer's Guide.
  
  
• Use ADO or OLE DB.
  
  For optimum application performance, Microsoft Visual Basic programmers can use ActiveX Data Objects (ADO) for database programming. Microsoft Visual C++ 6.0 (and later) programmers can instead use the Active Template Library (ATL) OLE DB Consumer Templates for simpler and efficient access to their database.
  
  
• Use the native Jet 4.0 OLE DB Provider.
  
  Visual Basic and Visual C++ programmers who use OLE DB or ADO for database programming have a choice of two OLE DB providers to access their Jet database: the OLE DB Provider for ODBC Drivers or the Microsoft Jet 4.0 OLE DB Provider. The Jet 4.0 OLE DB Provider is the most direct and efficient path to your database because the OLE DB Provider for ODBC must go through an ODBC driver to access your database.

For additional information on other known issues that may occur when you upgrade from Jet 3.5x to Jet 4.0, click the article number below to view the article in the Microsoft Knowledge Base: Jet 4.0 is available with Microsoft Data Access Components versions 2.1, 2.5, and 2.5 SP1, which can be downloaded from the following Microsoft Web site: For additional information and the latest service pack for Jet 4.0, see the following article: