My Problem: 

I have two update queries in a Microsoft Access database with the same
SQL, but one runs in <0.020 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.

For convenience, the database includes two macros that run these
queries and show the run times in the status bar.

My Question:

How can I redesign the query so that it runs in < 0.020sec after compaction?

Hey,

Ashan again. Ok...figured out the problem with the slow query. Now
they both time under 2 sec (around 0.4 sec).

Bye

Ashan

Hi Ashan,
I know that I can get the query to run in less than 0.4 sec by using
outer joins between the three tables. Unfortunately, that's not good
enough for my application. I'm looking for a way to redesign the query
so that it runs as fast before and after compaction, which, after
doing a more accurate speed test, I've discovered is 0.003sec.