Prev: making SQL the source for a report?
Next: SQL select statement question
From: Howard on 14 Jan 2010 16:57
My question is about ethics and efficiency.

I have a query that process the results of four other queries. All five
perform some calculations ans well as using criteria.

Is it more efficient / faster to hard code these queries as SQL within
the main query or is it better to leave them as separate saved queries
and use them as virtual tables within the main one? (I have to have
separate ones as I cannot perform the final calcs until the sub queries
have each performed their own calcs.)

How many times is a sub query called? If I have something like
SELECT thing FROM SELECT something else FROM table name where...

is the inner query executed just once and then the outer one run on the
result or is the inner one executed once for every result row in the
outer one?

I am in the process of changing all my saved queries into pure SQL that
is then run by db.execute, but I am also working on porting the whole
thing to Delphi and MSSQL where I shall be writing in pure SQL, so am
interested in the best approach to nested SQL.

Howard
From: Jeff Boyce on 14 Jan 2010 19:13
Howard

If you'll be porting your SQL, you'll probably want to check with the folks
who support the final destination. They may have different optimizations
than Access.

I've used both approaches (nested SQL statements, and "chained" queries),
and prefer chaining queries. While it may prove less efficient (and
certainly isn't as "elegant"), doing it that way means I can isolate any
piece of the overall task and make sure it runs correctly. A personal
perference...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Howard" <nospam(a)home.please.co.uk> wrote in message
news:4pWdneq1j-OrDtLWnZ2dnUVZ8midnZ2d(a)pipex.net...
> My question is about ethics and efficiency.
>
> I have a query that process the results of four other queries. All five
> perform some calculations ans well as using criteria.
>
> Is it more efficient / faster to hard code these queries as SQL within the
> main query or is it better to leave them as separate saved queries and use
> them as virtual tables within the main one? (I have to have separate ones
> as I cannot perform the final calcs until the sub queries have each
> performed their own calcs.)
>
> How many times is a sub query called? If I have something like
> SELECT thing FROM SELECT something else FROM table name where...
>
> is the inner query executed just once and then the outer one run on the
> result or is the inner one executed once for every result row in the outer
> one?
>
> I am in the process of changing all my saved queries into pure SQL that is
> then run by db.execute, but I am also working on porting the whole thing
> to Delphi and MSSQL where I shall be writing in pure SQL, so am interested
> in the best approach to nested SQL.
>
> Howard


From: John Spencer on 15 Jan 2010 08:22
I have used both methods and have found instances when one is more efficient
than the other. The funny thing is that using subqueries in SQL is sometimes
faster and other times "chained" is faster. I usually don't worry test the
alternatives unless I am having a performance problem as most often they give
the same performance to the human. If version a takes 1.1 seconds and version
b takes 1.05 seconds I really don't care which one I use.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Jeff Boyce wrote:
> Howard
>
> If you'll be porting your SQL, you'll probably want to check with the folks
> who support the final destination. They may have different optimizations
> than Access.
>
> I've used both approaches (nested SQL statements, and "chained" queries),
> and prefer chaining queries. While it may prove less efficient (and
> certainly isn't as "elegant"), doing it that way means I can isolate any
> piece of the overall task and make sure it runs correctly. A personal
> perference...
>
> Good luck!
>
> Regards
>
> Jeff Boyce
> Microsoft Access MVP
>
 | 
Pages: 1
Prev: making SQL the source for a report?
Next: SQL select statement question