Using Stored Queries Instead of Dynamic SQL

A common requirement of applications is the ability to build an SQL string based on values that the user provides. This technique is often referred to as using dynamic SQL. Dynamic SQL is easy to use, and you don’t have to store queries in the local Microsoft Jet database. However, performance may suffer. Using dynamic SQL requires that Microsoft Jet perform numerous steps, especially when using ODBC data, as described here:

1. Microsoft Jet parses the SQL string.
   
   
2. Microsoft Jet compiles the SQL string, determining which parts go to the ODBC server.
   
   
3. Microsoft Jet builds a server-specific query and sends it to the ODBC server.
   
   
4. The ODBC server parses and compiles the SQL string.
   
   
5. The ODBC server retrieves the results and returns them to Microsoft Jet.
   
   
6. DAO evaluates the results and builds the appropriate Recordset and Field objects.

The alternative to using dynamic queries is to save the queries as stored queries in the local Microsoft Jet database. After a query has been saved and compiled once, Microsoft Jet can reduce the number of steps required to execute that query.

In comparison to dynamic queries, the Microsoft Jet engine performs the same steps, except steps 1 and 2 only need to be done the first time a query is executed. After that, only steps 3 through 6 need to take place.

If you need to pass criteria to a saved query, you can create a parameter query and re-execute the query to use the criteria as new parameter values. This yields substantial savings, especially for client/server applications. If you don’t need to update the results and the query only uses server data, you can use a saved SQL pass-through query.

It’s also important to note the difference between dynamic queries and stored queries when your application is used on workstations with less than ideal memory configurations. When Microsoft Jet executes a dynamic query, it has to load several different engine modules to parse, optimize, and execute the query. Each of these modules takes up memory. When you create and store a query, Microsoft Jet parses it and stores it in a compiled state. This eliminates the need to load the modules responsible for parsing and optimization, and reduces the memory requirements of Microsoft Jet.