How to benchmark Access queries?

[E Harris]

Hello all query experts,

I have a ColdFusion-based website that queries scientific data in an
Access database. The amount of data has grown in the past few months,
and the queries are running more slowly. For the end-user, this means
web pages take longer to load.

Some of aspects of the queries are admittedly convoluted. There are
queries feeding into other queries, multiple table joins, etc. We
want to optimize the queries, and this obviously means being able to
tell where the bottlenecks are.

My question is, is there any way to do this in Access? For example,
in Access, if a run a query that calls two queries, and each of those
two queries calls another query.... I just wait for the datasheet view
of the query to display. I'll have the Windows Date/Time Properties
open, and just count seconds until I see the datasheet view display
behind it. Obviously, all this gives me is a net time for the whole
parent query to complete. In Access, there is any way to benchmark
the components of a string of queries like this? I know I could run
each query involved in the series of queries and time those, and
assume their cumulative time would equal the time it takes for the
parent query to complete, but that seems a bit dodgy to me. Just as
Access has tools for analyzing and making suggestions for optimizing
tables and relationships, does it have anything similar for queries?

Poking around Access 2002, it does not seem like it does. So my next
question is what do you experts recommend for doing what I want to do?
Are there any rules of thumb you can shed some light on for me? Like
maybe a parent query should call no more that X number of child
queries? When creating a query, is there some sort of algorithm I can
follow that will ensure that the query is being contructed optimally?
Or is there any kind of program I can use to benchmark my queries and
tell me where the bottleneck is?

And for those of you that are well-versed in using a server-side
scripting language like ColdFusion with an Access back-end-- is it
preferable to let Access handle all of the query duties? For example,
should I create a query in Access that is customized for a particular
ColdFusion script, returning only what I know that script needs -- or
-- create several "multi-purpose" queries in Access that various
ColdFusion scripts may call, with any one particular script containing
the SQL that customizes it for the particular need at hand? Which
approach usually is faster?

Any feedback anyone can give me on this overall process-- tweaking
queries for speed-- is greatly appreciated.

Thanks a lot.

 

[Jeff Boyce]

Some thoughts...

Access is a file-server application ... can you migrate your back-end/data
to a more robust engine (e.g. SQL Server)?

Are the tables sufficiently indexed? I.e., have you indexed every field
that's used for criteria and/or join?

Given the myriad of ways folks can design data structures (and queries), I'm
not familiar with benchmarks, per se. I have found situations, however,
when I can get faster results by "chaining" a series of queries, one after
the other, rather than trying to do it all at once in a more complex query.
That works in Access, but may not in Cold Fusion, unless you have a way to
build/reference intermediate recordsets.

 

[E Harris]

Some thoughts...

Access is a file-server application ... can you migrate your back-end/data
to a more robust engine (e.g. SQL Server)?

Are the tables sufficiently indexed? I.e., have you indexed every field
that's used for criteria and/or join?

Jeff,

Thanks for being the lone responder. I had thought thought surely
this would get more comments, being a forum specifically for Access
queries.

But I'll take the advice of making sure all the fields involved in
joins are indexed.

Given the myriad of ways folks can design data structures (and queries), I'm
not familiar with benchmarks, per se. I have found situations, however,
when I can get faster results by "chaining" a series of queries, one after
the other, rather than trying to do it all at once in a more complex query.

Actually, I have a chain of 3 queries right now that is not performing
as well as a chain of 2 and a table join (both obviously retrieving
the same data). In the chain of 3, the first query filters all data
according to one set of parameters. In the chain of 2, this filtering
is done by means of a table join in the final query.

That works in Access, but may not in Cold Fusion, unless you have a way to
build/reference intermediate recordsets.

You can, and that's what I was wondering-- is it better to offload
query-work to Access or let the ColdFusion scripts have their own SQL
to do it, hitting only Access tables (as opposed to Access queries)?

Thanks again, though, Jeff. It apparently is a bit of an artform and
I will just have to try some experimentation to see what exactly will
yield faster results.

 

[Jeff Boyce]

Afraid so - artwork and experimentation is definitely called for.

But maybe one of the other 'group readers can point to some benchmarks...