Recipe 8.4 Use Jet Engine Optimizations to Speed Up Queries

8.4.1 Problem

You've heard that the Jet database engine includes optimizations you can use to improve the performance of your queries. How do you create queries that use take advantage of these optimizations?

8.4.2 Solution

The Jet engine (the database engine built into Access) can execute certain types of queries dramatically faster that others, depending on how you construct the queries. This solution explains how this technology works and how you can take advantage of it. It also introduces a technique for timing the execution of queries.

Load the 08-04.MDB database. Open the qryOr1 query in design view. This query, which is shown in Figure 8-11, contains criteria on two fields, Menu# and Quantity. It returns all records from tblOrderDetailsNoIndexes where Quantity = 13 or where Menu# = 25. If you switch to SQL view, you'll see the following Where clause:

WHERE (((tblOrderDetailsNoIndexes.[Quantity])=13)) OR (((tblOrderDetailsNoIndexes. [Menu#])=25))

Figure 8-11. The qryOr1 returns rows where Quantity = 13 or Menu# = 25

Close the query and open the tblOrderDetailsNoIndexes table to confirm that this table has no indexes. The qryOr2 and qryOr3 queries are identical to qryOr1, but they are based on different tables. qryOr2 is based on tblOrderDetailsPartialIndexed, which contains an index on the Menu# field, and qryOr3 is based on tblOrderDetailsFullyIndexed, which contains indexes for both Menu# and Quantity.

Run the three queries in turn. You should notice that qryOr3 is much faster than qryOr1 or qryOr2, which are of similar speed. To get more accurate timings, open the frmQueryTimer form in form view and create a new test comparing the three queries, as shown in Figure 8-12. Press the Run Test button to begin executing each query the number of times specified in the Number of Reps text box. When the test is complete, press the Results button to view a Totals query datasheet that summarizes the results of the test (see Figure 8-13). When we ran this particular test on a 650-MHz Pentium III machine with 448 MB of memory, qryOr3 was 3.67 times faster than qryOr2 and almost 60 times faster than qryOr1! On a slower machine, the results would be even more dramatic.

Figure 8-12. A test comparing three queries

Figure 8-13. The qryOr3 query is 60 times faster than qryOr1

Follow these steps to take advantage of query optimization in your own queries:

1. Index all table fields that are referenced in the criteria of your queries.

2. Create queries with either:

   • Two or more criteria on indexed fields in the same underlying table connected with the And operator

   • Two or more criteria on indexed fields in the same underlying table connected with the Or operator

In addition, special query optimizations will be used whenever you create Totals queries that make use of the Count(*) expression and have either no criteria or criteria on indexed fields only.

8.4.3 Discussion

The Jet database engine can combine two or more indexes mathematically and thus execute a query using multiple indexes. The net result is faster execution when faced with this kind of query. This technology was originally created by the FoxBASE developers and is used by both Jet and SQL Server.

This technology also speeds up Totals queries involving Count(*). Jet is able to execute this type of query without reading any rows of data; instead, it counts the index rows, which is almost always faster than reading pages of data records.

In the sample database, you'll find three tests comparing the various optimizations using the three different versions of the tblOrderDetails table. You may wish to run these tests on your own computer to see what results you get. You may also wish to import the query timer form into your own database to time your queries in various scenarios. To use the frmQueryTimer form in your own database, import the objects from Table 8-6.

Table 8-6. The objects used in the query timer technique

Object type	Object	Description
Table	zstblTests	One row for each test in frmQueryTimer
Table	zstblQueries	One row for each query compared in a test
Table	zstblTimes	One row for each time recorded in a test
Query	zsqryTestAnalysis	Totals query used to analyze the results of a test
Form	frmQueryTimer	The query timer form
Form	fsubQueries	Subform used in frmQueryTimer

Once you've imported the objects from Table 8-6, you can set up and execute a new test following these steps:

1. Create and save two or more queries that you wish to compare.

2. Open frmQueryTimer in form view and enter the number of times to repeat the test in the Number of Reps text box.

3. Enter a description for the test in the Test Description text box.

4. Add a record to the subform for each query you wish to compare for the test. Use the Query combo box control to select the queries created in Step 1.

5. Click on the Run Test button to run the test. When it's done, the status text box will contain the message "Test completed." Click on the Results button to view a Totals query comparing the average execution times of the queries.

The frmQueryTimer form executes each query repeatedly using a For...Next statement that calls the acbTimeQuery function, which is shown here:

Public Function acbTimeQuery(ByVal strQry As String, _  datStart As Date, lngRecs As Long) As Variant     Dim db As DAO.DATABASE     Dim qdf As DAO.QueryDef     Dim rst As DAO.Recordset     Dim lngStart As Long     Dim lngEnd As Long          Set db = CurrentDb( )     Set qdf = db.QueryDefs(strQry)     lngStart = acb_apiGetTickCount( )     datStart = Now( )          Set rst = qdf.OpenRecordset(dbOpenSnapshot)          If Not rst.EOF Then         rst.MoveLast         lngRecs = rst.RecordCount     Else         lngRecs = 0     End If          lngEnd = acb_apiGetTickCount( )     acbTimeQuery = lngEnd - lngStart End Function

There are two interesting aspects to this function. First, it makes use of the GetTickCount Windows API function to get more accurate measures of time than VBA's built-in Timer function can provide. Second, it executes the query by creating a snapshot recordset, not a dynaset-type recordset. This forces the query to execute completely rather than returning just the first page of records.

Query optimization can't work if you don't create indexes. In general, it's a good idea to create an index for every field used in:

• Query criteria

• Query sorts

• Ad-hoc joins (when enforced relationships have not been created)

Don't create indexes on fields that are part of referential integrity relationships; Access already has indexes to enforce these relationships. Also be aware that Access has a limit of 32 indexes per table. And finally, don't go overboard indexing every field in every table of your database: indexes can slow down operations that modify data.