ACC1x: Queries Sent to Remote SQL Server Run Slowly

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

A query sent to a remote SQL Server from Microsoft Access seems to run very slowly compared to other methods of querying SQL data.

CAUSE

This behavior can be due to either of the following causes:

• A parameter used in the query is not explicitly defined, causing Microsoft Access to send the entire query to SQL Server.

  Microsoft Access automatically takes care of all implicit data type conversions internally, whether you are running a query against native Microsoft Access tables or attached tables. However, you will find that your remote queries run much slower than you expect when you force Microsoft Access to interpret query parameter data types, because it cannot be certain what implicit type conversions the remote server provides. As a result, Microsoft Access will not send the entire query to the remote server, but instead will locally process the parts of the query having parameters, resulting in slower than expected query performance.

• You are referencing a function or command that is unknown to SQL in your query criteria.

  Because SQL Server does not know how to process user-defined Access Basic functions and some built-in Access Basic commands and functions, it cannot evaluate them. As a result, Microsoft Access must locally evaluate them, which can hinder query speed. This applies to all functions not known by SQL, such as the Access Basic IIf() function.

  Note that this behavior applies only when a function unknown to SQL is used in your query criteria. It does not apply when such a function is used in the query's output, except for aggregates such as StDev() that have no SQL equivalent.

RESOLUTION

Make sure to explicitly define all the query parameters before running the query, so that Microsoft Access can send the entire query to the server.

To explicitly define parameters, choose Parameters from the Query menu, and then enter the names and data types of all the parameters used in the query. The "Employee Sales by Country (Parameter)" query in the sample database NWIND.MDB demonstrates this method.

Also make sure to use only functions and commands known to SQL in your query criteria or, if possible, provide additional query restrictions to send to the server. This limits the amount of data returned to Microsoft Access for local user-defined function evaluation. For example, the following query will return all records that are not null:

   Like MyFunction(Forms]![Formname]![Fieldname],*)

   Like [Fieldname] or [Fieldname] Is Null

   Name=Forms!Formname!Fieldname, Type=<yourtype>

STATUS

This behavior no longer occurs in Microsoft Access version 2.0.

REFERENCES

Microsoft Access "User's Guide," pages 182-185