[AccessD] Query tuning in Access?

Jim Dettman jimdettman at earthlink.net
Thu Feb 24 15:00:00 CST 2005


Charlotte,

<< In the latest 3 versions of
Access, saved queries are optimized while SQL strings are not,>>

 Actually no, it's the other way around.  For the last three versions, SQL
strings specified as a record source of a form or report are saved as a
query def.  Before that they were not.

  It's a same too because now there is no choice; the report or form always
runs on a pre-saved plan, which in some rare cases, is not a great idea.

Jim.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Thursday, February 24, 2005 11:18 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Query tuning in Access?


>>Infact avoid reports based on saved queries, if you can.

I'm afraid you're out of date, Erwin.  In the latest 3 versions of
Access, saved queries are optimized while SQL strings are not, which
means a report will run faster (or at least, load faster) using a saved
query.

Charlotte Foust


-----Original Message-----
From: Erwin Craps - IT Helps [mailto:Erwin.Craps at ithelps.be]
Sent: Thursday, February 24, 2005 12:16 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Query tuning in Access?



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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
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