locked
How to reduce size of a huge table in Access RRS feed

  • Question

  • We have an Access mdb file that is getting too large.  It is about 1.7 GB, and will probably hit 2.0 GB in two or three months.  It has already been compacted.  There are only two tables, and 99% is from just one of the two tables, which has about 8.4 million records.  What are some strategies I could use to reduce the file size?  I've already done the database splitting strategy.  This is one of three back-end files in the split set.  I think some users might have Access 2003, so if you have a solution that is unique to Access 2007/2010, I'm not sure if it will be viable or not.

    Thursday, December 19, 2013 10:58 PM

Answers

  • Hi Tim

    Sorry for the delay. Marry Xmas!

    The only column I see where you may have a chance to reduce the size is the VendorName. This column looks not normalized but I'm not sure about this. Do you have a vendor table? then you should only have the VendorNumber as a reference to this table, not the Vendor Name. If Vendor Name is the contact's name of the vendor then you should consider a VendorContacts table and replace it with a VendorContactNumber instead of storing the Vendor Name again and again.

    Anyway with >2Mio rows and this data fields I won't expect you can get it below 1GByte which IMO is already too much for a Jet database with a high data volume. I recommend you strongly to switch to an active database management system, you could start with SQL Server Express (free edition). In Release 2008R2 they increased the maximum database sitze from 4GB to 10GB per database.

    Henry


    Thursday, December 26, 2013 5:42 AM

All replies

  • Can you give us the definition of the table? Fieldnames and Types as well as Keys (Primary, Foreign) and indices you have defined on this table?

    Thanks

    Henry

    Monday, December 23, 2013 9:19 AM
  • Here it is.  Your question prompted me to change the datatype on Account and Fiscal Year, which were previously Text.  That reduced the size by 0.1 GB, but I definitely need to make further improvement.

    Company Code Text
    Cost Center Text
    Account Long Integer
    Item Number Text
    Item Description Text
    Charge Code Text
    Qty Double
    Unit of Measure Text
    Cost Per Unit of Measure Double
    Total Expense Double
    PO Number Text
    Vendor Name Text
    Vendor Number Text
    Source Type Text
    Fiscal Year Integer
    Fiscal Period Long Integer
    Journal Type Text
    Transaction Date Date/Time
    Tuesday, December 24, 2013 9:45 PM
  • Oh, and there are indexes on Company Code, Fiscal Year, Fiscal Period, and Account.

    I deleted a couple other indexes that were questionable, and reduced another 0.1 GB.

    Tuesday, December 24, 2013 9:50 PM
  • Hi Tim

    Sorry for the delay. Marry Xmas!

    The only column I see where you may have a chance to reduce the size is the VendorName. This column looks not normalized but I'm not sure about this. Do you have a vendor table? then you should only have the VendorNumber as a reference to this table, not the Vendor Name. If Vendor Name is the contact's name of the vendor then you should consider a VendorContacts table and replace it with a VendorContactNumber instead of storing the Vendor Name again and again.

    Anyway with >2Mio rows and this data fields I won't expect you can get it below 1GByte which IMO is already too much for a Jet database with a high data volume. I recommend you strongly to switch to an active database management system, you could start with SQL Server Express (free edition). In Release 2008R2 they increased the maximum database sitze from 4GB to 10GB per database.

    Henry


    Thursday, December 26, 2013 5:42 AM