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

[Guest]

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

 

[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

 

[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

 

[onedaywhen]

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

From a code maintenance point of view, I find it is more efficient

(debugging, alterations to business logic, etc) to have SQL in
procedures in the engine. I find the fewer procedures the easier to
maintain. Often with dynamic SQL the equivalent code is fragmented
across many lines of front end code, making it harder to maintain.

If you have 'runtime efficiency' problem with cached execution plans,
consider writing more procs, each being more specific, although this
will make you code harder to maintain i.e. several queries which do
similar things may all need to be revised if the business logic
changes.

Jamie.

--

 

[Guest]

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