Access Cookbook - Ken Getz [172]
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.
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:
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-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)
WARNING
Don't create indexes on fields that are part of referential integrity relationships;