Shifting from SQL to NoSQL and to which DB?

Question

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 ?

Answer 1

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).

Answer 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.

Answer 3

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.