dynamic queries and performance

[philfer]

Hello,

How much slower is it really to use dynamic queries.

In Access and Access VBA books which cover ADO you always see recordsets built with SELECT queries in VBA. These queries, which I assume are the definition of dynamic queries, as they are built on the fly with parameters supplied by the user or other variables, seem to be used all the time, but I assume slow down the database as they are compiled each time.

I guess one must balance using too many stored queries as they add to the size of the database. (But surely you can achieve the same by saving a parameterised stored query and passing the parameters from user input or other variables)

But which is really the best

Also can you use a temporary QueryDef object instead or achieve the same reulsts with the CurrentData object.


I am trying to build the quickest, most efficient, optimised database possible and know that these issues are important.

Any help please

Thanks
Phil

 

[Banana]

One thing to remember is that whenever you create a dynamic query, Jet *always* create a execution plan first. With saved queries, (or SQL statement stored in a property of form/control such as its recordsource or rowsource, after Access 2000 (Or is it 2002?)), Jet already has created the execution plan at time of query being saved so there is no lost time waiting for it to figure how it would execute a given query.

This may be a good thing or not. It may be a good thing if the query is doing something same and same again and again or refers a stable column (e.g. it looks up key columns). OTOH, you may want to re-compile query for columns that changes too frequent to be of any use for old execution plan. But latter is going to be rare compared to former, so most of time we usually benefit from having pre-compiled execution plan.

I make generous use of queries, especially parameter queries. I actually don't concern with the size of database; I'm perfectly fine if it has to have 100s of saved queries, as long they're all needed at one point or other. Even so with all their usefulness, parameter queries does have their limits. For example, if we had a search form that allowed users to select which columns to search upon by, it would be hard to write a parameter query that allow for such level of optionality.

Consider the default parameter query SQL: (To keep example simple, we'll just assume we can only search by one value per column.)

SELECT a, b, c, FROM foo WHERE a = ? AND b = ? AND c = ?;

Now suppose the user fills in values to search by upon a and c, but not b. Thus SQL ought to be:

SELECT a, b, c FROM foo WHERE a = X AND c = Y;

But with parameter query we are stuck with having to provide a value for b. We could say *, but that would exclude null values. We could say * OR IS NULL, but that throws out all optimization available for that query.

Thus, dynamic SQL makes sense for this kind of query where we cannot predict which columns to search upon.


EDIT: Here's a old thread discussing this deeper. HTH.