How Do Queries Work?

[bhipwell via AccessMonster.com]

Before I begin rebuilding a particular query in my database, I need to
understand how queries go about running. Let me explain...

I have the following two tables:

Employee
Position

Say I put these two tables in a query. The first item in the query would be
EmployeeID. The other fields contain a number of calculations. In the query,
I have set the criteria of the EmployeeID to match a form.

My question...does the query...

A) First filter down to the employee then do the calculations in the other
fields or
B) Do the calculations for every employee then filter down to the specific
employee

I have a large query which is running a bit slow and am looking to speed it
up. Should the query calculate every employee then filter through the
criteria, I know what I need to do to speed up the query. If the query first
filter through the criteria then does the calculations, I need to begin some
rethinking.

(Note: I have posted my query structure elsewhere and the general concensus
is the query is fine and the database is normalized. It is just the amount
of data and mathmatics slowing the database down.

BH

Thanks!

 

[Rick Brandt]

Say I put these two tables in a query. The first item in the query
would be EmployeeID. The other fields contain a number of calculations.
In the query, I have set the criteria of the EmployeeID to match a
form.

My question...does the query...

A) First filter down to the employee then do the calculations in the
other fields or
B) Do the calculations for every employee then filter down to the
specific employee

A

 

[John W. Vinson]

A) First filter down to the employee then do the calculations in the other
fields or
B) Do the calculations for every employee then filter down to the specific
employee

Retrieve as few records as you can. Disk access is the slow step; so you
certainly would want to do A, rather than retrieving all the records in the
table, doing calculations on all of them, and then throwing away 95% of what
you've done!

 

[bhipwell via AccessMonster.com]

Great news! Just wanted to make sure the query doesn't run all the
calculations then apply the query. This will solve my 16 query dilemma that
you have already helped me with.

BH

 

[bhipwell via AccessMonster.com]

..."apply the query" should have been ..."apply the criteria"