Efficiency of Nested queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm struggling to make a complex query as fast as possible.
If I have nested queries does the nested query run in full before the query that reads it runs or does some optimization take place?. For example:

Lets say I have a table of 50,000 rows of which 100 records are 'open'.
I need to display the open rows but only where the user = 'XYZ'. To determine the user requires a join with another table.

Is it more efficient to have 2 queries where the first query selects just the open records and the second query does the join (thus the join only applying to 100 records), or is it more efficient to do it with one query.
 
The JOIN will probably be the most efficient way to achieve this.

Access uses a remarkably intelligent set of algorithms to optimise your
queries, but it does not really expose these to you. There are some
undocumented ways to see what's going on. See "Micorosft's Unsupported JET
Optimization Tools" at:
http://msdn.microsoft.com/library/en-us/dnacbk02/html/odc_4009c15.asp?frame=true#odc_4009c15_topic4

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

scimitar said:
I'm struggling to make a complex query as fast as possible.
If I have nested queries does the nested query run in full before the
Click to expand...
query that reads it runs or does some optimization take place?. For example:
Lets say I have a table of 50,000 rows of which 100 records are 'open'.
I need to display the open rows but only where the user = 'XYZ'. To
Click to expand...
determine the user requires a join with another table.
Is it more efficient to have 2 queries where the first query selects just
Click to expand...
the open records and the second query does the join (thus the join only
applying to 100 records), or is it more efficient to do it with one query.
 
Allen, Thank you for that link. I found that article
very interesting and informative.

-Ted Allen
-----Original Message-----
The JOIN will probably be the most efficient way to achieve this.

Access uses a remarkably intelligent set of algorithms to optimise your
queries, but it does not really expose these to you. There are some
undocumented ways to see what's going on.
Click to expand...
See "Micorosft's Unsupported JET
 
Back
Top