Practical Maximum .MDB Size

[DaveK]

Hi all,

I have read up on the maximum size limits of an Access database, but what do
you feel is the practical maximum size of an .mdb file stored on a server?
One of my users is complaining about slow load/save times for .MDB files that
are between 1.5 and 2GB in size. I have done some simple file copy tests on
a 2 GB .MDB file and get 4-5 minutes to copy such a file between servers, and
35 minutes to copy that same file to my PC. Does Access do saves in chunks,
or must it copy the whole thing to local storage, manipulate it, then copy
the whole thing back to the server when saving? At what point do you start
looking at SQL server to overcome the limitations of LAN file load speeds?
BTW, we have 100MB LAN connections, but the users are on laptops with Win2K
and 512-1GB of RAM. I am proposing they go to an Access front end with SQL
Server backend. I have seen that work quite well.

 

[Jerry Whittle]

Size isn't the issue. It's how well the database is designed. If you had only
one table and ran a query that returned all records and fields, Access would
have to return the entire table.

However if you have a properly normalized database with efficient SQL
statements, only those records required for the form or report would be
returned.

Now for the flip side. Access is not client/server. For example with SQL
Server or Oracle, the database is running on a server. If you run a query the
work is done on the server and only the needed records are returned. With
Access the server only holds the data which needs to be sent to the user's PC
to be processed there.

I'd look at the existing database and see if it is properly design with good
normalization, indexing, etc. If not, fix it. If it is optimized properly and
still the performance is unexceptable, then your idea of a SQL Server backend
has merit.

I also recommend checking out Tony Toews web site on performance:
http://www.granite.ab.ca/access/performancefaq.htm

Sometimes it's something simple like an overactive virus scanner causing the
problems.

 

[DaveK]

I still a bit foggy about the basic size issue. Would Access not have to
copy the entire 1.5GB from the server to the PC before it could do anything?
I tried just a copy of the .mdb to my PC and it took 35 minutes.

I agree about the design issues. I have to see if they have done
compressions, indexing correctly, etc.

Thanks for the link, it was a good resource.

 

[Roger Carlson]

I'm confused. Are you saying the users copy the entire database to their
machines before they use it? If so, you might want to look into
Replication, which will only transfer the *changes* to the data, not all of
the data.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

On the other hand, if they are all being used remotely (as I am assuming
they are) with no network connection, how would SQL Server help?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[DaveK]

Some do because they say they get faster response than having the .mdb reside
only on the file server.

Can Access do anything without dragging the whole .mdb to their machine
before giving them the chance to do anything? This is not a front end/back
end design. Everything is in one big .mdb file (1.5 to 2 GB in size)

 

[Roger Carlson]

Well, it's really hard to tell. There are a lot of variables beyond size.
Jerry has talked about some of them.

But it's more than just the database design that can be a performance drain.
The design of the application is important too. Many people are surprised
when simply upsizing to SQL Server doesn't improve perfomance any. Their
application design is usually the problem.

For instance. Suppose you have a table with several million records.
Suppose further that you create a form bound to that table and simply filter
the records to those you want to see. This scenario will make even a bound
SQL Server table crawl. A better design is to leave the ControlSource of a
form blank and programmatically fill it at runtime with a smaller dataset.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImproveFormPerformance2k.mdb" which illustrates that sort of
thing. It's just one of many ways to improve the performance of your
application.

If you do upsize, you might want to get a copy of "Microsoft Access
Developer's Guide to SQL Server" by Mary Chipman and Andy Baron. It's got a
lot of tips for using Access as a front-end for SQL Server.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[DaveK]

These are all local connections, no remote or vpn in the way.

If I take your comments correctly, even though the file size is bumping up
against the 2GB limit for Access .mdb files, we should not necessarily see
any huge performance hit based on size alone?

I have obtained a copy of one of the .mdb's in question and will take a look
at it.

Thanks everyone for your comments.

 

[Jerry Whittle]

Access does NOT copy the entire contents of the database over to the PC. For
example if you do a query and ask for all the addresses in Iowa from a table,
that's what's returned. Now Access does need to look through the .mdb file
for these records, but it can do that while the file is on the network drive.

 

[Roger Carlson]

No, size DOES make a difference. But it is not the only issue. If you are
bumping the 2GB file size, you do need to start looking at SQL Server. But
as I said, that may not solve all of your problems.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

['69 Camaro]

Hi, Dave.

If it takes 35 minutes to copy a 2 GB file across the network, then there's
something seriously wrong with either the network or the computer at each
end. Check to ensure the network is using 100 Mb auto-negotiate, and that
your hard drive isn't seriously fragmented or low on disk space, and that
your Temp directory isn't near its maximum quota.

This is not a front end/back
end design. Everything is in one big .mdb file (1.5 to 2 GB in size)

No wonder they're complaining it's slow! It should be split. The Microsoft
Access development team has identified sharing a database across the network
as the number one cause of database corruption. For more information on why
you should always split your multiuser database, please see the tip, "Split
the Database," on the following Web page:

http://www.Access.QBuilt.com/html/gem_tips1.html

Ensure that there are indexes on appropriate columns for queries (i.e.,
columns used in joins and query criteria), and that wildcards are used as
little as possible in query criteria. For other recommended solutions to the
performance problems, please see the following Web page for a link to Access
MVP Tom Wickerath's article, "Implementing a Successful Multiuser Access/JET
Application":

http://www.Access.QBuilt.com/html/articles.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.