ACC: Tips for Optimizing Queries on Attached SQL Tables

ID: Q99321

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

Special considerations must be made for performance optimization of queries built on attached SQL database tables. An SQL database, for this article, is defined as any client/server database that supports some level of ANSI SQL as an intrinsic part of the database's programming language. Optimal query strategy for these queries is to ensure that all query operations are performed on the server. This article outlines tips on how to ensure that queries against attached SQL database tables are performed on the server.

This article assumes a basic understanding of client/server computing environments and architectures.

MORE INFORMATION

The key to improving query performance on attached SQL database tables is to ensure that no data is filtered on the client. Filtering data on the client increases network traffic and does not allow for leveraging of advanced server hardware, essentially turning a client/server system into a file server system. To this end, keeping the query evaluation on the server reduces overhead and keeps an application running as fast as possible.

Generic query optimization techniques should not be ignored when you are using attached SQL database tables. "WHERE clause" restrictions, such as equality or range comparisons, and sorting should still be performed on indexed fields. For more information on query optimization, call Microsoft Sales Information Center at 800 426-9400 to order a "white paper" on SQL Server optimization. Other SQL database vendors may provide the same information for their customers.

Use care when implementing intrinsic or user defined functions (UDFs) in query fields or when using criteria that are not supported on the server. Generally, SQL databases have functionality that corresponds to most standard Microsoft Access functions, but each server will be different.

Many intrinsic Microsoft Access functions have direct back-end correspondents. Microsoft Access asks the ODBC driver about intrinsic function support and performs the appropriate mappings.

You can use UDFs and Microsoft Access intrinsic functions without server equivalents when they are accompanied by server-capable restrictions that restrict the data. For example, the following query


   Query1a: SELECT * FROM MillionRowTable WHERE Funk1(col1) = 10

returns the whole table and evaluates Funk1(col1) = 10 locally, whereas the following query


   Query1b: SELECT * FROM MillionRowTable WHERE Funk1(col1) = 10
                      AND LastName BETWEEN 'g' AND 'h'

sends the BETWEEN 'g' AND 'h' restriction to the server, returns the qualifying rows, and evaluates Funk1(col1) on only those rows.

Non-remote-capable SELECT list items do not force a query to be executed locally, unless they are used with unique values or a totals query (DISTINCT/GROUP BY). For example, the following query


    Query2a: SELECT * FROM MyTable WHERE Format(col1, ...) = 10

returns the whole table and causes the WHERE clause to be evaluated locally. However, the following query


    Query2b: SELECT Format(col1,...) FROM MyTable WHERE col2 = 10

sends "SELECT col1 FROM MyTable WHERE col2 = 10" to the server, presumably returning far less data over the network. It then locally evaluates Format() on the col1 values returned.

Of the following two queries, Query3a is sent completely to the server. Query3b sends "SELECT col1 FROM MyTable" and performs the Format() function, and therefore the DISTINCT clause, locally.


    Query3a: SELECT DISTINCT col1 FROM MyTable
    Query3b: SELECT DISTINCT Format(col1,...) FROM MyTable

The following two queries are performed as follows: Query4a is sent completely to the server. Query4b sends "SELECT col1 FROM MyTable" and performs the StdDev() aggregate function locally, since it's not a SQL standard function.


    Query4a: SELECT Sum(col1) FROM MyTable
    Query4b: SELECT StdDev(col1) FROM MyTable

Crosstab queries present unique restrictions, some pertinent to all queries, some to crosstab queries only:

Only standard aggregate (totals) functions, such as Count(), Sum(), Min(), Max(), and Avg(), can be used.

Aggregate functions cannot be used as row or column headers.

Only one aggregate function can be used in the "value."

Nothing can be sorted.

If a fixed-value list of column headers is supplied, the value cannot be embedded in an expression.

Restriction #1 applies to all queries against SQL databases from Microsoft Access. Except for #4, the others are uncommon and are crosstab/SQL database specific. Crosstab queries are not SQL standard, and are not generally supported on SQL database servers. However, if the rules above are not violated (which is the case for most simple crosstab queries), Microsoft Access can reformulate the query as a standard GROUP BY query, send it remotely, return only the aggregation result, and transform it into a crosstab locally.

Open-ended restrictions do not use indexes on SQL databases. Typically, a SQL WHERE clause reading


   WHERE col1 > 1000

will be slower than


   WHERE col1 between 1000 and 1000000000

This is a server problem, not a Microsoft Access problem, but it can affect Microsoft Access performance when a SQL database is used as a back end.

When you are using wildcard characters, take special care to ensure that the correct wildcards are used. SQL Server supports the use of % and _ rather than ? and * for wildcards. For more information about using wildcards, please see the following article in the Microsoft Knowledge Base:

Q92682 ACC1x: Parameter Queries, Wildcards, and SQL Server

Keywords : kbusage OdbcOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbinfo