Question Many records speed tactics

[mcdhappy80]

Very soon I'll start to build a database which will approximately have 5000 new records a month, and it will have to keep records for last two years, so that is 5000 * 12 x 2 = 120000 records.

The scenario is :
Database will be empty at the beginning,
each month there will be 5000 new records
After two years when the records for the third need to be added the records for the first year will be deleted.

Questions:
Is this not to much records for access database to hold (and work with) at one the time?
What tactics must I think ahead of in order to optimize my access database to work as fast as it could?

 

[georgedwilkinson]

1. No, it is not, if your data is normalized, which it doesn't sound like you're doing.
2. Normalize your design making sure to include dates and valid columns as appropriate in each table. Failing that (in a DSS system) make sure to include indexes as appropriate to enable quick selects and sorts.

 

[Banana]

In regards to the first question- I have a sample database that holds 4 millions worth of records and it does work pretty well. I know others has databases with more than a million of records in production, so yes, 120,000 records is very feasible IF:

As my esteemed colleague George pointed out, the design is normalized to begin with.

Not only should you read up on normalization, even if you think you know, it never hurts to refresh and verify you remember the principles correctly but also you may want to take a look at some articles with regards to optimizing. You didn't say whether it'd be a pure Access solution or would there be an ODBC backend, but no matter; for most part optimizations apply and you'd need to treat it as a server/client anyway.

Optimizing Client/Server performance
Information about query performance
How to optimize Office Access and Jet database engine network performance with Windows 2000-based and Windows XP-based clients

HTH.

 

[mcdhappy80]

In regards to the first question- I have a sample database that holds 4 millions worth of records and it does work pretty well. I know others has databases with more than a million of records in production, so yes, 120,000 records is very feasible IF:

As my esteemed colleague George pointed out, the design is normalized to begin with.

Not only should you read up on normalization, even if you think you know, it never hurts to refresh and verify you remember the principles correctly but also you may want to take a look at some articles with regards to optimizing. You didn't say whether it'd be a pure Access solution or would there be an ODBC backend, but no matter; for most part optimizations apply and you'd need to treat it as a server/client anyway.

Optimizing Client/Server performance
Information about query performance
How to optimize Office Access and Jet database engine network performance with Windows 2000-based and Windows XP-based clients

HTH.

Thanks George, and Banana for You answers.
One of the reason I was asking was if Access couldn't deal with that many records then I would be forced to switch to different platform.
Now when I know it can there is no need to change it, so yes, I will use access as a pure solution.

Cheers!

 

[Banana]

Note that even if you choose to use an Access backend, it never hurts to treat it as a server/client architecture so if a change in organization raises a need to move to a bigger'n'beefier backend, the move will be much less painful.

Good luck.

 

[mcdhappy80]

Note that even if you choose to use an Access backend, it never hurts to treat it as a server/client architecture so if a change in organization raises a need to move to a bigger'n'beefier backend, the move will be much less painful.

Good luck.

And what exactly do I need to do that?

 

[Banana]

Basically explained in the links I gave you.

There may be some things missing, most notably using filters which may work OK for Jet backend in a small users but is abysmal with an ODBC backend. Substituting filter by form with a query by form usually is a good fix.

You could take a look at ODBC guide or at least skip to the links at the end of article.