Query efficiency in Access and Access' processes in running a query 1

[KevinsHandle]

I am looking for some fairly technical information on the processes Access applies to queries. My interest is mainly in getting access to work as little as possible and thus respond as quickly as possible.

So that there is some context for this let's say that I am querying invoices and the WHERE conditions for my query are that the invoice be dated in the first quarter, the amount be greater than $150.00 and the buyer be either "Johnson Development" or "Johnson Construction".

Questions:

Does it matter in what order of field the WHERE clause is constructed?

Would it help to run any of the conditions as sub queries?

How does Access actually process multiple conditions?

Thanks for any insight.

Kevin

 

[dhookom]

I would think this might take some experimentation. However I would make sure there are indexes on invoice date, amount, and buyer. I don't know how you are storing the date but I would expect performance to be optimized when using:

InvoiceDate BETWEEN #1/1/2012# AND #3/31/2012#

rather than

Year(InvoiceDate) = 2012 AND DatePart("q",InvoiceDate) = 1

Tech Republic has an article Use Microsoft Jet's ShowPlan to write more efficient queries

Duane
Hook'D on Access
MS Access MVP

 

[KevinsHandle]

Duane,

Thank you for the response and particularly the info on Jet's SHOWPLAN. I have been working with Access since the early 90s when I gave up on FoxPro and Clipper. I have never heard of or seen the SHOWPLAN feature. Does MS have a site where such things are discussed/catalogued?

Thx

Kevin

 

[dhookom]

I'm not sure what's available. Just search the web to see what you find.

Duane
Hook'D on Access
MS Access MVP