Which affects Access Database performance more: Thousands of tables or Millions of records?

Question

We're using an Access Database as the back-end to our software product. The program has been alpha/beta tested at a company for about 2 years now and we've noted that one of our tables has been filled with over a hundred thousand records in that time. This is probably not an example of the heaviest usage that our product will endure, and we're concerned about performance 5-10 years down the road.

There's a logical way for us to break down this enormous table into several thousand tables containing a few hundred records, but I don't think this solution is likely to help with any possible slowdown as the database will become bloated with tables instead of data (Though I have no formal training in databases, so what do I know).

I was hoping someone more informed than I might provide some insight into whether we're likely to see a significant slowdown and if so, which solution is likely to keep performance better in the long-term?

Answer 1

The question is a schema question and if the table partitioning you're contemplating is not a natural fit for the actual data, it's going to exacerbate performance problems, not ameliorate them. In regard to the 2GB file size limitation, it doesn't likely matter how you slice and dice the data -- if you're approaching that limit (within 50% of it, I'd say), you really need to have an upsizing path in mind.

On the question of a Jet/ACE data store, I would say that any app that has tables with 100s of thousands of records is already one that should be evaluated for upsizing. If it's possible/likely to have millions of records, I'd say it's a no-brainer -- upsize.

This is not because of any inadquacy of Jet/ACE, just because as requirements change, appropriate technology changes. A married couple might find a Mini Cooper fine when they get married, and it might accomodate their first child just fine, but if they are contemplating a couple more children, they should really seriously consider getting a larger car -- not because there's something wrong with a Mini Cooper, but because they've outgrown what it's best for.

Answer 2

The program has been alpha/beta tested at a company for about 2 years now

For the last about 10 years Microsoft has advised people NOT to use Access as database but to go with SQL Server in various versions.

and we're concerned about performance 5-10 years down the road

Given developments of the lat - hm - 10 years I would not be. I would seriously be concerned whether Access is actually still able to store data in 10 years down the road at all, or whether the call is "program for sql server" at one point in between.

There's a logical way for us to break down this enormous table into several thousand tables containing a few hundred records, but I don't think this solution is likely to help with any possible slowdown as the database will become bloated with tables instead of data

Access is well able to handle a million or 5 million records. SQL Server goes well into BILLIONS of records. The moment you get into problems with Access, basically, you earn any problems you get based on the - and I really find no way to say it more nicely - the tremendous ignorance to even try using access for a serious database, as - as I already said - MS is discouraging this for the last 10 years.

THOUSANDS of tables to split off a table is unwise; SQL databases are not designed for this. Even using clustered tables in SQL Server Enterprise (doing exactly this) is not really targeting you having tens of thousands of partitions.

You are a LOT more likely to just die in access - access is simply not a database server. Back to the drawing board.

That said, Access about 18 years ago or so added some technology acquired with FoxPro allowing it to easily handle tables with millions of records (not tens but hundreds of millions), so you are very safe at this moment (except the nightmare of trying to do a db repair, backup etc. on something like that, or even the nightmare of running a multi-user application through a network share.

SQL Server, otoh, I have a table currently at around 650 million records growing to about 10 or 20 billion in the next 6 months when data loads start, and no problems so far.

Answer 3

I’m going to steer clear of entering the access –v- SQL server debate in this thread and instead just answer the OP’s question.

If the data can be split and people wont be querying across those splits then it might be an option worth testing however there is a limit of 2048 open tables in access so you might want to watch out for that.

Its been said before however that if you have to ask what is the maximum number of something then chances are you are doing it wrong, I think this is an example of that. If it was splitting it into 10 tables maybe but thousands? I’ll pass on that one

Answer 4

Databases are generally optimized to deal with a high number of rows; the question is, will you be able to maintain thousands of almost-identical tables? (Few can, it is complicated to code with)

First of all, test the possible scenarios. I'm not familiar with your data, so I can't tell you if millions of rows will be too much for the db (after all, this is MS Access, not a real database) or not.

If find that you have problems with table size, and your datasets can be divided into less used (older?) and recent data, I'd suggest splitting tables in two: table and table_archived (which contains the less frequently used/older records). This could be a reasonable compromise between table size and manageability.

Answer 5

Sounds like overkill to break down the table that much, but horizontal partitioning is a very sound performance optimization strategy that is used in many database platforms.

With MS Access you aren't going to see much performance degradation on a well designed database for reads, even with millions of rows. Also, having a lot of tables won't cause you much in terms of performance problems either if you compact and repair it frequently, but the bigger issue is the complexity of maintenance. I'd say not to split the table until it has at least a million rows in and/or is showing performance issues with queries on that table.

Here's the rub: This type of partitioning can hurt performance considerably if the users are constantly querying across multiple tables in a partition that you need to UNION back together. In situations where the partitions contain archival records that aren't searched very often it works much better. If you think you will need to frequently query across tables, don't go there.

Your biggest scalability hurdle is going to be related to the number of users. If you are anticipating 100's of users you need to plan very carefully or perhaps consider a client-server database backend.