How big is too big?

[Mug1]

In an answer to another question, one of you says that if the database gets too big, Access has problems. Can you define "too big" for me? Thank you.

 

[JimHorton]

1 gig is the hard limit per .mdb.&nbsp;&nbsp;Prior to that limit, you may or may not have performance problems, depending on whether the size is due to large numbers of records, many objects, or just failure to compact. But you can really get almost as big as you want, since you could put, say, one or two large tables into a separate mdb, smaller tables into another mdb, etc.&nbsp;&nbsp;<br><br>&nbsp;&nbsp;As a side note, when you set the SQL property of&nbsp;&nbsp;saved queries, especially in loops, this can cause the db to grow very huge very fast, even though there may be few records and few objects--this is because Access stores every iteration of the SQL property as a saved querydef.&nbsp;&nbsp;A loop that iterates several thousand times will result in several thousand 'hidden' saved queries, which aren't cleared until compaction. I once had a db grow to 1 gig in about an hour (from about 40 meg) just off-loading data--I came back to my desk to see a &quot;disk or network error&quot; message.<br><br>Jim

 

[NoteSmith]

I cannot verify this, but I read that Access is designed for peak efficiency at 100,000 records.

 

[harryrich]

I work with a system which weekly imports about 300,000 separator/value records with about 1K of data each.&nbsp;&nbsp;Off the resulting raw database I run a suite of eleven reports for each of four organizations.<br><br>The system processes from 10,000 to 40,000 of these records weekly as new transactions.&nbsp;&nbsp;&nbsp;30+ classes of funds in 60+ budgets compenstate 70+ vendors in a manner depending on the service rendered and the characteristics of the recipient.&nbsp;&nbsp;Output is summary accounting on paper used to generate checks, and a complete EDI accounting for each vendor.&nbsp;&nbsp;Total database size is close to 0.5 GB and the current design will allow it to go to about 2.5 GB.<br><br>From the FTP in of the extracts to the deposit of the EDI in the outgoing FTP sites takes about four hours including database compaction and complete backup.<br><br>While this is all workable, a system this size needs some tuning and resources:&nbsp;&nbsp;<br><br>The system applies a 500 MHz Pentium III with 128 MB of RAM.&nbsp;&nbsp;The MB are probably more important than the MHz, but the system spends lots of time process bound.&nbsp;&nbsp;The four hours includes a process which takes about 25 minutes on this workstation but took more than 4 hours on a Pentium 166 with 32 MB of RAM.<br><br>My feeling is that with current processor technology the practical limit of this database is close to but something under the 2.5 GB design limit at something under four times it current extract size and something under three times its current transaction rate.&nbsp;&nbsp;<br><br>In a database of this size even one-time queries can need optimization with supporting indexes.&nbsp;&nbsp;And don't select all the records in a table and delete them.&nbsp;&nbsp;It is MUCH faster (almost instantaneous) to write a delete query, run it, and throw it away.<br><br>Action queries seem to generate records for roll-back even if you select the option not to use a transaction.&nbsp;&nbsp;The total size of the records modified seems to be a bigger factor than the size of the modification.&nbsp;&nbsp;On large jobs Access goes through the overhead of maintaining the transaction, stops after a long time, asks you if you want to continue without a transaction and finaly completes after you say yes.<br><br>But it all works.&nbsp;&nbsp;And I believe it is relatively low maintainace compared to Oracle or SQL Server. <br>