Visual Basic Concepts

Choosing a DAO Query Processor for Use with Jet

Once the database engine has been initialized and the connection established, your application can begin to fetch information from the database. When working with remote database engines that are capable of executing SQL queries, you can usually choose how and where the query is processed. The query used to build a cursor can be run by either the Jet query processor on the client machine, or by the remote database query processor on the server. In some cases you have no choice, because the type of query (or another consideration) forces you to use a particular option.

The query processor you use can have a significant impact on the performance of your application. For most queries the Jet query processor breaks down your query and submits intelligent subqueries to the remote server for processing. These subqueries return subsets of the data used to complete the query on the workstation. However, when Jet is unable to perform this type of query management, it might try to bring entire tables (or large portions of those tables) back to the workstation for further processing. If this happens, your query might exhaust all workstation resources or exceed query timeout limitations.

By forcing Jet to perform queries entirely on the remote server, you can prevent resource-constrained queries from overwhelming your application. However, in many cases, a redesign of your application's approach to the query might prove to be just as beneficial for performance. For example, if your query returns more than a few hundred rows from the server, running the query on the server might improve performance, but the resource demands on the workstation and the network might still be fairly significant.