|[ Team LiB ]|
Recipe 8.4 Use Jet Engine Optimizations to Speed Up Queries
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))
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.
Follow these steps to take advantage of query optimization in your own queries:
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:
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.
|[ Team LiB ]|