What is a large Database

[ftook]

I have started to develop a control system for a small company and the requirements are growing every day, the database will be used by 20 users eventually (we are now looking at sever 2003 to beat the 10 user limit).

I fear that the database could grow quite large, what is large in terms of a database and can i expect to hit any problems in terms of size / performance issues.

 

[fneily]

I've created 300mb size Access databases. Of course, that had a state's data in it. What do you foresee? What you should be aware of is why does an Access database grow? Obviously, if you keep adding data, the database grows. So you might want to archive old data into another database. Creating new tables, database grows. Complicated forms, database grows. Changing queries/creating queries, database grows. Deleting tables and recreating them, database grows. So you might want to incorporate the Compact and Repair function in your daily duties.
If you're going over an INTRAnet, you should split the database Front End(FE)/Back End(BE). Do a search on the Access forums to see the reasons why.
Straight Access uses the Jet engine. This is a file server database, it moves ALL the table info across the network when called upon. You might consider the MDSE engine. It is a client/server database engine. It only moves the results of a query across the network, so less traffic.
That's a start.

 

[JoeAtWork]

The actual size of the data is not really a concern in terms of performance. I've seen databases with Gigabytes of data that run very smoothly, and I've seen systems that crash and burn before they get to 50 MB.

Of much more importance is that you design your database for scalability and performance. Follow the rules of normalization, create indexes on strategic fields, enforce data integrity, write efficient SQL.

There is one absolute limit on the size of Access MDB files - if memory serves correctly it is 1GB for Access97 and 2GB for all later versions.

With more than 10 users I would suggest you try to use SQL Server instead (although some posters on this forum will swear that Access can handle 20 or even more users).

 

[ftook]

What worried me at first is that the database grew from 50mb to 120mb in a day, i compressed and went down to 20mb, all tables are normalised and i have set all index's (i hope) correctly. I know that the compression routine reduces the size massively and i am setting a compression routine for a tri-daily run.

Thank you for your feedback, it helps me make an informed judgement before i hit any size limits.

 

[Ed2020]

Access databases do have a tendency to grow considerably after compaction. I've always found it levels out after a while and 120MB is certainly nothing to worry about.

In terms of the user limits I am one of the people JoeAtWork refers to - I'm confident Access can handle comfortably more than 10 users in the right situation (if the database is designed correctly, the network has sufficient bandwidth, etc).

One of my systems currently has a table of over 2 million records, a file size of over 350MB and 15 - 20 users (in the past the number of users has been between 40 and 50). I've never had any performance or stability issues.

Ed Metcalfe.

Please do not feed the trolls.....

 

[JoeAtWork]

The fact that your database is doubling in size in a day tells me there is a lot of activity on it - i.e. lots of updates and deletions. Ironically, deleting records is often the cause of much database growth, because the space they occupied is not reclaimed until you do a compact.

Do you have temp tables that are constantly being cleared and repopulated? If so, this is a likely reason for rapid database growth.

Ed - I'm going to have to find out what magic charms you use to keep your databases intact. In my experience, just about every Access database eventually becomes corrupted to some degree. In most cases a simple Compact & Repair, or maybe a decompile, will clear out the problem.

But just yesterday I was at a client's, and for no apparent reason 9 of their tables just disappeared completely. Five of the remaining tables couldn't be accessed because their security settings had spontaneously changed (the owner had changed from Admin to Unknown). The only thing I could do for them was restore their previous day's backup.

Granted, this is about the worst case I've seen so far. Also, they said they'd been getting error messages for the last few weeks, which they would ignore by closing and reopening Access. Had they called me in at that time, a simple Compact & Repair may have saved the database before things got really bad.

But it's cases like these why I recommend to move to SQL Server if at all feasible.

 

[Ed2020]

If I knew what the magic charms were I'd gladly share! Maybe I'm just lucky.

The only corruption I've had was whilst working on a development copy. Oddly enough it was the same problem you describe - disappearing tables. I don't recall having anything like this in a production system.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Crowley16]

access can handle lots of concurrent users, however what it's not very good at is handling locking, so if your users are always getting and writing to the same record then you can have unstable behaviour and record corruption, but if your users aren't updating or looking at the same record, then it's not a problem.

I've run access systems with 30/40 concurrent users and they have been ok (albiet much slower...)

As for database size, JoeAtWork is spot on, the physical limit is 2gb but the performance depends mostly upon your database design. Although I would add that if you have tables containing 100,000s or millions of records, then I would definitely consider using a bigger database system.

And Ed, I too would be interested in those magic charms. The access databases are definitely less stable and do encounter strange problems which seems to have no explanation whatsoever.
At times the only way to recover some of them has been to create a completely new db and import all the old objects across.

--------------------
Procrastinate Now!