Query efficiency

[Guest]

At what point are query parameters applied when nesting queries?

Due to the need to group/sum at multiple levels of data extraction, I often
create Query1, then use that as the source for Query2, which in turn is used
as the record source of a report.

The record source of the report must be filtered down to some subset of
records, however. Query1 has no criteria, so if it is run alone, it will pull
every record from the table. The same is true of Query2.

I am currently inserting the criteria into the RecordSource of the report.
The criteria is a reference to the contents of a combo box on the form from
which the user opens the report: [Forms]![myForm]![combo1]. This is where the
user chose the criteria.

Would it be it more efficient to enter the criteria into Query1, or does it
not matter? The advantage of inserting the criteria into the report is that I
can use my Query1 in other places in my application where the criteria may
come from a different place, so Query1 does not have to have a hard-coded
reference to the form involved.

 

[Jeff Boyce]

Brian

Is your question one of practical significance (e.g., one way takes 10
seconds, the other way takes 2), or of theoretical interest (e.g., both ways
take less than 1 second)?

I'll typically add in parameters as early in a "chain" as possible, to limit
the number of rows Access needs to work with.

But I will also add in parameters at the "end" of a chain of queries, if the
final result is based on a generic (filtered) starting point, but requires a
final filtering for a specific use.

In the example you cited, couldn't you use the criteria for a specific
report in the final query that is used to feed the report?

Or have I misunderstood your situation?

 

[Guest]

You do understand perfectly, and I am, as you suggest, using the criteria at
the end so that the base queries can be used by more than one report or
process.

My question is a practical one - some of my reports open quite slowly due to
the necessary complexity of the queries, and I am just wondering if there any
significant difference in how fast it will run if I embed the criteria in the
base query as compared to the report's record source that calls those queries.

It's all about keeping the client happy, so a difference between 1 and 2
seconds does not matter, but I have a couple of reports that take well over a
minute on this particular client's aging Windows 2000 terminal server.

I can tell them all day that it runs faster on my laptop, but the complexity
of their requriements does not leave me a lot of choices in how the queries
are built, and (in their mind, at least) the delays in opening the reports
always seem to reflect upon my programming skills and not on the vintage of
their equipment. Therefore, I want to take advantage of any significant
increase efficiency I can find, and making the queries less generic might be
a worthwhile tradeoff in this instance.

Brian

Is your question one of practical significance (e.g., one way takes 10
seconds, the other way takes 2), or of theoretical interest (e.g., both ways
take less than 1 second)?

I'll typically add in parameters as early in a "chain" as possible, to limit
the number of rows Access needs to work with.

But I will also add in parameters at the "end" of a chain of queries, if the
final result is based on a generic (filtered) starting point, but requires a
final filtering for a specific use.

In the example you cited, couldn't you use the criteria for a specific
report in the final query that is used to feed the report?

Or have I misunderstood your situation?

--
Regards

Jeff Boyce
<Office/Access MVP>

At what point are query parameters applied when nesting queries?

Due to the need to group/sum at multiple levels of data extraction, I often
create Query1, then use that as the source for Query2, which in turn is used
as the record source of a report.

The record source of the report must be filtered down to some subset of
records, however. Query1 has no criteria, so if it is run alone, it will pull
every record from the table. The same is true of Query2.

I am currently inserting the criteria into the RecordSource of the report.
The criteria is a reference to the contents of a combo box on the form from
which the user opens the report: [Forms]![myForm]![combo1]. This is where the
user chose the criteria.

Would it be it more efficient to enter the criteria into Query1, or does it
not matter? The advantage of inserting the criteria into the report is that I
can use my Query1 in other places in my application where the criteria may
come from a different place, so Query1 does not have to have a hard-coded
reference to the form involved.

 

[Jeff Boyce]

Brian

I'm not aware of (which means nothing!) any hard/fast rule about what works
faster. I'm sure someone somewhere can tell you where the electrons are
going...

On a practical note, is there a reason you couldn't create two versions, one
using the "chain" and one customized for the single (long-running) report,
and test them against each other for performance?

On a related note, are you satisfied that your underlying queries are making
full use of table indexes? That is, is every one of your join fields and
your selection criterion fields indexed? This can make a considerable
difference in query performance.

Are your queries (or your report) using any db functions (e.g., DCount())?
These can also adversely affect performance.

--
Regards

Jeff Boyce
<Office/Access MVP>

You do understand perfectly, and I am, as you suggest, using the criteria at
the end so that the base queries can be used by more than one report or
process.

My question is a practical one - some of my reports open quite slowly due to
the necessary complexity of the queries, and I am just wondering if there any
significant difference in how fast it will run if I embed the criteria in the
base query as compared to the report's record source that calls those queries.

It's all about keeping the client happy, so a difference between 1 and 2
seconds does not matter, but I have a couple of reports that take well over a
minute on this particular client's aging Windows 2000 terminal server.

I can tell them all day that it runs faster on my laptop, but the complexity
of their requriements does not leave me a lot of choices in how the queries
are built, and (in their mind, at least) the delays in opening the reports
always seem to reflect upon my programming skills and not on the vintage of
their equipment. Therefore, I want to take advantage of any significant
increase efficiency I can find, and making the queries less generic might be
a worthwhile tradeoff in this instance.

Brian

Is your question one of practical significance (e.g., one way takes 10
seconds, the other way takes 2), or of theoretical interest (e.g., both ways
take less than 1 second)?

I'll typically add in parameters as early in a "chain" as possible, to limit
the number of rows Access needs to work with.

But I will also add in parameters at the "end" of a chain of queries, if the
final result is based on a generic (filtered) starting point, but requires a
final filtering for a specific use.

In the example you cited, couldn't you use the criteria for a specific
report in the final query that is used to feed the report?

Or have I misunderstood your situation?

--
Regards

Jeff Boyce
<Office/Access MVP>

At what point are query parameters applied when nesting queries?

Due to the need to group/sum at multiple levels of data extraction, I often
create Query1, then use that as the source for Query2, which in turn

is
used

as the record source of a report.

The record source of the report must be filtered down to some subset of
records, however. Query1 has no criteria, so if it is run alone, it

will
pull

every record from the table. The same is true of Query2.

I am currently inserting the criteria into the RecordSource of the report.
The criteria is a reference to the contents of a combo box on the form from
which the user opens the report: [Forms]![myForm]![combo1]. This is

where
the

user chose the criteria.

Would it be it more efficient to enter the criteria into Query1, or

does
it

not matter? The advantage of inserting the criteria into the report is that I
can use my Query1 in other places in my application where the criteria may
come from a different place, so Query1 does not have to have a hard-coded
reference to the form involved.

 

[Guest]

Thanks, Jeff.

Yes, I will try benchmarking both ways. I just thought there might be some
known efficiency depending on whether the filter (criteria) is applied at the
top level or at the bottom level; i.e. does one way actually allow Access to
apply the filter sooner in the runtime process and thus search through fewer
records.

I have been working through the process of removing domain aggregate
functions and have most of them replaced - that is one of the reasons for the
stacked-up queries.

Thanks for the reminder on indexes - I will have to review that again to see
if I miseed any that are used in these reports.

Brian

I'm not aware of (which means nothing!) any hard/fast rule about what works
faster. I'm sure someone somewhere can tell you where the electrons are
going...

On a practical note, is there a reason you couldn't create two versions, one
using the "chain" and one customized for the single (long-running) report,
and test them against each other for performance?

On a related note, are you satisfied that your underlying queries are making
full use of table indexes? That is, is every one of your join fields and
your selection criterion fields indexed? This can make a considerable
difference in query performance.

Are your queries (or your report) using any db functions (e.g., DCount())?
These can also adversely affect performance.

--
Regards

Jeff Boyce
<Office/Access MVP>

You do understand perfectly, and I am, as you suggest, using the criteria at
the end so that the base queries can be used by more than one report or
process.

My question is a practical one - some of my reports open quite slowly due to
the necessary complexity of the queries, and I am just wondering if there any
significant difference in how fast it will run if I embed the criteria in the
base query as compared to the report's record source that calls those queries.

It's all about keeping the client happy, so a difference between 1 and 2
seconds does not matter, but I have a couple of reports that take well over a
minute on this particular client's aging Windows 2000 terminal server.

I can tell them all day that it runs faster on my laptop, but the complexity
of their requriements does not leave me a lot of choices in how the queries
are built, and (in their mind, at least) the delays in opening the reports
always seem to reflect upon my programming skills and not on the vintage of
their equipment. Therefore, I want to take advantage of any significant
increase efficiency I can find, and making the queries less generic might be
a worthwhile tradeoff in this instance.

Brian

Is your question one of practical significance (e.g., one way takes 10
seconds, the other way takes 2), or of theoretical interest (e.g., both ways
take less than 1 second)?

I'll typically add in parameters as early in a "chain" as possible, to limit
the number of rows Access needs to work with.

But I will also add in parameters at the "end" of a chain of queries, if the
final result is based on a generic (filtered) starting point, but requires a
final filtering for a specific use.

In the example you cited, couldn't you use the criteria for a specific
report in the final query that is used to feed the report?

Or have I misunderstood your situation?

--
Regards

Jeff Boyce
<Office/Access MVP>


At what point are query parameters applied when nesting queries?

Due to the need to group/sum at multiple levels of data extraction, I
often
create Query1, then use that as the source for Query2, which in turn is
used
as the record source of a report.

The record source of the report must be filtered down to some subset of
records, however. Query1 has no criteria, so if it is run alone, it will
pull
every record from the table. The same is true of Query2.

I am currently inserting the criteria into the RecordSource of the report.
The criteria is a reference to the contents of a combo box on the form
from
which the user opens the report: [Forms]![myForm]![combo1]. This is where
the
user chose the criteria.

Would it be it more efficient to enter the criteria into Query1, or does
it
not matter? The advantage of inserting the criteria into the report is
that I
can use my Query1 in other places in my application where the criteria may
come from a different place, so Query1 does not have to have a hard-coded
reference to the form involved.