Which is more efficient - SQL or pre-written query?

A

AlShack

Hi

I need to change the recordsource of a subform based on user selection on
the main form.

Which is more efficient - using a SQL string or a pre-written query with
parameters?

I need to join together and group several tables (up to 8) with selection
criteria, and I don't want my users complaining!

Cheers
Al
 
A

Allen Browne

A dynamic query statement has a slight disadvantage, in that Access has to
calculate a query plan, whereas that has already been done with a saved
query.

Of course, if the data has changed significantly since the saved query was
created, the saved query might have the wrong query plan, and so the dynamic
statement would be better.

But those differences are academic. If the dynamic query means you can avoid
unnecessary criteria for the fields where the user did not enter any
criteria, will will be miles ahead of the saved query.

If you are interested in how to build a filter or query like that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
 
G

Graham R Seach

Al,

The concensus is that stored queries "should" be faster because their
execution plan should already have been created after the first time they
are run. Although some have observed a very slight performance benefit using
stored queries, others have not.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
A

AlShack

Thank you all for the most helpful responses, which have confirmed my thoughts.

I'm going to leave it as it is (ie dynamically generated SQL) rather than
queries for ease of maintenance - this, to me, outweighs the alleged
efficieny gains of pre-written queries.

Many thanks
Al
 
Top