[AccessD] Query tuning in Access?

Erwin Craps - IT Helps Erwin.Craps at ithelps.be
Thu Feb 24 02:15:44 CST 2005


Simple question, hard to answer.
A few things I learned the hardway when having lots of records (I'm in
one table at +/-1.400.000 records)

1) When having cascading queries (a query based on a query) only sort or
group in the last query (when posible).
2) When having cascading queries (a query based on a query) try to
filter (by using parameters to be able to stil use VBA) in the first
one. 
3) In a report, if SQL string based on saved querie, do not sort in
saved querie but only in SQL string of report.
Infact avoid reports based on saved queries, if you can.
4) When grouping or filtering in cascaded queries, try to add
non-filter/group essential data in the last querie.
5) Try to join only on numerical ID's
6) Try to avoid code in queries (like IIF or a VBA function), and if you
must work in a cascaded query. Use query one to filter the result, use
query 2 to add the code (ex. IIF, lcase or VBA functions).
Using VBA functions slows down queries enormously. IFF is realy slow...

7) Put indexes on al fields you group, filter or join on (if used often
enough)
8) In update queries, don't do all records, put a filter on it.
For example if you have a boolean field "Archived" and you want to set
all records to "True" add where clause "Archived=False" in the query.
This is posibly much faster (if already true records exist) because
updating is slower than filtering....

8) Queries need to be compiled to. They are automaticaly compiled the
first time they run after a edit or compact.
So after a edit or compact the first time query run is always slower
then the second time.
You can compile all non-parameter queries at forhand, I have a function
for that.
I dont know if this is important and if it makes that much difference.
But if you are timing your queries you must be aware of it. Each time
you change your query it needs compiling again. So before actualy timing
your query, I would first run the querie a couple of times.


Erwin






-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Sad Der
Sent: Thursday, February 24, 2005 8:12 AM
To: Acces User Group
Subject: [AccessD] Query tuning in Access?

Hi group,

I've got a large number of queries that I need to tune. 

Does anybody have any tips on tuning in Access?

Thnx

Sander


	
		
__________________________________
Do you Yahoo!? 
Yahoo! Mail - You care about security. So do we. 
http://promotions.yahoo.com/new_mail
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list