7

We recently are having major performance related issues in our current SQL Server DB. Our application is pretty heavy on a single table we did some analysis and about 90% of our db data is in a single table. We run lot of queries on this table as well for analyticall purposes we are experiencing major performance issues now even with a single column addition sometimes slows our current Sp. Most of our teams are developers and we don't have access to a dba which might help in retuning our current db and make things work faster.

Cause of these constraints we are thinking of moving this part of the app to a NoSQL db. My Questions are :

  1. If this is the right direction we are heading ? As we are expecting exponential growth on this table. With loads of analytic's running on it.
  2. Which would be best option for us CouchDB , Cassandra , MongoDB ? With stress on scalability and performance
  3. For real time analysis and support similar to SQL how things work in a NoSQL is there a facility through which we can view current data being stored? I had read somewhere about Hadoop’s HIVE can be used to write and retreive data as SQL from NoSQL db's am I right?
  4. What might be things which we would be losing out of while shifting from SQL to NoSQL ?
Nikshep
  • 2,117
  • 4
  • 21
  • 30
  • How many rows are in the table? Are you inserting lots of rows while trying to read the data? It is possible that it would be easier to tune the database or use a reporting database etc than to change everything to NoSQL. Also, how fresh do you need the data you are querying and how important is each record? CouchDB for example uses "eventual consistency"... – Steven Oct 16 '11 at 05:54
  • Currently only 60000 rows but this would increase very rapidly. Each data is very important to us that in itself is the core of our application , query would as well be heavy. But we can live with delayed results but there cannot be inconsistency i.e hopefully the data would not change. This table has only inserts and reads no updates are allowed on it.What ever data is in the table is static in nature. – Nikshep Oct 16 '11 at 06:00
  • 2
    If you are having performance problems with 60k rows then you definitely have problems with your SQL design. – Steven Oct 16 '11 at 06:04
  • Err so the question is how should we rectify it before we end up hitting a wall ? – Nikshep Oct 16 '11 at 06:06
  • How many columns does the table have? What kind of queries are you running? – Omar Oct 16 '11 at 06:06
  • Around 17 columns and mostly queries are adding up data with different filters. There could be instance where we might end up doing inner joins on the table for analysis but they would be for different filters. – Nikshep Oct 16 '11 at 06:12
  • 60000 rows with no writes? Something is definitely wrong. Have you created indexes to optimise the queries? Can you provide a sample of a slow query? – Marcelo Cantos Oct 16 '11 at 06:16
  • This was an issue which I had come with the Query plan is in there hopefully that might help.http://stackoverflow.com/questions/6814721/sql-query-runs-faster-than-stored-procedure – Nikshep Oct 16 '11 at 06:20

3 Answers3

7

To your questions:

1.. If this is the right direction we are heading ? As we are expecting exponential growth on this table. With loads of analytic's running on it.

Yes, most of the noSQL systems are developed specifically to address scalability and availability, if you use them in the intended way.

2.. Which would be best option for us CouchDB , Cassandra , MongoDB ? With stress on scalability and performance

This depends entirely on what does your data looks like and how you will use it. The noSQL db you mentioned are implemented and behaves very differently from each other, see this link for a more detailed overview comparing the few you mentioned. Comparisons of noSQL solution

3.. For real time analysis and support similar to SQL how things work in a NoSQL is there a facility through which we can view current data being stored? I had read somewhere about Hadoop’s HIVE can be used to write and retreive data as SQL from NoSQL db's am I right?

This depends on the system you go with, because some noSQL db doesn't support range queries or joins, you are restricted in what you can view and how fast you can view.

4.. What might be things which we would be losing out of while shifting from SQL to NoSQL?

There are two major considerations for noSQL:

Query/Structure: NoSQL means no SQL. If your system actually requires structured and complex queries but you went with one of these cool new solution (especially a key-value storage, which is basically a giant hash table), you may soon find yourself in the middle of re-implementing a amateurish, ill-designed RDBMS, with all of your original problems.

Consistency: If you choose a eventual consistent system to scale horizontally, then you will have to accept your data being outdated, which may be harmless to some applications (forums?) or horrible in some other systems (bank).

Desmond Zhou
  • 1,369
  • 1
  • 11
  • 18
2

I think you should stay relational and tune the table, its indexes, and the tables it joins to. You should also consider the use of aggregated (summarized data). Perhaps a more denormalized design would help or even re-designing the data into more of a star structure. Also, operational processing and decision support (or reporting) analyses should not be run on the same tables.

TomFH
  • 55
  • 3
1

It might be possible to improve the SQL approach by checking for missing indexes etc and also seeing if the isolation level you are using is optimal. It may be possible to use snapshot isolation etc to improve performance. MSDN link

Read up on OLTP vs OLAP also.

NoSQL may still be a better option but you would still need to learn how to work with the database properly, it will come with another different set of issues.

Steven
  • 3,878
  • 3
  • 21
  • 21