4

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?

JohnFx
  • 34,542
  • 18
  • 104
  • 162
RITBeast
  • 43
  • 1
  • 5
  • 1
    These two docs might be worth a read for someone in your organization who is involved with the technical design: http://download.microsoft.com/download/5/d/0/5d026b60-e4be-42fc-a250-2d75c49172bc/when_to_Migrate_from_Access.doc and http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQLAccessWhatsRight.doc - Docs are called: When to Migrate from Microsoft Access to Microsoft SQL Server and Microsoft Access or SQL Server 2005: What's Right - Both are from Microsoft – Ta01 Jul 20 '10 at 20:42
  • @RandomNoob : Those articles aren't really relevant to the question. Nowhere does it ask about Access vs. SQL Server. – JohnFx Jul 20 '10 at 20:47
  • This sounds like something a CEO's 15-year-old kid who "knows how to use computers" would do. Access? Are you serious? – Amy B Jul 20 '10 at 20:47
  • @RandomNoob: Thanks for the documents, I'll examine them if only to present to the boss better options than Access in the future. @Coronatus: I came into the project late after most of it was written and didn't know enough about alternative database engines to suggest one - so this is where we stand. – RITBeast Jul 20 '10 at 20:52
  • 4
    @JohnFX even if not actually asking, the questioner should nonetheless be *told* about Access vs SQL Server. If someone comes here asking how to drive a screw in with a hammer, we shouldn't be advising the least bad hammer for the job, we should be telling them about screwdrivers. – AakashM Jul 21 '10 at 07:22
  • @JohnFX: Thats why I posted it as a comment and not an answer. – Ta01 Jul 21 '10 at 12:35
  • @AakashM - Very true, but using the Access jet database as a database is hardly an example of driving a screw with a hammer. Do you mention Oracle on every SQL Server performance/capacity question? Further planting the myth that the reason to "upgrade" from Access to SQL is based on DB size in the head of a novice is worse than not mentioning it, IMHO. – JohnFx Jul 21 '10 at 12:51
  • @JohnFx: Oracle vs SqlServer != Access v/s SqlServer- you're proceeding on a false analogy - You're comparing two Enterprise Relational Databases where as I was simply pointing out references to two documents for two systems made by the same vendor, and when its time to scale up. – Ta01 Jul 21 '10 at 14:44
  • The problem here is not Access at all. It's a serious schema design question that is independent of the database engine -- performance could be problematic with a server database engine, too, if, say, the connection to the database is slow, or if the indexing is poor. The answer is very clear, too -- partitioning into separate tables just to cut down the number of records in each is a terrible idea. – David-W-Fenton Jul 21 '10 at 18:12

5 Answers5

2

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.

Piskvor left the building
  • 91,498
  • 46
  • 177
  • 222
2

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.

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
1

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.

JohnFx
  • 34,542
  • 18
  • 104
  • 162
0

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.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
TomTom
  • 61,059
  • 10
  • 88
  • 148
  • 6
    This answer is a bit "Ranty" suggest you tone down the rhetoric and lose the "stupid" comment as it may offend the OP who came up with the idea for splitting the tables. It may not be a good idea, but it would be better not to phrase this in a way that might be taken personally. – JohnFx Jul 20 '10 at 20:45
  • 1
    Yeah, but given that someone really ignors all the MS talks for the last 10 ears really puts me up in the modd to say he deserves it. This is personally - I would fire whoeve rmade this decision and make sure he pays (by court order) for the rewrite. – TomTom Jul 20 '10 at 20:46
  • Well, my answer to this is always "get lost". Really. I wrote it as I see it. You don't like it, vote it down. Your right, my right. – TomTom Jul 21 '10 at 04:57
  • Thanks for the information TomTom - This project has been a bit of a mess from the get-go, but based on your numbers the database should easily handle even our most heavy users. While abrasive, your answer was most informative in regards to the question at hand. (This is not being used to service more than 20-25 users at a time at absolute maximum) – RITBeast Jul 21 '10 at 14:47
  • I would not like that - seriously. 20-25 users on access may stress it. It is file based - that sort of means a lot of possible problems (failuress of network write badly affect the database integrity). At least for a new version I strongly suggest you throw access out of the window. THe earlier the better. – TomTom Jul 21 '10 at 17:05
  • Your assertion that MS has been deprecating Access (i.e., Jet/ACE) for the last 10 years is itself FACTUALLY ERRONEOUS. If you think it's not, provide a citation that is current (rather than something dug up from documentation that has long go been superseded). – David-W-Fenton Jul 21 '10 at 18:14
  • Other than the claim about MS deprecating Access, I think your answer is fine. Access/Jet/ACE is fine with many databases with 100s of thousands of records. But if that's the starting point, not the ending point, I'd definitely say a different database engine is appropriate, as 100s of thousands of records is hitting the danger zone for Jet/ACE in terms of performance and storage capacity, so upsizing to SQL Server or some other server back end with higher capacity should be a no-brainer. – David-W-Fenton Jul 21 '10 at 18:16
  • We range from (mostly) single users and 5-or-less users to a few companies that are in the 20-25 user range. Would something like sqlite be a better solution relative to Access on a file share? – RITBeast Jul 21 '10 at 19:53
  • No. The moment you run into problems with the file share approach you basically go client/server. Point. This is the main reason Access is a toy - (and, btw., SQL Server STRONGLY discourages putting the db files on a network share). Plus a lot of administrative issues (try taking a backup from access WHILE USERS WORK ON IT, for example - impossible). – TomTom Jul 21 '10 at 21:09
  • 1
    @TomTom: Access is not a toy. Jet/ACE is not a toy, either -- it has its proper applications, but you have to know what you're doing and do regular maintenance for it to be reliable and perform well. in that regard, it's just like every other database engine -- it's only the details of the care and feeding that are different. – David-W-Fenton Jul 22 '10 at 19:24
0

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

Kevin Ross
  • 7,185
  • 2
  • 21
  • 27