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

Jet 3.5 resolved this issue by modifying the way that it recycles pages. Jet 3.5 can now recycle LV pages once a new allocation of LV pages has been created. The size of the database will still grow initially, but it will remain stable with only small increases of size over time. However, the Jet performance team discovered that this feature caused a performance hit when manipulating LV data. To circumvent the performance issue, a new registry entry called RecycleLVs was added. By default RecycleLVs is turned off to ensure performance parity with Jet 3.0. However, Microsoft Access will dynamically turn this feature on immediately before manipulating any of its objects that utilize LV data and will turn it off when those objects are no longer being manipulated. This provides the user with optimal performance while minimizing database bloat.

DAO users who are manipulating LV data should leave the RecycleLVs registry setting turned off and turn it on only when necessary by utilizing the SetOption method of the DBEngine object discussed above.

New Asynchronous Writing Techniques

The biggest performance change for Jet 3.5 was a modification to how Jet uses its internal cache for manipulating data outside transactions. One of the biggest performance improvements in Jet 3.0 was the introduction of asynchronous writes. However, due to potential concurrency issues, this feature could not be fully exploited.

In Jet 3.0, asynchronous writes were controlled by two registry entries: MaxBufferSize and SharedAsyncDelay/ExclusiveAsyncDelay. The SharedAsyncDelay setting is used for databases opened in shared mode; likewise, ExclusiveAsyncDelay is used for databases opened in exclusive mode. The combination of the two settings determined how long modified data pages could be held in Jet’s cache before being flushed to disk. The most restrictive of the two registry entries was SharedAsyncDelay. The default SharedAsyncDelay value was 50 milliseconds. This value prevents concurrency issues that could arise with Microsoft Visual Basic or Microsoft Access users modifying data through forms, because any modified data that is held in Jet’s cache also has a corresponding write lock. Therefore it was necessary to keep the SharedAsyncDelay setting low as to prevent pages from sitting in Jet’s cache and holding write locks.  This scenario would cause a significant concurrency issue if a higher SharedAsyncDelay value were used as the pages in Jet’s cache would continue to hold write locks. However, leaving the value low also directly affected performance on operations that manipulated many rows of data at once (such as looping constructs in DAO or SQL DML statements) due to the fact that Jet was not utilizing its cache as it flushed it’s cache every 50 milliseconds.

This situation made it very difficult for a developer to take advantage of the asynchronous writing techniques that were present in Jet 3.0 without affecting concurrency. The only way to take advantage of these techniques was to have one machine with a high SharedAsyncDelay setting designated as the machine to only run operations that affected many rows.

Jet 3.5 introduces a new method of determining when to flush the cache for asynchronous writes that eliminates this problem. This was accomplished by adding a new registry setting called FlushTransactionTimeout that would ignore the AsyncDelay registry settings. While Jet 3.0 would use the SharedAsyncDelay setting to determine a maximum amount of time to elapse before flushing the cache to disk, Jet 3.5 uses the FlushTransactionTimeout setting to determine an amount of time of inactivity before flushing the cache to disk.

This eliminates the problem with users modifying data in forms because 500 milliseconds, the default setting for FlushTransactionTimeout, would expire before the user could modify and save the next row. Therefore, the maximum amount of time that a lock would be held on the data and index pages associated with the row being edited would be 500 milliseconds. This eliminated any concurrency issues when using forms in Microsoft Visual Basic or Microsoft Access. On the performance side, users would see significant performance increases, as Jet would now be able to use the maximum amount of cache before flushing to disk. This is due to the fact that any operation that modifies many rows (for example, a DAO looping construct or an SQL DML statement) would be adding rows to Jet’s cache before the 500 milliseconds setting would expire. This behavior results in Jet fully utilizing its cache and substantially less I/O because Jet does fewer reads and writes. Below is an example of the performance differences with the new behavior.