Simple and complex optimizable expressions and Rushmore optimization

Microsoft Access can optimize simple expressions or complex expressions in the Criteria row of the query design grid, or in a WHERE clause in an SQL SELECT statement. For certain types of complex expressions, Microsoft Access can use Rushmore, a data access technology used by the Microsoft Jet database engine, to achieve a greater level of optimization.

Simple optimizable expressions

A simple optimizable expression can form an entire expression or can appear as part of an expression. A simple optimizable expression takes one of the following forms:

Indexed field Comparison operator Expression

[Order Date]=#09/15/96#

Expression Comparison operator Indexed field

#11/1/96#<[HireDate]

In a simple optimizable expression:

Indexed field can either be a field that has its own index or a field that's the first field in a multiple-field index.
Comparison operator must be one of the following: <, >, =, <=, >=, <>, Between…And, Like, In.
Expression can be any valid expression, including constants, functions, and fields from other tables.

For examples of simple optimizable expressions, click .

Complex expressions

A complex expression is created by combining two simple expressions with the And or Or operator. A complex expression takes one of the following forms:

Simple Expression And Simple Expression

Simple Expression Or Simple Expression

A complex expression is fully or partially optimizable depending on whether one or both simple expressions are optimizable, and which operator you used to combine them. A complex expression is Rushmore-optimizable if all three of these are true:

The expression uses And or Or to join two criteria.
Both criteria are made up of simple optimizable expressions.
Both expressions contain indexed fields. The fields can be indexed individually or they can be part of a multiple-field index.

Note You can optimize multiple-field indexes if you query the indexed fields in the order they appear in the Indexes window, beginning with the first indexed field and continuing with adjacent fields (up to and including 10 fields). For example, if you have a multiple-field index that includes the LastName and FirstName fields, you can optimize a query on LastName or on LastName and FirstName, but you can't optimize a query on FirstName.

The following table indicates the level of query optimization for different combinations of simple expressions in query criteria.

Expression	Operator	Expression	Query result
Optimizable	And	Optimizable	Fully optimizable (using Rushmore)
Optimizable	Or	Optimizable	Fully optimizable (using Rushmore)
Optimizable	And	Not optimizable	Partially optimizable (doesn't use Rushmore)
Optimizable	Or	Not optimizable	Not optimizable
Not optimizable	And	Not optimizable	Not optimizable
Not optimizable	Or	Not optimizable	Not optimizable
--	Not	Optimizable	Not optimizable
--	Not	Not optimizable	Not optimizable

You can also use parentheses to group combinations of simple expressions. The preceding rules also apply to combinations of expressions grouped with parentheses.

Once you've combined simple optimizable expressions into complex expressions, these complex expressions can, in turn, be combined to form even more complex expressions that might be optimizable according to the preceding rules.

For examples of complex optimizable expressions, click .

Notes

The Count function is highly optimized for queries using Rushmore. For more information about the Count function, click .
Rushmore queries will work with Microsoft Access tables, as well as with Microsoft FoxPro and dBASE tables (.dbf files). You can't use Rushmore with ODBC data sources, since Microsoft Access sends these queries to the ODBC data source instead of processing them locally.