Strange Query Behavior

[AG]

Access 97 with split FE/BE
I have a query based on 5 tables with one inner join and 3 outer joins in
FE.
Two tables have about 125,000 records each, one has about 17,000 records and
two have about 80 records.
The query has parameters derived from form textboxes and is used for a
form's recordsource.
Performance has been excellent until I tried to change the query.

With the original query, parameters that would return results in 1-2
seconds, not take 8+ seconds.

Initially, I thought that I inadvertently changed something that I did not
intend to, so I imported the original query from a backup copy of the FE. It
ran fine.

Opened the query in design view, changed nothing and did File > SaveAs ...
Performance of the new query dropped like a rock -- NOTHING WAS CHANGED!

Tried several times and got same results.

I have decompiled, compiled, repaired and compacted.

Can anyone offer an explanation/solution?

 

[Allen Browne]

When you save a query, Access saves an execution plan with it. If you alter
the query, it recalcualtes the best execution query, and saves that, even if
nothing else changed. It would seem that for this particular query, the
original execution plan (calculated on whatever the data was originally) is
better than the new execution plan (based on the current data.) That's
unusual, but not impossible.

Here's a really silly idea. Temporarily move the back end to a different
location, and restore the old back end, with data similar to what would have
been there when you originally saved the query. Do what you like with the
query, and save it. It might save the execution plan like your original one.
Then restore your current back end, and the saved query with the old plan
just might behave like it used to.

These articles might provide a starting point for understanding the
execution plan:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp
http://builder.com.com/5100-6388-5064388.html
http://msdn.microsoft.com/archive/d...MicrosoftAccessMicrosoftJetDatabaseEngine.asp

 

[AG]

Thanks Allen,

I will check out the articles.
The last time the query was modified/saved was probably almost a year ago. I
am working remotely via TS and client does daily backups, but he would not
have one that old.

 

[privatenews]

Hello,

Since it is Access 97 database, you may want to refer to the following
article to truoblehsoot the issue:

ACC: How to Optimize Queries in Microsoft Access 2.0, Microsoft Access 95,
and Microsoft Access 97
http://support.microsoft.com/default.aspx?scid=kb;[LN];112112

Also, it is suggested that you consider upgrade latest Access version so
that you might be get better support since Access 97 has ended

Office Family Product Support Lifecycle FAQ
http://support.microsoft.com/default.aspx?scid=fh;en-us;lifeOffice

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[AG]

Thanks for the reply Peter,

The article was informative. I have been working in Access for a long time
and remember when Rushmore first came out. It was supposed to be so
wonderful and fast. In fact it was so fast that many of my queries ran at
least 50% slower (yes, that is slower, not faster). I had to redesign them
just to get to 90% of pre-Rushmore!

Of course, I could be wrong, but I doubt that upgrading to a later version
of Access would do anything performance-wise. Feature-wise, there is nothing
in the newer version that this app needs.
As for support, I think these forums are the best for issues like this.

I was able to get near original performance by creating a query with the few
fixed parameters necessary, using it a a base to build a new recordsource
with the added user-entered parameters, in code and then applying that to
the form's recordsource.

 

[Wei Lu [MSFT]]

Hello,

I would like to know whether the KB article could help you on this issue.

If you have any questions or concerns, please feel free to let us know.

Sincerely,

Wei Lu

Microsoft Online Community Support