Re: Query Design View is Slow to Open

On Oct 23, 7:15 pm, existential.philoso...@xxxxxxxxx wrote:
This is a new problem for me: I have some queries that open very
slowly in design view.

My benchmark query takes about 20 minutes to open in design view. That
same query takes about 20 minutes to open in data*** view. As an
experiment, I deleted all rows in all tables; after that, the query
took only seconds to open in both design view and data*** view. From
these facts, I conclude that Access is evaluating the query when I go
to design view. Why would it do that?

The problem occurs each time I do a Compact and Repair. After I
compact the database, the first time I open my benchmark query in
design view takes 20 minutes. Each subsequent time, the query opens
instantly, until the next time I compact.

In the same database, there are many queries that open normally in
design view. I can't find any obvious differences between the slow
ones and the normal ones, except that the slow ones are generally
downstream of the normal ones.

This issue is killing my productivity. It can easily take hours to
make a trivial design change. Can anyone offer insight into what is
going on?

Thanks
-TC

Here are some additional notes:
- This is not a multi-user problem. It occurs even when I have the
database opened in exclusive mode.
- While I'm waiting for the query to open in design view, I can't
interrupt Access.
- The database is local (i.e. on my C drive).
- The troublesome queries are based entirely on local tables (i.e. no
linked tables).
- I cleaned the database by creating a new mdb file and importing all
objects into it.
- Subdata*** Name is set to [None] for all tables.
- Name AutoCorrect is turned off.
- There are no crosstab queries or union queries anywhere in the
database.
- The problem doesn't seem to be related to the anti-virus software;
it is just as slow when anti-virus is disabled.
- My version of Jet is 4.0.9511.0, which supposedly does not suffer
from the bug described at <http://support.microsoft.com/?kbid=302496>.
- I'm running Access 2003 SP3 and Windows XP with a 2GHz dual-core
processor and 2GB of RAM.
- The computer doesn't have any resource-hogging processes slowing
down Access.
- While the query is opening, the Task Manager shows that Access is
using about 50% of the CPU cycles and progressively more memory, to
about 80MB.
- I've seen the same behavior on three different computers, each
configured very differently, so I doubt the problem is related to some
esoteric configuration thing.
- I've been doing research online, and I've followed-up on all the
performance tips I could find, including those at <http://www.granite.ab.ca/access/performancefaq.htm>.

Try the following experiment. Turn off compacting the database on
close (or just don't do it manually) then close Access. Now open the
database again. If the problem recurrs close Access and open it a 3rd
time.
What we're looking for here is any evidence that the compact & repair
is anything to do with the problem as I don't believe it is. If I'm
right the slow query will continue to happen every time you run it
fresh from loading the DB.

What I think might be happening is that the query is poorly written -
either too much nesting or something similar. Once the tables are in
memory everything will happen much faster. 50% CPU time would be
appropriate in this case as would the enormous amount of memory being
used.

I suggest you post the troublesome query(ies) and some bright spark
will perhaps be able to spot something.
.