Take the 2-minute tour ×

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?


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!

share|improve this question
There is no "best". Simply "adequate [enough] for the functional requirements and within resource constraints". Also, this question is so disturbingly vague -- build up some test cases, check query plans, unused/excess indexes, clustering, benefits (if any) of using a sliding-window, etc.. –  user166390 Jul 13 '10 at 21:14
Performance problems when doing what? Reading? Writing? Also, maybe define large. Many rows? Many columns? Both? Do you use partitioning? And why don't you benchmark yourself since you have the table structure, the data, the queries? –  Pascal Thivent Jul 13 '10 at 21:16
some more resources: stackoverflow.com/questions/761204/… –  Marco Mariani Jul 13 '10 at 21:51

6 Answers 6

up vote 11 down vote accepted

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.

share|improve this answer
Agreed. Learning command line to interact with databases opens up new doors, and large databases don't become so much a problem. –  swt83 Jul 13 '10 at 21:42
This is totally the advice that I was looking for. Thanks! Yes, I need to look at my queries too –  JohnB Jul 13 '10 at 21:54
+1 for the Postgres comment –  rfusca Jul 14 '10 at 3:28

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?

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
+1 for pointing out SimpleDB. Using the cloud is a great idea for my senario. However, #1, I'm not sure I can do BulkCopy with SimpleDB, and individual inserts would be way too slow (and therefore expensive), #2, this is just a pet project, and I don't want a hosted database. –  JohnB Jul 13 '10 at 21:53

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.

share|improve this answer

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.

share|improve this answer

Your Answer


