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 processes on the server side 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. Restrictions on WHERE clauses, such as
equality or range comparisons and sorting, should still be performed on
indexed fields.
For more information on SQL Server 7.0 query optimization, see the following on the Microsoft TechNet Web site:
http://msdn.microsoft.com/en-us/library/aa226170(SQL.70).aspx
Other SQL database vendors may provide the same
information for their customers.
Use care when implementing intrinsic or user-defined functions
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 is
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 user-defined functions 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, because it is 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
is 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 an SQL database is used as a
back end.
When you are using wildcard characters, take special care to ensure
that you use the correct wildcards. SQL Server supports the use of %
and _ rather than ? and * for wildcards.