Very Slow Database

  • Thread starter Thread starter Todd Patterson
  • Start date Start date
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:
(e-mail address removed)
 
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

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Hi Todd,

Are you sure your hard drive is only 1200 rpm? That seems awfully slow!

Here is a listing of some of the causes of database bloat:
http://tinyurl.com/2dmpw

Also, see this KB article if you are using Access 2002. I'm not sure if Access 2003 suffers from
the same problem:

Access 2002 Format Database Bloat Is Not Stopped by Compacting
http://support.microsoft.com/?id=810415


Tom
_________________________________________


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

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

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:
(e-mail address removed)
 
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
 
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!
 
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.
 
If you are storing images, or any other type of BLOB's (binary large object files stored in OLE
fields) such as Word documents, PDF files, etc., then don't. It is much better to store the path
to these types of files in text field(s) rather than store the object in OLE fields. If you are
storing tons of text in memo fields, consider moving these memo fields to a separate database and
linking them.

I'm not sure that I agree with the suggestion to enable compact on exit in a networked DB. I
always copy the back-end to my local hard drive first, before compacting, and then I copy the
compacted file back to the shared folder. This requires that I kick other users off, if they
happen to be accessing the database at the time, but I'm usually doing this task late enough in
the evening that this is not an issue. Making a copy first ensures that I have the latest backup
before I attempt to compact the DB.

Tom
_________________________________________


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

_________________________________________


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:
(e-mail address removed)
 
Sorry, I misread Tony's advice regarding compact on exit. I didn't see the word "not" in your
original statement.

Tom
________________________________________


If you are storing images, or any other type of BLOB's (binary large object files stored in OLE
fields) such as Word documents, PDF files, etc., then don't. It is much better to store the path
to these types of files in text field(s) rather than store the object in OLE fields. If you are
storing tons of text in memo fields, consider moving these memo fields to a separate database and
linking them.

I'm not sure that I agree with the suggestion to enable compact on exit in a networked DB. I
always copy the back-end to my local hard drive first, before compacting, and then I copy the
compacted file back to the shared folder. This requires that I kick other users off, if they
happen to be accessing the database at the time, but I'm usually doing this task late enough in
the evening that this is not an issue. Making a copy first ensures that I have the latest backup
before I attempt to compact the DB.

Tom
_________________________________________


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

_________________________________________


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:
(e-mail address removed)
 
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
 
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)
 
Back
Top