Re: Query Optimization
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Mon, 20 Mar 2006 09:05:42 +0800
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/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_MicrosoftOfficeDeveloperForumMicrosoftAccessMicrosoftJetDatabaseEngine.asp
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Milewskp" <Milewskp@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8E9D5F57-9A36-4A52-803A-7BEC13B87003@xxxxxxxxxxxxxxxx
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?
.
- Prev by Date: Update query on more than one table
- Next by Date: Re: Query Problem
- Previous by thread: Update query on more than one table
- Next by thread: Re: I' unable to uninstall a previous download of Office2003Trial.
- Index(es):
Relevant Pages
|
