This article discusses how to optimize query performance in
Microsoft Access 2000, in Microsoft Access 2002, and in Microsoft Office Access
2003. The following topics are included:
- Query Optimizer for the Microsoft Jet database
- Query timing
- Analyzing performance
- Tips to improve query performance
This article assumes that your database has local tables
instead of linked (or attached) tables. If your tables are linked, this
information still applies. However, there are additional issues that affect
query performance with linked tables. For more information about improving
performance with linked tables, you can search the Microsoft Knowledge Base by
using the following string:
odbc and optimizing and tables
Query Optimizer for the Microsoft Jet database engine
The Jet database engine contains several components, but the most
important component to queries (and the most complex) is the Optimizer. The
Optimizer is cost-based. This means that the Optimizer assigns a time cost to
each query task and then chooses the least expensive list of tasks to perform
that generates the intended result set. The longer a task takes to perform, the
more expensive that task is.
To decide what query strategy to use,
the Optimizer uses statistics. The following factors are some of the factors
that 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 indexes are present
- How unique the indexes are
Based on these statistics, the Optimizer then selects the best
internal query strategy for dealing with a particular query.
statistics are updated whenever a query is compiled. A query is flagged for
compiling when you save any changes to the query (or its underlying tables) and
when the database is compacted. If a query is flagged for compiling, the
compiling and the updating of statistics occurs the next time that the query is
run. Compiling typically takes from one second to four seconds.
you add a significant number of records to your database, you must open and
then save your queries to recompile the queries. For example, if you design and
then test a query by using a small set of sample data, you must re-compile the
query after additional records are added to the database. When you do this, you
want to make sure that optimal query performance is achieved when your
application is in use. Note
You cannot view Jet database engine optimization schemes, and you
cannot specify how to optimize a query. However, you can use the Database
Documenter to determine whether indexes are present and how unique an index
For more information about the problem that may occur when you use the Database Documenter
in Access 2000, click the following article number to view the article in the Microsoft Knowledge Base:
ACC2000: Error using Database
Documenter if objects are open
There are two significant time measurements for a Select query:
- Time to display the first screen of data
- Time to obtain the last record
If a query returns only one screen of data, these two time
measurements are the same. If a query returns many records, 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 screen of data
and a total number of records that are returned by the query, such as "Record 1
." If it is faster for the Jet database engine
to display the first screen of data than to complete the query and then
retrieve the last record, you see a screen of data but no
in "Record 1 of N
value is blank until the query is complete, or
until you scroll down to the last record.
This behavior is the
result of the Jet database engine selecting one of two performance strategies:
- Complete the query, and then display data
- Display data, and then complete the query
You cannot control the strategy that is used. The Jet database
engine selects the strategy that is most efficient.
If you are using Microsoft Access 7.0, Access 97, Access 2000,
Access 2002, or Access 2003, 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 suggests 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 that are more specific to your database than the general
suggestions listed below in the "Tips to improve query performance" section.
To run the Performance Analyzer in Access 7.0, in Access 97, in
Access 2000, in Access 2002, or in Access 2003, follow these steps:
menu, click Analyze
, and then click
Tips to improve query performance
To improve query performance, try these tips:
- Compact your database
When you compact your
database you can speed up queries. When you compact your database, the records
of the table are reorganized so that the records reside in adjacent database
pages that are ordered by the primary key of the table. This improves the
performance of the sequential scans of records in the table because only the
minimum number of database pages now have to be read to retrieve the records
that you want. After you compact your database, run each query to compile the
query so that each query will now have the updated table statistics.
- Index a field
Index any field that is used to set
criteria for the query fields and the index fields on both sides of a join. Or,
create a relationship between these fields. When you create a relationship with enforced referential integrity, the Jet database engine creates an index on the foreign key if one does not already exist. Otherwise, the Jet database engine uses the existing index.
Note The Jet database engine automatically optimizes a query that
joins an Access table on your hard disk and an ODBC server table if the Access
table is small and if the joined fields are indexed. In this case, Access
improves performance by requesting only the required records from the server.
Make sure that tables you join from different sources are indexed on the join
- Select the smallest data type that is
When you define a field in a table, select the smallest
data type that is appropriate for the data in the field. Also, make sure that
fields that you plan to use in joins have the same data types or compatible
data types, such as Autonumber and Number (if the FieldSize property is set to Long Integer).
- Add only the fields that you must have
create a query, add only the fields that you must have. In fields that are used
to set criteria, click to clear the Show check box if you do
not want to display those fields.
- Save the SQL statement as a query
If the RecordSource property for a form or for report is set to an SQL statement,
save the SQL statement as a query and then set the RecordSource property to the name of the query.
- Avoid calculated fields
Avoid calculated fields in
subqueries. If you add a query that contains a calculated field to another
query, the expression in the calculated field may slow performance in the
top-level query. In the following example, query Q1 is used as the input for
Q1: SELECT IIF([MyColumn]="Yes","Order Confirmed","Order Not Confirmed") AS X FROM MyTable; Because the IIF expression in Q1 cannot be optimized, Q2 also cannot be
optimized. If an expression that cannot be optimized is nested in a subquery,
all the query cannot be optimized.
Q2: SELECT * FROM Q1 WHERE X="Order Confirmed";
An alternative way to construct
the query is as follows:
Q1: SELECT * FROM MyTable WHERE MyColumn = "Yes";
If expressions are required in the output, try to put the
expressions in a control on a form or on report. For example, you can change
the previous query to a parameter query that prompts for the value of MyColumn,
and then base a form or a report on the query. On the form or on the report,
you can then add a calculated control that displays "Hello" or "Goodbye,"
depending on the value that is in MyColumn.
Construct the query as
PARAMETERS [To see confirmed orders, enter Yes. To see unconfirmed orders, enter No.] Text; In the calculated control on the form or on report, type:
WHERE MyColumn = [To see confirmed orders, enter Yes. To see unconfirmed orders, enter No.];
=IIF([MyColumn]="Yes","Order Confirmed","Order Not Confirmed")
- Specify Group By
When you group records by the
values in a joined field, specify Group By for the field that is in the same
table as the field that you are totaling (calculating an aggregate on). For
example, in the Northwind.mdb sample database, if you create a query that
totals the Quantity field in the Order Details table and then groups by
OrderID, you can specify Group By for the OrderID field in the Order Details
table. If you specify Group By for the OrderID field in the Orders table,
Access must join all the records first and then perform the aggregate, instead
of performing the aggregate and then joining only the required fields.
For greater speed, use Group By on as few fields as possible.
Alternatively, use the First function if you can.
If a totals query includes a join,
consider grouping the records in one query and then adding this query to a
separate query that performs the join. When you do this, performance may be
improved with some queries.
- Avoid restrictive query criteria
query criteria on calculated fields and on non-indexed fields if you can. Use
criteria expressions that you can optimize.
- Test your query performance in a field that is used in a
join between tables
If you use criteria to restrict the values in a
field that is used in a join between tables with a one-to-many relationship,
test whether the query runs faster with the criteria placed on the "one" side
or on the "many" side of the join. In some queries, you may realize faster
performance by adding the criteria to the field on the "one" side of the join
instead of on the "many" side of the join.
- Index sort fields
Index the fields that you use
- Use make-table queries to create tables
data seldom changes, use make-table queries to create tables from your query
results. Use the resulting tables instead of queries as the basis for your
forms, your reports, or your other queries. Make sure that you add indexes
according to the guidelines that you read in this article.
- Avoid using domain aggregate functions
domain aggregate functions, such as the DLookup function to access data from a table that is not in the query.
Domain aggregate functions are specific to Access, and this means that the Jet
database engine cannot optimize queries that use domain aggregate functions.
Instead, add the query to the table that the function was accessing or create a
- Use fixed column headings
If you are creating a
crosstab query, use fixed column headings whenever possible.
- Use operators
Use the Between...And operator, the In operator, and the = operator on indexed fields.
- Optimize performance on the server
For bulk update
queries against ODBC data sources, optimize performance on the server by
setting the FailOnError property to Yes.
For more information about optimizing performance in Microsoft Access 2000, click Microsoft Access Help
menu, type optimize
in the Office Assistant or the Answer Wizard, and then click Search
to view the topic.
For more information about optimizing performance
in Microsoft Access 2002, click Microsoft Access Help
menu, type Improve performance of an Access
in the Office Assistant or the Answer Wizard, and then click Search
to view the topic.
For more information about improving performance in Access
2003, click Microsoft Office Access Help
on the Help
menu, type Improve performance of an Access database
in the Search for
box in the Assistance pane, and then click Start searching
to view the topic.
For more information about using indexes in Access
2000, click the following article number to view the article in the Microsoft Knowledge Base:
ACC2000: Compound indexes must
restrict first indexed field
Article ID: 209126 - Last Review: November 28, 2007 - Revision: 2.5
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
- Microsoft Access 2000 Standard Edition
|kbquery kbhowto kbperformance kbinfo kbusage KB209126|