All Access documentation I've ever read says that a query stored in the
database will run faster than an SQL placed directly in code.  I've tested
this and tested this, but can find no examples of a stored query running
faster than dynamic SQL.  Matter of fact, a couple of my SQL strings ran
faster from code than their stored counterparts.  Any thoughts?

Well, this is another "it depends" situation.

A query you build "on the fly" in code must be compiled every time.  For
most queries, this doesn't take long, however.  In general, stored
queries -- especially ones that you've executed successfully before so that
Access has had a chance to compile and store a "plan" -- should run a tad
faster.  HOWEVER, if you have thousands of queries or other objects in your
database, it can take longer for Access to *find* the stored query than it
would to compile a new one!

--
John Viescas
author, "Running Microsoft Access 97"
http://www.amazon.com/exec/obidos/ISBN=1572313234/

 If you use a SQL string, then the Jet database engine create a query
execution plan on the fly when the query is run. If you use a saved
query, then the query plan is generated and saved the first time the
query is run after saving it, or the first time the query is run after
compacting the database that contains the query.

Using saved queries (after that first use), you do save the very small
amount of time it takes to generate a query plan. On the other hand,
using a temporary query you get the advantage of knowing that your
query plan is based on the most up to date statistics.

With lots of data, and data that changes in size markedly, there are
going to be many times when the saved query would not run as fast as
the temp query because its query plan may not be as good as the one
generated at the moment.

I like using saved parameter queries because it is a more object
oriented approach -- you can make a change in one place to the query
rather than in many places in your code. And it results in somewhat
cleaner, more maintainable code. But there are many cases where the
flexibility of building a SQL string in code is required, and in those
cases I never worry about the tiny bit of extra time required to
generate a query plan.

This issue points out a seldom understood argument for frequently
compacting all databases -- both the front end and the back end. The
back end needs to be compacted to update the database statistics, and
the front end needs to be compacted to assure that fresh query plans
are created for all saved queries the next time they are run.

If you are interested in running query timings and tuning performance,
you should consider using the ISAMStats and QueryPlan functions, two
undocumented functions which are both discussed in the Microsoft Jet
Database Engine Programmer's Guide, from Microsoft Press.

 -- Andy

 -- Andy

I am not an expert on the functions within Access, but I have noticed over
the years that something written once and accepted as a truism can change
very quickly and still be accepted as correct.

To my knowledge any SQL code in a form now is optimised to the same lavel as
a query (Access 8) but maybe Michael Kaplan or similar expert can tell the
real facts.

--
From Richard in Woy Woy, Australia
Specialising in POS applications and
program conversions to MS Access.

What you are referring to is the fact that Access 8 (97) creates
hidden saved queries when you use a SQL string for a form/report
recordsource or for a combo/list box rowsource. This does not,
however, apply to SQL strings created and used in code.

 -- Andy

Not necessarily. A compiled query may run inefficiently if the data sources
have changed in size significantly. In other words, if I compile my query
using development test data on my development machine - the same query may
have a different execution plan once it actually gets out into the
production environment. The point is that you must be very careful with the
assumption which you make below.

Abbot Cooper

(remove "_NoSpam_" from address when sending)