Efficiency of Nested queries

[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.

 

[Allen Browne]

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.

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.

 

[Ted Allen]

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.

See "Micorosft's Unsupported JET