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

The primary goal of this paper is to disseminate information regarding features of Jet 3.5 that you can use to improve and optimize performance. Since many of the features are primarily exposed through DAO, this paper will also illustrate some of the new properties and methods included in DAO 3.5. This paper will go over each feature and, where applicable, include sample code to illustrate the features along with performance numbers generated from the Jet performance lab. In addition, tips and techniques discovered during our performance analysis will be shown along with other pertinent information discovered from customers in the field. Below is a high level list of the major enhancements that were put into Jet/DAO 3.5 to improve and fine tune performance.

·  Programmatic run-time control of registry settings

·  Improved caching mechanism

·  Reduced locking

·  Improved allocation of pages by extents (clustering)

·  Improved page recycling

·  Explicit default registry entries

·  Removal of locking issues with Netware

·  Improved lock-retry mechanism to reduce network traffic

·  Programmatic control of refreshing cache

·  Programmatic control to guarantee flushing of cache to disk 

·  Significant performance increase with large SQL DML statements

·  Improved Rushmore™ algorithms

·  Improved performance on SQL DELETE statements

Architecture Changes

Non-configurable Performance Settings

Reduced Locking

To improve multi-user performance from over that of Jet 3.0, the Jet team took a hard look at where concurrency bottlenecks were occurring. This analysis lead to a modification of locking algorithms in two areas: write locks on index pages and read locks on long value (LV) data pages.

When a Jet 3.0 database used enforced referential integrity, Jet would always place a read lock on any index page that was being read. This was necessary to prevent other users from changing data in an upper-level index btree that might be necessary to enforce referential integrity. However, this did not come without cost as write locks on a series of index pages would prevent other users from even seeking on that index.

This was discovered too late in the Jet 3.0 time frame for the Jet team to make an architectural change to reduce the concurrency hit. This was resolved in Jet 3.5 by replacing the write locks with read locks on index pages that were not being modified. This allows Jet to continue to enforce referential integrity while still allowing other users to read the index pages.

The other locking enhancement in Jet 3.5 concerns placing read locks on LV pages. LV pages typically contain columns with the Memo and OLE data types used in Microsoft Access. In Jet 3.0 a read lock would be placed on every LV page that was being read. The primary reason for using read locks was to ensure that the user would see unchanged data when reading large LV pages. However, while we examined more customer databases, we discovered that many users were using a Memo field with only a minimal amount of data being stored. Since LV data that did not span more than one page did not meet the original criteria for placing read locks, the Jet team devised a way to eliminate read locks in those scenarios. The outcome of this was to remove read locks on LV pages that did not span more than one page. This substantially reduced concurrency conflicts and substantially increased performance.

Below is a chart showing the multi-user performance throughput improvements in Jet 3.5:

Since Jet 3.0, a large effort has been made to measure performance with Jet. The Jet 3.5 team expanded on this effort by increasing test suites to over 1,500 performance benchmarks. The chart above and graphs that will follow are results from some of those tests.