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

The lab was also upgraded during the 3.5 cycle to reflect the operating system and machine hardware that a high-end customer might use. Of course, testing was still done with memory restrictions as low as 5 MB of RAM to represent users with low-end hardware. The majority of the multi-user tests were conducted on 36 machines. 27 of them were identically configured Pentium 60mhz machines with 32 MB of RAM while the remaining nine were Pentium 120 machines with 64 MB of RAM. All machines had a 540 MB IDE hard disk drive and many had a second 1.2 or 2.5 GB EIDE hard disk drive with a PCI network interface card(NIC). The tests were run using only DAO/SQL commands on Windows NT® Server/Workstation 3.51 SP5, Windows NT Server/Workstation 4.0, Windows® 95 SP1 or OSR2. When run in a network environment the network operating system was NetWare 4.11 on a Dell XPE PowerEdge P120 with four 1 GB RAID drives running off a dedicated EISA RAID SCSI host adapter, two four-port PCI full-duplexed Ethernet NICs and 64 MB of RAM.

Improved Rushmore Algorithms

Jet 3.5 was improved to use Rushmore technology on more operands in predicates. In particular, the FALSE and <> operands now provide substantial performance improvements.

Removal of Implicit Transaction for SQL DML Statements

Even with all the work in Jet 3.0 to eliminate transactions in order to obtain better performance, SQL DML statements were still placed in an implicit transaction. This negated some of the performance work and typically resulted in DAO looping code to substantially outperform SQL DML statements. Jet 3.5 has removed that restriction and SQL DML statements now are no longer placed in an implicit transaction. This results in a substantial performance benefit when running SQL DML statements that affect many rows of data.

While this change provides a substantial performance increase, it also introduces a change to the behavior of SQL DML statements. Previously, an SQL DML statement would roll back if any part of the SQL DML statement could not be completed. This was due to the fact that Jet placed it in an implicit transaction. It is now possible to have some of the rows committed by a SQL DML statement while others are not.  An example of this would be when Jet’s cache is exceeded.  The data in the cache will be flushed to disk and the next set of rows will be modified and placed in the cache.  Therefore, if the connection were terminated, there would be a possibility that some of the rows were saved to disk and others were not.  This behavior is the exact behavior that Jet 3.0 users would see when using DAO looping constructs to update data without an explicit transaction.  If this behavior is not desired, then the user will need to add explicit transactions around the SQL DML statement to define a set of work and sacrifice the performance gains.

Reduced Index Btree Navigation

Jet 3.5 has been enhanced to not reseek an index page if the index is a primary or unique index. This reduces the amount of I/O that occurs since Jet does not need as many reads.

Improved Cache Reuse

Jet 3.0 would always refresh its cache on a page when a read lock was promoted to a write lock. In Jet 3.5 it is no longer be necessary to refresh the cache when this operation occurs, thus resulting in reduced I/O.

Persistent Connections to Security System Tables

While working with the Microsoft Access team, we discovered that their implementation of Jet through the user interface required frequent checking for security on objects. Jet 3.5 now keeps the MSysObjects and MSysACEs tables open for the duration of a Jet instance in order to improve performance through the Microsoft Access user interface and other areas where security is implemented and objects are being referenced through DAO. This reduces the overhead of constantly opening and closing those tables when security is implemented and reduces I/O.