Q about query optimisation, and query compilation 
Author Message
 Q about query optimisation, and query compilation

Hi

I read in many documents that for optimal query performance you should not
construct SQL in the fly and fire it at Jet but use stored queries and
parameterise.

I tend to code in such a way that I create a general query and then create
SQL based on that query but append a filter, I thought, in the same way in
which you can pass a filter to a form or a report and it gets applied to the
underlying table/query.

The benefit of this, as I see it, is that when you are developing, you can
construct filter on any of the fields and/or combination of fields in the
underlying query rather than only to those fields parameterised by the query
so you end up with a function like

SQL = SQLCreateSelect("*", QueryName, Filter)
set rs = db.openrecordset(SQL...)

However, I'm beginning to get a bit concerned that I may be losing
performance as I'm not using the query in a compiled state. This is
exacserbated when the underlying query could be quite complicated.

2 questions.

1) Have I lost the benefits of the query compilation by constructing SQL
based on an underlying query ? If I have then doesn't a form or report loose
this as well as it obviously constructs  the underlying query and filter
passed in the openform/openreport arguments
2) Have I explicit (rather than implicit via query execution) access to the
query compilation process ?

what do you think ?

Simon



Wed, 11 Jul 2001 03:00:00 GMT  
 Q about query optimisation, and query compilation
Hi Simon,

It is not clear to me from your message whether you understand that
queries are always run "in a compiled state". The only issue is when
the query gets "compiled". If you use a SQL string, then that
compilation takes place on the fly when the query is run. If you use a
saved query, then the query plan is generated and saved (which is what
you are referring to as compilation) 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.

There are advantages to both. 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.

I think that the way you are doing it is fine. The only exception
might be if you were running a query repeatedly within a loop, but
even there you could get around it by creating a temporary querydef
object (with a name of ""). As with all performance questions, the
only way to answer it is by testing, but overall I think the
performance advantages of using saved queries has been overrated.

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 bit of extra time required to generate a
query plan.

Be aware that by combining a saved query with additional SQL criteria,
as you described in your message,  you run the risk of getting the
worst of both worlds if the query plan for the base query is out of
date.

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.

 -- Andy

Quote:

>Hi

>I read in many documents that for optimal query performance you should not
>construct SQL in the fly and fire it at Jet but use stored queries and
>parameterise.

>I tend to code in such a way that I create a general query and then create
>SQL based on that query but append a filter, I thought, in the same way in
>which you can pass a filter to a form or a report and it gets applied to the
>underlying table/query.

>The benefit of this, as I see it, is that when you are developing, you can
>construct filter on any of the fields and/or combination of fields in the
>underlying query rather than only to those fields parameterised by the query
>so you end up with a function like

>SQL = SQLCreateSelect("*", QueryName, Filter)
>set rs = db.openrecordset(SQL...)

>However, I'm beginning to get a bit concerned that I may be losing
>performance as I'm not using the query in a compiled state. This is
>exacserbated when the underlying query could be quite complicated.

>2 questions.

>1) Have I lost the benefits of the query compilation by constructing SQL
>based on an underlying query ? If I have then doesn't a form or report loose
>this as well as it obviously constructs  the underlying query and filter
>passed in the openform/openreport arguments
>2) Have I explicit (rather than implicit via query execution) access to the
>query compilation process ?

>what do you think ?

>Simon



Thu, 12 Jul 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Query Optimisation

2. how can I build 2 queries in 1 query(VB SQL query)

3. query compilation

4. Selecting from a Parameterised query in a CreateQuerydef'd query

5. function used in query repeats for each query value

6. query of a query

7. SQL Query - MAX, Grouping and Sub query

8. Query Object vs VBA Query

9. Dialog box displayed during query insert or query update

10. Make Table Query vs Append Query

11. a query within a query (sort of)

12. Query Spy- FREE Access tool to Analyze Queries

 

 
Powered by phpBB® Forum Software