Queries Built on Top of Other Queries

[Matt]

What is the general rule on these ... should they be avoided or is the
performance the same as writing one more complex query.

For example I have an audit/error table. I have a few reports based
on this query. I first create a query base to only gather records in
the date range. I then have 5 different queries built on top of this
original query base. These 5 queries all filter the data
differently. I did not create this structure, but rather inherited
it.

That being said, I wanted to get the groups thoughts or experiences
with these types of queries. Is it better to have one complex query
do all of the filtering, or is having queries built on top of queries.

Does this rule change when introducing sub queries to the mix (is it
better to have more queries, or less queries with sub queries built
in).

Thanks for any advice!!

 

[Jeff Boyce]

Matt

My experience only...

If a more complex query I build has poor performance, I'll see if "chaining"
queries together can improve overall performance. Typically, it does.

That said, the first place I'll look to improve query performance is in
making sure the fields comprising joins, selections and sorts are properly
indexed in the underlying tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Aaron Kempf]

Matt

I dis-agree

Jeff-- and the rest of us around here-- know that stacking queries on top of
queries makes them randomly crap out

That is why he doesn't reccomend queries on top of queries.

This symptom is but one example of why I use Access Data Projects-- ADP
doesn't have this sort of flakiness

SERIOUSLY

query on top of query in MDB land sucks -- ADP is infinitely more reliable

 

[Jeff Boyce]

Matt

Perhaps I didn't express myself well...

I DO build queries on queries, if that improves performance overall. Unlike
Aaron's experience, I have not had this approach "crap out".

But that's only two data points ... collect a few more before you decide for
yourself.

And I'll still suggest looking at the tables' indexing first.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Toews [MVP]]

I DO build queries on queries, if that improves performance overall. Unlike
Aaron's experience, I have not had this approach "crap out".

I've never had a problem with stacked queries either. And I have some pretty ugly
ones.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm