[AccessD] Record Maximum

>What is the maximum or the most records that anyone has seen in a table
before the database started to degrade in performance.

I don't think it is possible to quantify it in quite those terms.  The
number of users in the db is a bigger concern in my experience than raw
"number of records in a table".  

That said however, you have to go back to "what are you trying to do?".  If
you are trying to update records using an update query while 10 people are
in the database trying to use that same table, I have experienced the
database becoming unusable at 1/4 million records.  The update operation had
to be moved to "after hours".

The thing to remember is that Access is a file based back end, not a server
based back end.  Thus if you have an application that needs to join a dozen
tables with many FKs with a dozen joins and then filter to a subset etc...
You just have to know that the way access works is to pull "a lot of" the
indexes in question across the wore into the local workstation, sort through
the joins, where clauses etc. assemble the requirements for data records
from each table, then ask the file server to "go here in the file and get
this chunk of data, now go here in the file and get this chunk of data" etc.

>What would you consider the maximum record count before archiving?

Again, the question makes no sense to me as stated.  Archiving and getting
data back from archives is an inherently slow operation.  Thus if the
archived data are never used, then archive whenever the data is not actively
useful any more.  If you will need the data daily, or even weekly (and
quickly) then you might want to push the archive out until the data isn't
actively needed any more.  

OTOH, if you need the archived data weekly, but it is for a report that can
be done overnight for delivery on a Monday morning, then archiving it is
very plausible.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gowey Mike W
Sent: Monday, October 30, 2006 4:38 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Record Maximum

 

Hi Everyone,

Just a quick question.  What is the maximum or the most records that anyone
has seen in a table before the database started to degrade in permormance.

What would you consider the maximum record count before archiving?


TIA for your thoughts,

Mike Gowey  MCP, MCDST, MCSA, A+, LME, NET+ Team Leader - SouthEast Region
Information Systems Unit
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com