What database (DBMS) can handle large tables best?

Question

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

I was wondering if another DBMS would work better with large tables. I mainly consider only the following systems: SQL Server 2008, MySQL, and PostgreSQL 9.0.

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

Also, will normalization improve performance or prevent it?

Edit:

One of the comments below is vague. I have over 20 million rows (20 years of stock data and 2 years of optional data), and I'm trying to figure out how to increase productivity by an order of magnitude. I only care about read / compute performance; I don't care about recording performance. The only entries are during data updates, and this is BulkCopy.

I already have some indexes, but I hope I'm doing something wrong because I need to speed things up quickly. I also need to look at my queries.

The comments and answers provided have already helped me understand how to start profiling my database. I am a programmer, not a database administrator (so the recommendation on Marco’s book is perfect ). I don't have much experience with databases, and I've never done database profiling before. I will try these suggestions and send a report if necessary. Thanks!

Answer 1

Lines 80M are small. You just need to learn how to create and query data of this size. Which may include normalization, denormalization, clustering, indexing, but very often the trade-offs are deeper than they seem. Adding indexes can hurt performance even for reading, for example, if the optimizer is not good enough or solves erroneous statistics.

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

The book is written by The Art of SQL and compares Oracle, SQL Server, and MySQL in many scenarios. It is pragmatic and comes with some useful graphics.

I would stay away from MySQL if I hadn't been forced to. Postgres 9.0 for several definitions of "rock", but I will still use 8.4 in production for several months.

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

Answer 2

Switching a DBMS is not a solution.

How big is big? What indexes does he have?

If it really hurts so much, can you break it?

Answer 3

You are far from the maximum load of SQL Server. If you are not considering the design and indexing issues that are the source of performance issues, you simply port them to another platform.

There will be no solution for a silver bullet that will “quickly make db”, or a lot of DBA will be out of work. You just need to perform performance profiling and fine-tune your database design and indexing strategy to get the performance to suit your requirements.

Sorry, there are actually no shortcuts.

If you give more detailed information about queriesthat, which are problematic in terms of performance and basic table / indexing structures, I bet that wiseacres on SO will be able to provide some recommendations.

Answer 4

I think simpledb is the choice. Given that Amazon uses it for her record.

Answer 5

Just saw it. You need to check out infobright.org. For numerical calculations, this is great. It provides a database engine for mysql, but is intended for analysis, not transactional updates.

The only problem you will encounter is the dataset, a little intuitive, but should work fine.

Answer 6

Two DB products that most of the truly large companies, banks, military and governments trust in a huge amount of data, Oracle and DB2 . Both come with suitable thick price tags. Both products have decades of intense professional customization behind them, although often the benefits are only available to people who carry (optional!) A bill for highly qualified consultants. I have a friend who is a DB2 consultant; it charges the arm and leg, but achieves tremendous success in productivity with measures that other people would not consider.

None of them are on your short list, so most likely you will not take them into account. I suspect that any of the other products can handle your workload, although I have some distrust of Microsoft products. So ... consider this as just information for information.