This article was previously published under Q112112
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
This article discusses how you can optimize query performance inMicrosoft Access 2.0, 7.0, and 97. The topics include the MicrosoftJet database engine's query Optimizer, query timing, analyzing performance,and design tips for improving query performance.
This article assumes that your database has local tables instead of linked(or attached) tables. If your tables are linked, this information stillapplies; however, there are additional issues that impact queryperformance on linked tables. For more information about improvingperformance on linked tables, please search on the following words in theMicrosoft Knowledge Base:
ODBC and Optimizing and Tables
The Query Optimizer
The Microsoft Jet database engine contains several components, but the mostimportant to queries (and the most complex) is the Optimizer. The Optimizeris "cost-based," meaning that it assigns a time cost to each query task andthen chooses the least expensive list of tasks to perform that generatesthe desired result set. The longer a task takes to perform, the more costlyor expensive it is considered to be.
To decide which query strategy to use, the Optimizer uses statistics.These statistics are based on the number of records in a table, the numberof data pages in a table, the location of the table, whether or notindexes are present, how unique the indexes are, and so on. Based on thesestatistics, the Optimizer chooses the best internal query strategy fordealing with a particular query.
The statistics are updated whenever a query is compiled. A query isflagged as needing to be compiled when you save any changes to the query(or its underlying tables) and when the database is compacted. If a queryis flagged to be compiled, the compiling and updating of statistics occursthe next time the query is run. Compiling usually takes from 1-4 seconds.
If you add a significant number of records to your database, you shouldopen and save your queries to recompile them. For example, if you designand test a query using a small set of sample data, you should recompilethe query after more records are added to the database. This ensuresoptimal query performance once your application is in use.
NOTE: You cannot view Jet database engine optimization schemes or specifyhow to optimize a query. However, you can use the Database Documenter tofind out whether indexes are present and how unique an index is. For moreinformation about the Database Documenter, search on "Database Documenter"using the Microsoft Access 97 Help Index.
NOTE: The Database Documenter is called the Database Documentor inMicrosoft Access 2.0 and 95.
There are two significant time measurements for a Select query: time todisplay the first screenful of data and time to obtain the last record. Ifa query returns only one screenful of data, these two time measurementsare the same. If a query returns many records, then these timemeasurements can be very different.
If the two measurements are the same when you view a Select query inDatasheet view, you see a screenful of data and a total number of recordsreturned by the query such as, "Record 1 of N." If it is faster for theJet database engine to display the first screenful of data then tocomplete the query and retrieve the last record, you see a screenful ofdata but no "N" in "Record 1 of N". The "N" value is blank until the queryis complete or you scroll down to the last record.
This behavior is the result of the Jet database engine choosing one of twoperformance strategies: complete the query, and then display data; ordisplay data, and then complete the query. You cannot control whichstrategy is used; however, the Jet database engine will choose whicheveris most efficient.
If you are using Microsoft Access 7.0 or Microsoft Access 97, you can usethe Performance Analyzer to analyze queries in your database. Because thequery performance analysis is closely tied to the Jet database engine, thePerformance Analyzer will suggest adding indexes only when the indexes willactually be used by the Jet database engine to optimize the query. Thismeans that the Performance Analyzer can provide performance tips which aremore specific to your database than the general suggestions listed below inthe "Tips to Improve Query Performance" section of this article.
To run the Performance Analyzer in Microsoft Access 7.0 or Microsoft Access97, on the Tools menu click Analyze, and then click Performance.
Tips to Improve Query Performance
To improve query performance, try these tips:
Compact your database. Compacting can speed up queries because it reorganizes a table's records so that they reside in adjacent database pages, ordered by the table's primary key. This will improve the performance of sequential scans of a table's records because the minimum number of database pages will have to be read to retrieve all of the records.
Also, compacting the database flags all queries as needing to be compiled and regenerates the table statistics used in the query optimization process. Because the statistics are cached in memory, the statistics can become out-of-date over time, typically because of transactions being rolled back or because you turned off your workstation without closing the Microsoft Access database.
When you join tables, try to index the fields on both sides of a join. This can speed query execution by allowing the query optimizer to use a more sophisticated internal join strategy.
If you use criteria to restrict the values in a field used in a join, test whether the query runs faster with the criteria placed on the "one" side or the "many" side of the join. In some queries, you get faster performance by adding the criteria to the field on the "one" side of the join instead of the "many" side.
Index fields as much as possible. If a database is not updated frequently, then an index should be placed on all fields that are used in a join or in a restriction. With the inclusion of Rushmore query optimization technology in the Jet database engine version 2.0 (and higher), queries are able to take advantage of multiple indexes on a single table. This makes indexing many columns advantageous.
Use make-table queries to create tables from your query results if your data does not change often. Then, you can base your forms, reports, or other queries on the new tables.
Try to construct your queries so that Rushmore technology can be used to help optimize them. Rushmore is a data-access technology that permits sets of records to be queried efficiently. With Rushmore, when you use certain types of expressions in query criteria, your query will run much faster.
Rushmore does not automatically speed up all of your queries. You must construct your queries in a certain way for Rushmore to be able to improve them.
For more information about how to take advantage of Rushmore technology, search for "Rushmore queries" using the Microsoft Access 97 Help Index.
Use the BETWEEN...AND, the IN, and the EQUALITY (=) operators on indexed columns.
Redesign queries that use NOT IN because this is difficult to optimize. For example, the following query
SELECT Customers.* FROM Customers LEFT JOIN Orders ON Customers.[Customer ID] = Orders.[Customer ID] WHERE ((Orders.[Customer ID] Is Null));
will perform better than:
SELECT Customers.* FROM Customers WHERE Customers.[Customer ID] NOT IN (SELECT [Customer ID] FROM Orders);
NOTE: The Find Unmatched Query Wizard uses the first technique in the above example.
If you use the LIKE operator with parameters, try concatenating the query criteria in code. Because the value is unknown at the time the query is compiled, indexes will not be used. For more information about creating queries in code, please see the following articles in the Microsoft Knowledge Base:
117544 ACC2: Query by Form (QBF) Using Dynamic QueryDef (2.0)
136062 ACC: Query by Form (QBF) Using Dynamic QueryDef (95/97)
136460 ACC2: DLookup() Usage, Examples, and Troubleshooting (2.0)
136122 ACC: DLookup() Usage, Examples, and Troubleshooting (95/97)
If you use the LIKE operator with an asterisk (*) to find approximate matches, use only one asterisk at the end of character string to ensure that an index is used. For example, the following criteria uses an index:
Like "Smith" Like "Sm*"
The following criteria does not use an index:
Like "*sen" Like "*sen*"
When creating a query, add only the fields you need. In fields used to set criteria, clear the Show check box if you do not want to display those fields.
Avoid restrictive query criteria on calculated and non-indexed columns whenever possible.
Avoid calculated fields in nested queries. If you add a query containing a calculated field to another query, the expression in the calculated field may slow performance in the top-level query. In the example below, query Q1 is used as the input for query Q2:
Q1: SELECT IIF([MyColumn]="H","Hello","Goodbye") AS X FROM MyTable; Q2: SELECT * FROM Q1 WHERE X="HELLO";
Because the IIF() expression in Q1 cannot be optimized, Q2 also cannot be optimized. If an expression is buried deeply enough in a query tree, you can forget that it is there. As a result, your entire string of queries cannot be optimized.
A better way to write the example query above is as follows:
Q1: SELECT * FROM MyTable WHERE MyColumn = "H";
If expressions are necessary in the output, try to place them in a control on a form or report.
When grouping records by the values in a joined field, specify Group By for the field that is in the same table as the field you are totaling (calculating an aggregate on). For example, if your query totals the Quantity field in an Order Details table and groups by OrderID, specify Group By for the OrderID field in the Order Details table, not the OrderID field in the Orders table.
For greater speed, use Group By on as few fields as possible. As an alternative, use the First() function where appropriate.
For example, if your query is based on Customers and Orders, and you are grouping by Customer ID, Customer Name, and Customer City, you could use First() for Customer Name and Customer City instead of Group By, because the value will be the same for all rows with the same Customer ID.
If a totals query includes a join, try grouping the records in one query and adding this query to a separate query that will perform the join. This improves performance in some queries. For example, instead of the following query
SELECT Customers.[Company Name], Customers.[Contact Name], Max(Orders.[Order Date]) AS [MaxOfOrder Date] FROM Customers INNER JOIN Orders ON Customers.[Customer ID] = Orders.[Customer ID] GROUP BY Customers.[Company Name], Customers.[Contact Name];
break the query into two separate queries, as follows:
Q1: SELECT Orders.[Customer ID], Max(Orders.[Order Date]) AS [MaxOfOrder Date] FROM Orders GROUP BY Orders.[Customer ID]; Q2: SELECT Customers.[Company Name], Customers.[Contact Name], Q1.[MaxOfOrder Date] FROM Q1 INNER JOIN Customers ON Q1.[Customer ID] = Customers.[Customer ID];
Note that Microsoft Access usually "collapses" queries where possible. Other than situations specifically noted in this article, it should not make any difference whether you have a query based on another query, or create one query.
Use COUNT(*) rather than COUNT([Column Name]) to determine the number of records in a table. This is because there are special optimizations in the Microsoft Jet database engine that allow COUNT(*) to run much faster than COUNT([Column Name]) in some situations.
Avoid using domain aggregate (totals) functions, such as the DLookup() function, in a query that accesses table data. Instead, add the table to the query or create a subquery.
When defining a field in a table, choose the smallest data type appropriate for the data in the field. Also, give fields you use in joins the same or compatible data types.
Use field sorting judiciously, especially with nonindexed fields.
If you are creating a crosstab query, use fixed column headings whenever possible.
For more information about optimizing performance in Microsoft Access 97,search the Help Index for "optimization, system" or ask the MicrosoftAccess 97 Office Assistant.
For more information about query performance tips, see the "Querying Tips,Tricks, and Optimizations" document from Tech*Ed 95 (TE9511.EXE). Fordetails about how to obtain the TE9516.EXE file, please see the followingarticle in the Microsoft Knowledge Base:
129530 ACC2: Tech*Ed 95 Sessions on Query Tips and Tricks
For more information about optimizing database performance, see the"Database Optimization Techniques" document from Tech*Ed 95 (TE9516.EXE).
For details about how to obtain the TE9516.EXE file, please see thefollowing article in the Microsoft Knowledge Base:
129612 ACC2: Tech*Ed 95 Sessions on Database Optimization Techniques
For more information about using indexes, please see the following articlein the Microsoft Knowledge Base:
98793 ACC: Compound Indexes Must Restrict First Indexed Field
For more information about Rushmore technology, see the "Rushmore QueryOptimization" document (RUSHMR.EXE). For details about how to obtain theRUSHR.ZIP file, please see the following article in the MicrosoftKnowledge Base:
128388 ACC2: Rushmore Query Optimization Paper Available in Download Center