Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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.

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.