fast queries became very slow

  • Thread starter Thread starter annie
  • Start date Start date
A

annie

I have a database with many objects, probably more than 100. I have a
number of complicated queries that involve 5 or 6 tables/queries, and they
used to run quickly, i.e., within a couple of seconds. Now they are taking
upwards of a minute. This does not happen to all of the queries.

Maybe I'm answering my own question by admitting that it's possible that the
now slow queries may use a common query that could be the problem. I'll
check that out, but are there any other reasons why a set of queries would
slow down so much?

Thanks!!!
 
One reason comes to mind. The larger the number of records in the table(s),
the queries are based on, the longer it takes to process them. And another
thought, instead of nesting queries, I'de do the calculating on the reports
as much as possible in separate sections (groupings) .
 
Thanks Bill.

The number of records has not increased.

I will see if I can get the same functionality by having the reports do the
work though.
 
I had a case of corruptions that slowed some queries down
substantially but all queris still worked correctly.
Monitoring the network traffic, I found that the work-
station sent to the server (where the Back-End resides)
and received from the server only about 100 packets at a
time while the norm is about 2000 packets for a particular
query.

Compact & Repair and /Decompile didn't help. Eventually,
I created a new blank database and imported all objects
from the old database and this fixed the problem instantly.

HTH
Van T. Dinh
MVP (Access)
 
Your importing idea is intriguing.

As it happens, my queries inexplicably started executing in about one-tenth
the amount of time today. Unfortunately, I can't trace the improvement to
anything that I did or otherwise. I have been having a few "Access has
encountered an error and must close" errors recently for no apparent good
reason.
 
It seems to me that it is easy to corrupt the database while you are
designing Forms & writing code rather than when the database is actually
used. Quite often, the corruptions are so minor and hardly noticeable but
the effect of corruptions will get worse with usage. I find importing
objects to a new blank database seems to get rid of a lot of these
corruptions.

In fact, I have adopted the policy of *ALWAYS* creating a new empty
database, importing all objects from the database I used in designing &
coding, testing the new database (again) just prior to commissioning of the
database.
 
Is there an easy way to transfer lots of objects to a new blank database
aside from one at a time?

Maybe it should be the subject of a separate post, but I have lots of
trouble managing all of my objects. The tables are not a problem, but I
have lots of versions of queries, forms and reports, and it seems to me that
Access does not provide adequate means to determine the relationships
between them. For instance, I would like to know all of the objects that
make use of a particular query. I don't know how/if Access provides such a
capability. My database design practices make such a capability crucial to
having a good hold on what's going on, and I can't be the only one for which
this is the case. Yes, I could be missing something, even something
obvious, but I'd be happy to hear that that's the case!
 
You can create a new blank database and *import* the whole lot from the
existing database.

After that, check the References and Compile the code.

There are 3rd-party utilities you can. Often mentioned in these newsgroups
are:

* Search & Replace
* SpeedFerret
* FMS Total Visual Developer Suite

Search for John Vinson's or Douglas J. Steele's posts for more info on these
3rd-party tools. They seem to be able to remember a lot omre details than I
can.
 
Back
Top