The Jet query processor supports advanced capabilities such as heterogeneous joins, queries based on other queries, and arbitrary expressions, including user-defined functions. But Jet must communicate with a server in standard SQL terms and refer only to functionality and data on that server. For any given query, Jet must determine what portions may be sent to each server involved for remote processing. The overriding goal is to send as much of the query to the server as possible, but some operations must be performed locally.
This assistant identifies Access queries that may cause Jet to process queries locally. Linked query performance is sometimes severely degraded when Jet must request full table scans. Full table scans are very time consuming especially when tables contain large amounts of data. The following constructs that may degrade query performance when linked to ODBC data sources are identified by the High-Overhead Query Report.
|
Top Values |
TOP N and TOP N PERCENT queries |
|
Built-In Functions |
When used in the WHERE, GROUP BY, ORDER BY or HAVING clauses. See the list of built-in functions not supported by standard SQL below. |
|
DISTINCT |
When the select clause contains unsupported built-in functions, Jet requires local processing. |
|
Outer Joins |
In determining where to performs joins, Jet adheres to the ODBC specification that limit mixing of inner and outer joins. Thus, any query Jet sends through ODBC will have a FROM clause containing any number of inner joins, but at most one outer join (perhaps combined with some inner joins). This means that some complex queries involving multiple outer joins will not be sent completely to the server; Jet may perform some of the higher level joins locally. |
|
Joining Linked (ODBC) and Local Tables |
When joins involve a local table/query with relatively few rows, and a remote table with many more rows, and the remote table's join column(s) is indexed, Jet will perform a "remote index join". Rather than fetch the entire remote table, and perform the join locally, Jet will request only rows that match the values in the local table/query, thus greatly reducing network traffic, and dramatically improving the performance of the query. |
|
Crosstab Queries |
Remote Execution of Crosstab Queries Jet sends some Crosstab queries to the server for evaluation; this can result in far fewer rows transferred over the network. Jet sends a simpler GROUP BY form of the Crosstab and transforms the result set into a true Crosstab. But this transformation does not apply to complex Crosstabs. The criteria you must meet to send the optimal amount of a Crosstab query to the server are: 1. Row/Column Headers may not contain aggregates. 2. The Value must contain only one aggregate. 3. There can be no user-defined ORDER BY clause. All other reasons for forcing local processing also apply.
Note: The High-Overhead Query Report only lists Crosstab queries that specify an ORDER BY clause. |
|
Closed-Range Restrictions
Negations |
It is generally better to specify (Field Between value1 And value2) than open-range restrictions, ex. (Field1 >= value And Field2 <value).
It is also a good rule-of-thumb to use (Not [Field] = Expr1) rather than (Field <> Expr1).
These generic optimization techniques help RDBMS optimization to choose the most effective evaluation path. |
|
StdDev |
StdDev is not a standard SQL aggregate function. |
Non-Standard Access Built-In Functions:
|
IIf |
TimeSerial |
|
Format |
TimeValue |
|
DateDiff |
StdDev |
|
DateAdd |
Var |
|
DateSerial |
CVar |
|
DateValue |
CodeDB |
|
IsDate |
QBColor |
|
IsTime |
RGB |
|
Fix |
Shell |
|
Int |
Environ[$] |
|
StringComp |
Command[$] |
|
Val |
CDate |
Note: The preceding information has been excerpted from a Microsoft white paper entitled Jet Database Engine - ODBC Connectivity.
Choose the High-Overhead Queries item from the Tools | Migration Tools menu or shortcut to display the following form.
QueryDef Name
The name of the high-overhead query candidate.
Press this button to display the associated Access QueryDef.
Reason
There are several reasons that may cause queries to perform poorly unless they are converted to SQL Server.
1. Non-Standard VBA functions like Format and IIf used in WHERE and HAVING clauses. (FUNCTION is show in the Reason field).
2. Non-Standard VBA functions like Format and IIf used in SELECT clauses when the Unique Values or Unique Rows property is set to Yes. (DISTINCT is shown in the Reason field).
3. Multiple outer-joins. (Outer Join is shown in the Reason field).
4. Queries that specify the Top Values property. (Top Values is shown in the Reason field).
QueryDef References
Displays any Forms, Reports, Queries, and Modules that contain references to the QueryDef. An efficient client-server architecture can be achieved by converting and reformulating high-overhead queries and all action queries. Queries that are only used by reports and those that will not be used to update underlying tables can be converted to SQL Server views, procedures or functions for optimal performance. High-Overhead queries that are updated by Forms or VBA code should be reformulated so that the constructs that will cause full table scans are removed. You can use the information contained in this field to analyze where queries are used to determine whether they can be converted or must be reformulated.
Show Type
Use this combo to display only Select or Action queries.
Rebuild QueryDef References
Press the Rebuild button to update "QueryDef References." This is only necessary after adding or modifying forms or reports that use high-overhead queries. Check the Search Modules box to search for QueryDef occurrences in Form and Global Modules. The following whole word strings are used for the search:
[QueryDef]
"QueryDef"
QueryDef (enclosed by spaces)