we are currently running at the edge of resources with our mssql server based solution.
We have now many traditional options regarding the next move to tackle the load:
- buy faster CPUs and IO
- split some customers to seperate server
- move db to cluster
All are either expensive in terms of licensing and hardware or time. So, I want to add another option by moving the whole system to a scalable solution that nosql engine cassandra promises.
Yet, I am not sure and not experienced with noSQL databases, so I need to understand the structure of "unstructured" data.
In our application, we basically store data entered by users in various ways as "key-value" lists. There is a parent table, that contains the head element (like an Order) and there is a child table with the key-value pairs comprising the contents of the order (like Order_Lines).
Business-wise, Order and OrderLines are a unit. But due to the RDBMS, they are stored in tables and must be joined all the time.
During operations, we sometimes choose to load only the top part, but most of the time, we load the head row + some KVPs to display some useful information.
For example, in an overview list, we show the head identifier + some values to in columns for each row.
UPDATE: We store forms of any kind. So, basically we store "documents". Nevertheless, we have to prepare and search through these forms by any value, sort etc. Data access control adds another layer of compexity on the database.
As you may guess, the amount and availability of certain KVPs varies from object to object. There is no valid possibility to create single tables for each kind of object as we would have to create thousands of tables for the different data combinations.
Would this kind of "Dictionary" like datasets be better stored in a noSQL database? And will we have performance benefits from this? Would cassandra model these head+KVPs as one dataset? Looking at the cassandra webpage and some tutorials, I have the impression, that there is not so much of a difference between our RDBMS and cassandra in terms of data organisation - leaving us with the same huge amount of joins if you wanted to select 5 KVPs for a list for each row.
Enlightenment is welcome, also pointers to papers explaining the issues are ok.