How do queries work?

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

Guest

If I have a sub-query that lists all items and then I have a parent query
that lists all items, when run how does it work? ie. Does the parent query
run and gain all info then it calls the sub query, hence the sub query is
passed the link key so it does a key look up to retrieve the required rows or
does the sub query look through all rows based on it's own criteria?

Just thinking about performance?
 
Hi Paul

There is not a simple answer to your question, because:
a) Microsoft does not publish the details of how the query engine works, and
b) There are so many factors to consider, that the query optimiser can make
different choices in different cases, even with identical table structures
(i.e. depending on the actual data in the tables.)

JET is actuallly very good most of the time at optimizing query. If you are
interested in tracing through what it is doing, these links might be useful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp
http://builder.com.com/5100-6388-5064388.html
http://msdn.microsoft.com/archive/d...MicrosoftAccessMicrosoftJetDatabaseEngine.asp

Just for the sake of clarity a subquery (a complete SELECT query statement
that is part of another query statement) is not the same as a stacked query
(a query that uses another query as a source "table".) I was not sure from
your question which you meant.
 
In a nutshell, you can query a table, or query another query. You appear to
be referring to querying another query.

Using your terms, your Parent Query gathers and displays data from a table.
You then designed a Sub Query to gather and display data from the Parent
Query. What happens? When you run the Sub Query, Access opens the Parent
first to make the data available, then opens and displays the Sub Query.
 
Back
Top