Which database (DBMS) can best handle large tables?

Question

I also have a very large table in SQL Server (2008 R2 Developer Edition) that is having some performance problems.

I was wondering if another DBMS would be better for handling large tables. I'm mainly only considering the following systems: SQL Server 2008, MySQL, and PostgreSQL 9.0.

Or, as the referenced question above eludes to, is the table size and performance mainly a factor of indexes and caching?

Also, would greater normalization improve performance, or hinder it?

Edit:

One of the comments below claims I was vague. I have over 20 million rows (20 years of stock data & 2 years of options data), and I am trying to figure out how to improve performance by an order of magnitude. I only care about read/calculation performance; I don't care about write performance. The only writes are during data refreshes, and those are BulkCopy.

I have some indexes already, but hopefully I'm doing something wrong because I need to speed things up a lot. I need to start looking at my queries too.

The comments and answers provided already helped me understand how to start profiling my database. I'm a programmer, not a DBA (therefore Marco's book recommendation is perfect). I don't have that much database experience and I've never profiled a database before. I will try these suggestions and report back if necessary. Thank you!

Answer 1

Switching DBMS is not the solution.

How big is big? What indexes does it have?

If it really is that big then can you partition it?

Answer 2

I think simpledb is the choice. Considering that amazon uses it for their plattform.

Answer 3

You are a long way from maxing out SQL Server. If you don't address the design and indexing issues that are the source of your performance problems you will just wind up porting them to a different platform.

There isn't going to be a silver bullet solution that will "Make the db fast" or else a lot of DBA's would be out of a job. You are just going to have to do some performance profiling and fine tune your database design and indexing strategy to get performance in line with your requirements.

Sorry, there really aren't shortcuts.

If you give more detail on the queriesthat are problematic in terms of performance and the underlying table structures/indexing, I'll bet the smarties on SO will be able to provide some guidance.

Answer 4

The two DB products to which a majority of really big companies, banks, militaries, governments entrust huge amounts of data are Oracle and DB2. Both come with appropriately fat price tags. Both products have decades of intensive professional tuning behind them, though often the benefits are only available to people who foot (additionally!) the bill for high-powered consultants. I have a friend who is such a DB2 consultant; he charges an arm and a leg but achieves some amazing performance gains with measures other people wouldn't consider.

Neither of these is in your short list, so chances are you won't consider them. I suspect that any of the other products could handle your load too, though I have some distrust of Microsoft products. So... consider this as just information for information's sake.

Answer 5

80M rows is not big. You just need to learn how to design and query data of that size. Which might include normalization, denormalization, clustering, indexing but very often the tradeoffs are deeper that they seem. Adding indexes can actually hurt performance even for reading, for instance, if the optimizer is not good enough or decides upon the wrong statistics.

I suggest you read Refactoring SQL Applications because it approaches the problem not from a "DB tuner" but from a developer's point of view.

The book is by the author of The Art of SQL and compares Oracle, SQL Server and MySQL under many scenarios. It's pragmatic and comes with some useful graphs.

I would stay away from MySQL unless forced to. Postgres 9.0 rocks according to several definitions of "rock" but I would still use 8.4 in production for a few months.

And if you want people to help you with this table, provide as many details as possible: schema, indexes, data distribution, pattern of usage, etc.

Answer 6

Just saw this. You need to check out infobright.org. For number calculations, its great. It provides a database engine for mysql, but built for analysis, not transactional updates.

The only issue you'll have is your data set is a little small for infobright, but should work fine.