Subqueries (nested queries) and performance

[J]

Hello All:

In creating some graphs for the users in a FE/BE database, I end up
needing about 10 queries in series (with some in parallel) to do all
the calculations. Using the aggregate functions available in access
it's the best I could do (seriously, I did the math and checked it many
many times over). A single graph can take 15-90 seconds to load with
both ends on my machine.

I need insight and advice: Does anyone have any tips for this kind of
thing? How does Access handle processing subqueries? When does the
crunching switch from back end to front end? Would doing it in VB with
recordsets have any advantages to stored querydefs? What about disk
thrashing and network traffic? Am I better off having the user copy
the entire back end (around 50MB) to their harddrive once a month or so
and running it from there?


Thanks in advance,
~J
PS. In trying to maintain good ettiquite, this is a cross post, not a
double post.

 

[strive4peace]

Hi J,

depending on what you are doing, I have found it much faster to do
make-tables (you can make a scratch db, create the tables there, and
then link to them so the working db doesn't get bloated with the
temporary data))

Also, subqueries are often slow ... in these cases, it is faster to make
a table for the criteria and link to it to limit the records...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*