Microsoft Jet 3,5 Performance Overview and Optimization Techniques. Non-configurable Performance Settings, страница 8

As the chart illustrates, almost a 50% reduction in I/O was accomplished with this new setting, while not increasing concurrency with forms usage. While the Jet performance team did not encounter any reasons to not use this feature, setting the FlushTransactionTimeout value to zero disables the feature. Disabling this feature causes Jet to use the AysncDelay settings in the same manner as Jet 3.0.

Optimization Tips and Techniques

This section is intended to illustrate performance optimization tips and tricks that the Jet performance team discovered during our internal tests and while examining performance issues provided by customers.

Prevent Unnecessary Query Recompilation

An area that can cause performance degradation is the constant recompiling of stored queries. We have seen this occur when DAO objects are not explicitly closed. The reason for this is that stored queries reference a particular memory address in order to use the Visual Basic for Applications expression service. If a DAO object is opened followed by the execution of a stored query, re-executing that query may cause Jet to recompile because the original memory address space is occupied. In certain circumstances this can also cause the database to increase in size. Many of these issues were resolved in Jet 3.5, but some of the issues can be resolved only by explicitly closing DAO objects when they are no longer being used. The developer should not rely on Visual Basic for Applications to implicitly close objects when a Sub or Function procedure is exited.

If explicitly closing the DAO object is not an option due to nested explicit transactions or some other scenario, another possible workaround is to use the dbForwardOnly syntax when opening a recordset. Using the dbOpenShapshot, dbForwardOnly syntax prevents Jet from recompiling the query. We have found this to be most useful when using the data grid that comes with Microsoft Visual Basic 4.0, because the developer has no control to explicitly close the DAO objects being used.

Compact Frequently

From a performance perspective, there are many reasons to frequently compact a database. One reason is that compacting will create a new database that stores all table rows in a contiguous order. If a primary key or unique index is defined, the rows will be sorted in order of the primary key or unique index. This allows Jet to take full advantage of its read-ahead cache and also reduces disk I/O when doing sequential scans of a table. Compacting also causes all the statistics in the database to be recalculated. Statistics can become out of date during the course of database operations, thus resulting in inaccurate query plans. Probably the most important performance reason for compacting the database is that the CompactDatabase command or CompactDatabase method switches a flag in all stored queries that causes them to recompile the next time they are executed. This is important because it ensures that the query plan retrieves the latest statistics and creates the best execution path to retrieve the data.

Compacting is also important from a stability standpoint because it removes all deleted pages, recopies all pages (thus ensuring integrity in the pages), and recreates all index pages.

A somewhat related issue to this concerns repairing a database. A bug was found in Jet 3.0 where issuing the RepairDatabase command (or the RepairDatabase method) before compacting the database could result in a database that could no longer be opened. This problem (due to a very rare bug that could allow duplicate indexes on the system tables) has been resolved in Jet 3.5 and a special release of Jet 3.0 is now available on http://www.microsoft.com/kb/articles/q151/1/86.htm.  Note that the problem will never occur if the database is compacted before it is repaired.  Previously it was recommended to repair the database before compacting it. This was primarily for Jet 2.x databases, because the RepairDatabase command and RepairDatabase method had additional functionality to recover truncated rows of data. This is no longer true for Jet 3.x file formats and it is recommended that users only repair a database if a Jet error message indicates that this is necessary.