Is Access 2010 performance based on records in a table or file size ?

[edmscan]

I have an Access 2010 database .. and will be adding about 25,000 records a day to it. So it will get pretty big fast .. so 9 million a year. Is this too big for Access ?? Or is it more a issue of file size .. I know that Access 2010 can go up to 2 GB.



I am interested in how I can maximize my performance and not have the database go crazy on me.

The data is as follows .. One table. The other tables will be small, just this one will increase in size really.

Id .. Autonumber (primary key)
When .. datetime
Radio .. integer (indexed .. w duplicates)
Group .. integer (indexed .. w duplicates)
Type .. text 4
TransType .. text 2

I am considering a purge of old records if needed .. like only keeping the most previous 5 million records or some reasonable number. Like .. then I would think I should try to number my records backwards (using long int rather than autonumber and number records backwards on import) .. ie record 1 would be the most recent one and only keep the last X number of them for performance reasons.

Looking for thoughts ?

[June7]

Is 9 million records too much for Access? - depends on how much data is stored in each record.

The sheer number of records can impact performance of some queries.

Can reverse number records if you want but expect record purge could be managed otherwise.

[edmscan]

Is 9 million records too much for Access? - depends on how much data is stored in each record.

The sheer number of records can impact performance of some queries.

Can reverse number records if you want but expect record purge could be managed otherwise.

I have posted 10 sample records above ... this will be exactly how each record will be. There will be no difference. Just looking for best way to optimize things.

[June7]

What is the size of the database at present? How long did it take to get to that size? How long will it take to reach 2GB? Is the number of records consistent for each import? How much does the db grow with each import. How many imports before hit 2GB? Do the math.

Be sure to run Compact & Repair periodically.

[edmscan]

It is 7 MB total file size now (that is with all the forms etc ... ) with about 80,000 records in that table. That is about 3 days worth.

Should I split the data and the forms ? I do make changes all the time to the forms etc. I think I will be fine for a while.

Yes the number of records should be similar each import.

Yes I will compact and repair .. but for now I will just let it grow and see. I only run one query on it .. and it takes about 1 sec to run right now. The one I posted yesterday.

[June7]

A db for multiple simultaneous users definitely should be split. Otherwise, if there are a lot of forms/reports, will gain some data storage by splitting but possibly not enough to matter in your case.

[edmscan]

I ran the performance analyzer on it .. and made 2 changes. I think I am good .. I will just let things go for now and see how the performance is.

Thank you ..

[edmscan]

I actually have determined that I do not really have to capture the "when" field at all as I am really just interested in is the last date stamp and that is captured in another table. That will cut down on my imported data by well over 50%. So .. each record will be very small, so that will help but there will be lots of records but I think that I will be fine. But only time will tell .. and should be an interesting test of the performance of Access.

[edmscan]

I have been doing some thinking and reading .. and determined that each record should be about .. 12 bytes. I will only store the absolute minimum required data.

The other tables in the database will have about 6500 and 450 records. So I am good there, as they are pretty small fields too. It is just this one table that will be big as far as records are concerned.

So .. 9 million rows per year at 12 bytes a row, 108 million bytes .. and add 10% just because, gives me 120 MB approx for 1 years worth of data. Really .. I will want to archive at that point, who wants old data right ?

So this will be interesting ... and maybe just for fun, I might just copy the database structure and make a table with 9 million rows in it .. and see how the performance is re running of my one query and form that will use that data. I restrict the form to 45 records as that is what will fit on one screen. It is more of how that one query will run on those 9 million records.