You’ve heard that Rushmore can improve the performance of your queries. How do you create queries that use Rushmore?
Rushmore query optimizations help the Jet engine (the database engine built into Access) execute certain types of queries dramatically faster. This solution explains how Rushmore 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))
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—which takes advantage of Rushmore—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.
Follow these steps to take advantage of Rushmore in your own queries:
In
addition, special Rushmore 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.
Most database engines,
including the Jet engine, are quick when executing queries based on a
single indexed field. Unfortunately, they can slow markedly when
executing queries involving criteria on two or more indexed fields
from the same table connected with the And
or
Or
operators. That’s because most database
engines can use only one index when executing a query against a
table.
Enter Rushmore, a Microsoft technology that 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 now used by both Jet and SQL Server.
Rushmore
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 Rushmore 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:
Create and save two or more queries that you wish to compare.
Open frmQueryTimer in form view and enter the number of times to repeat the test in the Number of Reps text box.
Enter a description for the test in the Test Description text box.
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.
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. This forces the query to execute completely rather than returning just the first page of records.
Rushmore 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:
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.