Very Slow Database

T

Todd Patterson

Need some advice. I have a database that so far is about
800,000k in size and by the time that it is completed is
probably going to be about 2,000,000k. Everytime I try
to open a form, table, etc., the loading is extremely
slow. Is there any way to improve the performance and
speed?

We are using a Pentium 4 with Hyper-Threading, 1 GB of
RAM and a 1200RPM drive.

If you have any suggestions please reply to:
[email protected]
 
R

RobFMS

Do you have list boxes or combo boxes that are loading a large quantity of
data into the controls?

Have you compacted and repaired the database recently?

Have you created a new database and imported the objects into the new
database?

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
T

Tigger

Try:

Indexes/Primary Keys for main or large Tables
Upgrade any larger Macros to Modules
Filter out any Combo/List Boxes by using a Query first
If it's not a networked dB, Set up a compact on exit

I hope this helps?

Tony
 
V

Van T. Dinh

Also, are you sure the database is already 800 MB and will be around 2 GB by
the time you finish designing???

2 GB is the max size of an Access database file!
 
D

Dirk Goldgar

Todd Patterson said:
Need some advice. I have a database that so far is about
800,000k in size and by the time that it is completed is
probably going to be about 2,000,000k. Everytime I try
to open a form, table, etc., the loading is extremely
slow. Is there any way to improve the performance and
speed?

We are using a Pentium 4 with Hyper-Threading, 1 GB of
RAM and a 1200RPM drive.
Click to expand...

The database size is a matter of some concern, since you anticipate
hitting the maximum size of an Access database. Are you storing images
in this database? Access doesn't handle that very well, though you can
work around it.

One thing that is known to cause slow loading of forms is the Name
AutoCorrect option, specified on the General tab of the Tools ->
Options... dialog. Check if that is set on; if so, see if turning it
off speeds things up.
 
T

Tony Toews

Todd Patterson said:
Need some advice.
Click to expand...

You have two separate problems. You've gotten some very useful
suggestions but I'll add my own in as well.
I have a database that so far is about
800,000k in size and by the time that it is completed is
probably going to be about 2,000,000k.
Click to expand...

You don't want to embed graphics into a table as this causes
significantly bloating of the database. Frequently about one Mb per
graphic.

For more info see the Image Handling Tips page at my website.
http:\\www.granite.ab.ca\access\imagehandling.htm
Everytime I try
to open a form, table, etc., the loading is extremely
slow. Is there any way to improve the performance and
speed?
Click to expand...

The three most common performance problems in Access 2000 are:
- LDB locking which a persistent recordset connection or an always
open bound form corrects (multiple users)
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off

For more information on these, less likely causes, other tips and
links to MS KB articles visit my Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
D

david epsom dot com dot au

The Access user interface is not optimised for very large
tables. Every time you open a table, it tries to read the
entire table into memory. If you want to go faster, you
have to use forms that are designed to read just one record
at a time, where you select a record before reading it from
the table.

Also, the Access caching scheme is not optimised for very
large indexes. The cache is automatically invalidated every
few seconds. If you want to go faster, you have to use
something like SQL Server, which tries to keep indexes in
memory instead of invalidating and reloading them.

However, for a partial solution to the problem, just leave
all the tables open.

(david)
 
Top