Queries Taking too Long......

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have 10 users using access XP 2002, all are connecting to an access
database with about 300,000 records (150meg DB). and currently it is taking
about 6-8 minutes per lookup on the database which resides on a windows 2003
server with 2 gigs of Ram. Is this normal or is there something I can do to
speed the lookups? My systems are running windows XP SP2 with at least 512
megs ram with most close to a gig a ram locally.
 
The RAM on the server doesn't matter that much since the processing is done
on the work-station. The server in this case is simply a file server.

I think the main problem is generally transfering the data from the (file)
server to the work station. Perhaps, you may want to check the section
"Tips to improve query performance" from the following article:

http://support.microsoft.com/?id=209126
 
I can do a lookup in a 2GB db with 1,000,000 records
almost instantaneously. There is an index. (The same
database takes minutes to add a single record).

Does it take 6-8 minutes to do a lookup if the database
is local?

Have you been through all of the standard suggestions at
Microsoft Access Performance FAQ
http://www.granite.ab.ca/access/performancefaq.htm

(david)
 
David asked a very good question: How does it run locally?

If good, you have a network problem or a problem with how you've set up
Access on the network. One fix is often splitting the database into Front End
(FE) and Back End (BE) parts. The BE holds the tables and the FE contains
everything else like forms and reports. Each user gets a copy of the FE on
their computer. That way only the data, and not the forms and report, need to
cross the network.

You can also limit the records sent over the network. For example opening up
a form that shows all 300,000 records could take a while. Opening up a form
that only shows the last year's worth of record, for example, could speed
things up. Then you would have buttons on the form to retrieve older data if
needed.

Now if ti runs slowly when the database is on your local machine, you need
to do some tuning at the minimum. If a field has criteria (AKA Where Clause)
in a query, that field should be part of an index. Tables should all have
primary keys and be linked together in the Relationships Window preferably
with Referiential Integrity enforced. If this can not be done for most of
your tables, there's a very good chance that the database design is the
culprit.

One other question that comes to mind: How are you doing the lookups?
Straight SQL? Some strange code? Filter by Form or Filter by Selection?
Subforms? Some of these techiques my not be suitable for your application.
 
Back
Top