Table with 50000 records (1 Viewer)

Derek

Registered User.
Local time
Today, 12:35
Joined
May 4, 2010
Messages
234
Hi guys,

Are 50000 records too many for MS Access table? Will the performance increase if I delete the old records ?

Many Thanks,
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:35
Joined
Oct 29, 2018
Messages
21,358
Hi. 50K is not too much for Access unless it contains files or images. A million records is not too much for Access. The main thing to watch is the file size of the ACCDB file. What is it now?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:35
Joined
Feb 19, 2002
Messages
42,973
I have tables with millions of rows. As theDBguy says, images and other attachment types take up tons of space and it is better to store a path to them than to store the document itself.

The database size is the limiting factor. If your data grows too large for a singe BE, you could move some tables to a second BE and the app will still work fine. You will loose the ability to enforce RI for the tables in separate BE though. The best solution when you exceed the 2g file size limit is to move the data to SQL Server.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:35
Joined
Feb 19, 2013
Messages
16,553
Will the performance increase if I delete the old records ?
among many factors that affect performance it depends on your indexing - if fields regularly used for joining, filtering, criteria and/or sorting are not indexed then you will see a performance improvement by deleting records because without indexing the db has to do a sequential search through the records - so on average, halving the number of records will improve performance by 50% - but it does depend where the records were in the old dataset and the new.

If they are indexed (which is good db design practice) then performance will hardly be affected whether you have one record or a million.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:35
Joined
Feb 28, 2001
Messages
27,000
I had tables with over 500,000 records but because I normalized my tables and carefully chose my indexes, I never saw more than a one-second delay in single-record searches. When I was choosing all records matching a particular indexed field, that was pretty fast, too. As noted by the others, indexing and careful structuring will let a 50,000 entry table fly on most modern systems.

There IS a caveat... Using Windows XP or higher, you would do better to not memory-choke your system. If you have a PC with 8 GB of RAM, the worst-case simple scenario of having a big front-end file and a big back-end file still won't cramp your style. Doing really big (1.5-2.0 GB) back-end files on a 4 GB system might rarely force you to start page-faulting, which would be a performance hit. But if your system is not memory starved, you'll never care about size as long as you don't max out Access itself.
 

Users who are viewing this thread

Top Bottom