What size can a access table handle before affecting performance?

[Matt in UK]

I have an assignment for updating (maintenance, version control
documents)database analysis documents (dfd's, lds etc). One table is stock
which is used most within the database. Presently stock is kept for for
records for 6 months and has been requested to be held for 5 years before
deletion. This will increase the table size 10 fold and bring it to around .8
mb. My question is how will this affect the performance of the database and
should i create a new table named something like 'archived stock' for the
extension in time of keeping the records. I would only want to create a new
table if the database is affected through sequential record searching. Please
help!!

 

[Douglas J. Steele]

Hopefully "sequential record searching" isn't an issue. You should have
appropriate indexes set up so that it doesn't need to do tablescans to find
the data.

I'm assuming your .8 mb is a typo: that you either mean 8 mb (which is
nothing), or .8 gb (which is starting to get up there, but with an
appropriate design should still be manageable) I've worked with tables with
in excess of a million rows, and while it was slow for certain calculations,
it was still usable.

 

[John Vinson]

On Sat, 6 Nov 2004 08:29:01 -0800, "Matt in UK" <Matt in

I have an assignment for updating (maintenance, version control
documents)database analysis documents (dfd's, lds etc). One table is stock
which is used most within the database. Presently stock is kept for for
records for 6 months and has been requested to be held for 5 years before
deletion. This will increase the table size 10 fold and bring it to around .8
mb. My question is how will this affect the performance of the database and
should i create a new table named something like 'archived stock' for the
extension in time of keeping the records. I would only want to create a new
table if the database is affected through sequential record searching. Please
help!!

An 8 MByte database is a VERY modest size. I'm working right now with
a 380 MByte database; there are performance issues, but by setting the
database's properties correctly (turning off all Subdatasheets,
turning off Name Autocorrect, defining the necessary indexes,
designing queries correctly) the performance is satisfactory to the
users.

Of course, making a table ten times larger will "affect" performance.
But unless your users are extremely intolerant of one-second delays in
response, you should be fine.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps