Query Optimization

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two update queries in a Microsoft Access database with the same
SQL, but one runs in <0.003 sec and the other runs in >2.000 sec. The
second one is slower because it has a query plan that is not optimal
for the data currently in the database.

After I compact the database, I would expect the queries to be
recompiled so that they both run fast. In fact, the opposite is true –
both take >2.000 sec to run.

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 the two queries: qryFast and qrySlow.
Initially, qryFast is fast and qrySlow is slow, but after you compact
the database, they will both be slow.

My Question:
How can I redesign the query so that it runs in < 0.003sec after compaction?
 
Have not downloaded your sample, but the difference could be due to factors
such as Name AutoCorrect, subdatasheets, and so on.

Take a look at the Access Performance FAQ by Tony Toews, and work through
the issues he lists here:
http://www.granite.ab.ca/access/performancefaq.htm
It will be worth your while for the long term, and certainly could impact
your immediate issue.

If you want to work with the query plan in JET, these links should get you
started:
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
 
Back
Top