We help IT Professionals succeed at work.

Optimize My MS Access Query

Milewskp
Milewskp asked
on
455 Views
Last Modified: 2011-09-20
I have an update query based on three tables. It runs in <0.020 sec. However, after I compact the database, this same query takes >2.000 sec to run. How can I redesign the query so that it runs in < 0.020sec after compaction?
If you think you can answer this question, please send me your email address so I can email you the database (<4MB, zipped).
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
Hi Milewskp,
> If you think you can answer this question, please send me your email address so I
> can email you the database (<4MB, zipped).

Please be aware that using email to resolve questions is a violation of site rules.

Regards,

Patrick

Author

Commented:
Thanks Pat, I didn't realize that (I'm a newbie). Any suggestions on how I can post the file?
CERTIFIED EXPERT
Top Expert 2010

Commented:
Whenever I need to post a sample, I upload it to the free site I got at www.geocities.com.

You could do the same, then post the URL to the file here.

Patrick
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Does the query continue to take a longer time to execute after you've compacted the database?
Or does it find it's second wind again? ;-)

The act of compacting an mdb removed the saved execution plan of a stored query - and it will have to fully recalculate it again the next time.
That's unavoidable.
CERTIFIED EXPERT

Commented:
Most likely, that behavior is due to one or more missing indexes in the tables(s).  Goto tools, analyze, performance.  Select the query and follow the recommendations.  It is a good idea to run the performance analyzer on the entire database.

Author

Commented:
Hi LPurvis,
The query continues to take longer to execute after I've compacted the database (it doesn't find its second wind).

Hi Nelson,
I tried running the Analyzer - no recommendations. Note that I don't change indexes or anytime else - I just compact the database.

Author

Commented:
I have posted my database on:
http://www.geocities.com/milewskp/ee/   (then click on  FastSlowTX.zip )

When you extract and then open the mdb file, you will see two queries: qryFast and qrySlow. They have the same SQL, but one runs fast and the other runs slow. (The database also includes two macos that run these queries and show the run time in the status bar - try them.)
When you compact the database, both queries will run slow. :(
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Both queries execute at the same speed for me.
And at several hundred thousand records you're pretty much going to have an update lag.

I've tried removing the excess indexes from your tables (those not involved in joins) - and that made no difference to the execution time.
(Just knocked a meg and a half off the file size).

I could only postulate wildly as to exactly why your formerly fast query ran more quickly.
Perhaps the new execution plan that Jet has calculated is simply less efficient for the number of records.
Sometimes you can compact a database and the new size is actually bigger!  (Possibly due to indexes).

Eventually it takes longer to update more rows.
Not exactly great news for you perhaps...

Author

Commented:
Hi LPurvis,
Are you sure that the database is not being compacted before you run the macros the first time?
When you run the queries using the macros RUN_qryFast and RUN_qrySlow, what does it say in the status bar? (I always get less than 0.020 sec and  greater than 2.000 sec, respectively.)
What version and SP of Access are you using? (I am using Access 2002 SP3.)

The only other thing I can think of is that your Option settings are different than mine. Screen shots of my Options panels are available at http://www.geocities.com/milewskp/ee   (then click on AccessOptions.doc). Please have a look and let me know the differences from yours.
Just so that we have more than one vision: I downloaded your database and have the same results as you have (less than less than 0.020 sec and  greater than 2.000 sec). I must admit I have no idea why. I have played with copies of the queries, etc, etc. but everytime the result is like 'RUN_qrySlow'.....

Kalim
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
I altered the queries too.
Made changes that should have resulted in slight improvement in efficiency.

I didn't compact the mdb first though - will try over again if I get the chance.
See if there's anything.
Have you tried running a separate query to set the value to a default value - so that you can also be sure that each query is actually executing successfully?

Author

Commented:
Hi LPurvis,
Yes, both queries do what they are supposed to (just at different speeds). If you're suspicion that the Fast query can execute in <0.020 sec when there are over 200,000 records in one of the tables it is based on, it is because one of the other tables the query is based on has only one record, and that's how many records are in the query's recordset.

Author

Commented:
There's been no activity for almost a month, so I'll ask the moderator to close this. Although my question my not answered, I will give part marks to matthewspatrick  for pointing out the free web sites at  www.geocities.com., which I've found to be useful. Thanks Matthew.  

In desperation, I actually paid Micorsoft $100 for an answer. Two very nice ladies from the Microsoft Global Technical Support Center assisted me, and I though you might be interested in what they had to say:

"Unlike SQL Server, we cannot specify any index hints or “plan guide” in the query statement to tune the query plan. The only thing that we can try in Jet to optimize the query is to create different indexes or remove indexes so Jet decides to use a different plan. Or, we have to re-write the queries or even re-design the tables. I understand this is a limited method, but it is the only method that we have with Access. Jet would try to generate a better plan, but sometimes it may not be so intelligent. Most database query engines are not so intelligent as well, but some of them may provide some methods to help tune the plan. Unfortunately Jet does not provide such interactive methods.

"Also, the term “good query plan” is not absolute because of different sample data. The “good query plan” may be good for the current amount of data, but it may not be so good for other data. For example, let’s say we have criteria “A=1”, and two different sets of sample data. One with 1000 rows, and all are with A=1; another with 1000 rows, and all are with A=0. What could be the most efficient plan for both sets of data? We may get a plan that is very fast for the first set but very slow for the second set, or we may get a plan that is average fast/slow for both sets of data. What would we prefer?"


Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Essentially - the nice ladies at MS have confirmed what we've said regarding execution plans (and been a bit more explicit about other things - which is good info for you).
It isn't great news for you I know.

I can't remember the nuances of the query in question (unsurprisingly) but if I got some time would have another peak.
(Very busy just at the mo).
Is the link still valid?

But as I recall - even try to break it out - and using various standard techniques didn't prove very fruitful for me last time.
I suppose the real mystery isn't why it's so slow now - but how the hell it was so quick beforehand.

Author

Commented:
Hi Leigh,
You find all the answers to your questions (including how I made the query so fast) on my website: http://www.geocities.com/milewskp/ee/ . Just download the file InstructionsComplex.doc and follow the instructions.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.