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?

I can forward the mdb file to you if required. It is < 4MB (zipped).

---

Request for Question Clarification by answerguru-ga on 11 Mar 2006 23:05 PST

Hello,

Compacting a database has adverse affects on performance and is meant
strictly for databases that are no longer in regular use. What you are
essentially doing is forcing the database to decompress, run the
query, and then recompress.

Is there a reason you need a compacted database that is so sensitive
to the performance lag?

answerguru-ga

---

Clarification of Question by milewskp-ga on 12 Mar 2006 07:49 PST

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. :(

---

Clarification of Question by milewskp-ga on 12 Mar 2006 08:04 PST

answerguru,
I am talking about a Microsoft Access database (mdb file). Regular
compacting of these databases is recommended by Microsoft (for
example, see: http://support.microsoft.com/kb/92681/en-us).