Erwin Craps - IT Helps
Erwin.Craps at ithelps.be
Thu Feb 24 15:56:15 CST 2005
I supose that is true... It not the first time I catch myself still using workarounds while this is no longer needed due to access improvements.... -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Thursday, February 24, 2005 5:18 PM 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