The Query Optimizer
The Microsoft Jet database engine contains several components, but the most
important to queries (and the most complex) is the Optimizer. The Optimizer
is "cost-based," meaning that it assigns a time cost to each query task and
then chooses the least expensive list of tasks to perform that generates
the desired result set. The longer a task takes to perform, the more costly
or 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 number
of data pages in a table, the location of the table, whether or not
indexes are present, how unique the indexes are, and so on. Based on these
statistics, the Optimizer chooses the best internal query strategy for
dealing with a particular query.
The statistics are updated whenever a query is compiled. A query is
flagged 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 query
is flagged to be compiled, the compiling and updating of statistics occurs
the 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 should
open and save your queries to recompile them. For example, if you design
and test a query using a small set of sample data, you should recompile
the query after more records are added to the database. This ensures
optimal query performance once your application is in use.
NOTE: You cannot view Jet database engine optimization schemes or specify
how to optimize a query. However, you can use the Database Documenter to
find out whether indexes are present and how unique an index is. For more
information about the Database Documenter, search on "Database Documenter"
using the Microsoft Access 97 Help Index.
NOTE: The Database Documenter is called the Database Documentor in
Microsoft Access 2.0 and 95.
Query Timing
There are two significant time measurements for a Select query: time to
display the first screenful of data and time to obtain the last record. If
a query returns only one screenful of data, these two time measurements
are the same. If a query returns many records, then these time
measurements can be very different.
If the two measurements are the same when you view a Select query in
Datasheet view, you see a screenful of data and a total number of records
returned by the query such as, "Record 1 of N." If it is faster for the
Jet database engine to display the first screenful of data then to
complete the query and retrieve the last record, you see a screenful of
data but no "N" in "Record 1 of N". The "N" value is blank until the query
is complete or you scroll down to the last record.
This behavior is the result of the Jet database engine choosing one of two
performance strategies: complete the query, and then display data; or
display data, and then complete the query. You cannot control which
strategy is used; however, the Jet database engine will choose whichever
is most efficient.
Analyzing Performance
If you are using Microsoft Access 7.0 or Microsoft Access 97, you can use
the Performance Analyzer to analyze queries in your database. Because the
query performance analysis is closely tied to the Jet database engine, the
Performance Analyzer will suggest adding indexes only when the indexes will
actually be used by the Jet database engine to optimize the query. This
means that the Performance Analyzer can provide performance tips which are
more specific to your database than the general suggestions listed below in
the "Tips to Improve Query Performance" section of this article.
To run the Performance Analyzer in Microsoft Access 7.0 or Microsoft Access
97, 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:
The following criteria does not use an index:
- 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.