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

Reduced Flushing of the Cache

When adding data to a table, Jet 3.0 would always flush its cache every two seconds after each extent (up to 16K) was added. By examining the performance characteristics when adding one million rows of data, Jet 3.5 was modified to only flush its cache two seconds after all new extent data was added to the database. This results in less I/O.

Larger Allocation by Extents

Jet 3.0 would allocate up to 8 pages (16K) at a time when adding data to a table. This was done to allow data to be contiguous within a table, thus optimizing sequential reads and Jet’s read-ahead cache. It was determined during performance testing that increasing this maximum allocation resulted in better performance. Jet 3.5 has been modified to add up to 32 pages (64K) at a time when adding data. Like Jet 3.0, Jet 3.5 is smart about adding large extents to tables and will only do so when large numbers of rows exist in a table.

Improved Performance with DELETE Statements

One of the big performance improvements in Jet 3.0 was the reduced time it took to remove a table or delete all the rows in a table. While Jet 3.0 offered substantial performance improvements, there was still room for improvement and Jet 3.5 took advantage of that by optimizing how it removed pages in the database by reading and processing less directory pages.

Faster Generation of Query Plans

During the course of performance tests, it was determined that users that utilized temporary queries (for example, DB.Execute SQL) or constantly modified stored queries in code were experiencing slow performance. Jet 3.5 addressed this issue by optimizing the query plan generation process.

Explicit Registry Values

With Jet 3.0 it was difficult for users to manipulate registry values to control Jet because the key names, types, and values were not created in the registry. Jet 3.5 remedies that by explicitly loading the key names and default values when Jet 3.5 is registered. Explicit registry names and values are also loaded for the Jet 2.x installable ISAM. Below is a listing of the default registry entries for Jet 3.5. New registry entries will be discussed later in this paper.

New MaxBufferSize Formula

When using the default value of zero for the MaxBufferSize registry setting in Jet 3.0, Jet would calculate how much RAM to dynamically allocate for its cache. This formula was ((Total in MB – 12) / 4 + 512K). However, it was discovered that machines with large amounts of RAM (that is, over 64 MB) were becoming more common and causing Jet to start using a large amount of RAM for the cache. This could potentially cause a lot of swapping to occur due to the use of virtual memory, especially when Jet was being used with Microsoft Internet Information Server (IIS). Jet 3.5 addressed this issue by putting a cap of 13,824K on the result of the formula. The user can override the formula and the cap by putting a value other than zero for the MaxBufferSize setting.

Configurable Performance Enhancements

Programmatic Run-Time Registry Control

From talking with customers and examining the results of different registry settings while running performance tests, we realized that there was only so much self-tuning that Jet could do to optimize all scenarios. The only way to fine-tune Jet was by exposing programmatic run-time controls via DAO.

Probably the most exciting enhancement to Jet 3.5 is the ability to control the majority of Jet’s registry value settings during run time. This provides the developer with ultimate control over performance and functionality for Jet 3.5. These new features are exposed through DAO 3.5 with the new SetOption method of the DBEngine object. Below is a list of the Jet registry values and their corresponding DAO constants that can be manipulated during run time.