Erwin Craps - IT Helps
Erwin.Craps at ithelps.be
Thu Feb 24 08:41:13 CST 2005
Depends on the situation. When using the data in a form, you could prcoess that at the form level. In a non-continious form it would only executed once each time you change from record. In case of a continious form you could use a combo/listbox. This would only executed x times x records on you form. Difference is that if you have 10000 records in your table, if you do it in the query it gets executed 10000 times, in the form only as much as you change records. Sometimes you need to use IIF or choose, of u also use filter criteria you should think about creating two queries. 1 to filter and a second one (based on the first one) tu use IIF in. Depending on your criteria your IIF get only execute 20 times instead of 10000 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 10:07 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Query tuning in Access? Erwin, thnx for you're response. it is a very detailled list! One question: I know IFF() is very slow. But what can I do to replace them? Thnx. Sander --- Erwin Craps - IT Helps <Erwin.Craps at ithelps.be> wrote: > > 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 > __________________________________ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com