How are queries executed?

[David Cleave]

Hi all

I wanted to ask about how queries are executed in Access. Let's say you
design a query which itself contains two queries each linked through a join
field.

Does it execute both these queries first, then discard the records where the
join fields don't match? Or does it only produce records in the first place
where the join fields match?

On a similar note, when you run a query with a parameter, does it find all
records and then discard those which don’t meet the parameter, or does it
only return records which meet the parameter in the first place?

Thanks for your help

David

 

[John Vinson]

On Mon, 1 Nov 2004 05:20:03 -0800, David Cleave <David

Hi all

I wanted to ask about how queries are executed in Access. Let's say you
design a query which itself contains two queries each linked through a join
field.

Does it execute both these queries first, then discard the records where the
join fields don't match? Or does it only produce records in the first place
where the join fields match?

On a similar note, when you run a query with a parameter, does it find all
records and then discard those which don’t meet the parameter, or does it
only return records which meet the parameter in the first place?

If the field is indexed, it uses the Index to filter the records down
to those which match. The algorithms that Access uses are pretty
effective at avoiding unnecessary full-table scans - *if* you give it
the tools! For instance, if you'll routinely be searching by last name
and first name, it would be helpful to have non-unique indexes on both
fields.

I am not certain how the query optimizer handles the case of a query
based on queries, but my guess is that it gets compiled into a single
operation. There's tricks to get it to "Show Plan" which I haven't
explored yet, but they might be able to resolve this question!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps