Access Cookbook

Use Rushmore to Speed Up Queries

Problem

You’ve heard that Rushmore can improve the performance of your queries. How do you create queries that use Rushmore?

Solution

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.

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

Run the three queries in turn. You should notice that qryOr3 ...