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