Microsoft Jet 3,5 Performance Overview and Optimization Techniques. Non-configurable Performance Settings

White Paper

Microsoft Jet 3.5 Performance Overview and Optimization Techniques

Kevin Collins Microsoft Jet Program Management 1/15/97 © 1997 Microsoft Corporation. All rights reserved.

Table Of Contents

Table Of ContentsOverview................................................................................................. 2

Architecture Changes.......................................................................................................... 3

Non-configurable Performance Settings__________________________________________ 3

Reduced Locking.......................................................................................................... 3

Improved Rushmore Algorithms.................................................................................. 5

Removal of Implicit Transaction for SQL DML Statements......................................... 5

Reduced Index Btree Navigation.................................................................................. 6

Improved Cache Reuse................................................................................................. 6

Persistent Connections to Security System Tables..................................................... 6

Reduced Flushing of the Cache................................................................................... 6

Larger Allocation by Extents........................................................................................ 7

Improved Performance with DELETE Statements....................................................... 7

Faster Generation of Query Plans................................................................................ 8

Explicit Registry Values................................................................................................ 8

New MaxBufferSize Formula........................................................................................ 9

Configurable Performance Enhancements________________________________________ 9

Programmatic Run-Time Registry Control................................................................... 9

Programmatic Control of Flushing Jet’s Write Cache............................................... 10

Programmatic Control of Refreshing the Cache........................................................ 11

Improved Control over Lock Retries........................................................................... 12

Improved Functionality with NetWare Network Operating System.......................... 12

Improved Page Recycling............................................................................................ 13

New Asynchronous Writing Techniques.................................................................... 13

Optimization Tips and Techniques................................................................................... 14

Prevent Unnecessary Query Recompilation______________________________________ 14

Compact Frequently__________________________________________________________ 15

Avoid Embedding Expressions in Queries_______________________________________ 15

Cautiously Use Indexes_______________________________________________________ 16

Use SQL DML Statements Instead of DAO Looping Constructs____________________ 17

Implement Persistent Connections with Linked (Attached) Tables___________________ 17

Use Explicit Transactions When Implementing Online-Transaction Processing (OLTP) 18

Check Parameterized Queries for Optimal Performance___________________________ 18

Unsupported Tuning Features......................................................................................... 19

The ISAMStats Function______________________________________________________ 19

The ShowPlan Function_______________________________________________________ 21

Conclusion........................................................................................................................ 24

Overview

Microsoft Jet 3.5 is the database engine that is used in Microsoft Office 97, which includes Microsoft Access 97, and in Microsoft Visual Basic® 5.0. Any application that has access to the version of Visual Basic for Applications that shipped with Office 97 will have access to Jet data. There is no format change in Jet 3.5; thus, it is possible to access a database that was created with Jet 3.0 without converting it. (Note: Microsoft Access 97 does require a conversion process, primarily due to a new method of storing Visual Basic for Applications). Even though the database format has not changed, an application cannot access Jet 3.5 using DAO 3.0. Thus, if you are using Microsoft Visual Basic 4.0, you will not be able to reference the DAO 3.5 type library to gain access to Jet 3.5. Jet 3.5 also has new file names for the three primary DLLs that it uses (MSJET35.DLL, MSJINT35.DLL, and MSJTER35.DLL).