Expert Access™ 2007 Programming

Appendix B. Query Performance

Many different factors determine how efficient and performant your queries will be. Simply reordering elements of your query can make a world of difference. Other times, changes to your database schema will provide a more efficient design that the database engine can use to decrease the time it takes to run your queries. Even simple periodic maintenance can speed things up.

In this appendix, we discuss several issues and techniques you can use to design tables and queries that will make it easy to create efficient queries.

The Query Optimizer

Both the Access database engine and Jet database engine contain a Query Optimizer that analyzes a set of statistics and determines the optimal query execution strategy. Every time you save a query, the SQL is automatically run through the Optimizer, which determines the optimal way to run a query.

The Query Optimizer is a cost-based analyzer. The Optimizer examines the SQL and identifies a list of actions that can be performed when the query is executed. An estimated cost is generated for each action. The Optimizer identifies the least expensive set of actions that will generate the required results. This set of actions is known as the query execution strategy or query plan. The database engine stores the execution strategy along with the query so that when you run the query, the execution plan is already determined and you save the time of computing the optimal execution plan. Queries that have a stored execution ...