[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