9

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.

3

There are a couple of concepts which need to be distinguished. One is about structure and the other about schema.

Structured data is one where the application knows in advance the meaning of each byte it receives. A good example is measurements from a sensor. In contrast a Twitter stream is unstructured. Schema is about how much of the structure is communicated to the DBMS as how it is asked to enforce this. It controls how much the DBMS parses the data it stores. A schema-required DBMS such as SQL Server can store unparsed data (varbinary) or optionally-parsed data (xml) and fully parsed data (columns).

NoSQL DBMSs lie on a spectrum from no parsing (key-value stores) upwards. Cassandra offers reatively rich functionality in this respect. Where they differ markedly to relational stores is in the uniformity of the data. Once a table is defined only data which matches that definition may be held there. In Cassandra, however, even if columns and families are defined there is no requirement for any two rows in the same table to look anything like each other. It falls to the application designer to decide how much goes in a single row (also referred to as a document) and what is held separately, linked by pointers. In effect, how much denormalisation do you want.

The advantage is you can retrieve a full set of data with a single sequential read. This is fast. One downside is that you, the application programer, are now solely responsible for all data integrity and backward compatibility concerns, for ever, for every bit of code that ever touches this data store. That can be difficult to get right. Also, you are locked into one point of view on the data. If you key your rows by order number, how do you report on the sale on one particular product, or region, or customer?

  • 1
    In our case, the data we store is basically forms data. The user defines the form at runtime and can modify it at any time he likes. A form can be constructed from thousands of fields. This can happen if list-like data is captured. If we knew the data upfront - at db design time, we would normalize it. Your comment about the view on the data makes me think: If the forms are written as document, how do you create a view on them for a list or sort the data by a field in real life? Map-reduce the data, recollect and prepare the list in code? – thst Aug 27 '15 at 19:02
  • Historically it was all client side - you got your documents back and you did what you had to. CQL has clauses that any SQL developer would be familiar with. Map Reduce is the go-to architecture for large datasets. And it looks like Cassandra 3.0 will have Materialized Views. – Michael Green Aug 30 '15 at 10:33
5

Despite the mainstream of noSQL databases IMHO the decision about adopting such technology should be made according to the achievements needed according to the information stored, not only attending to the performance you currently have. This means that maybe your best option is to stick to the SQL database and improve your HW.

But additionally I read something in your question that made me think. There is not much about the current status of your database but your sentence "we basically store data entered by users in various ways as "key-value" lists" makes me think about if the problem wouldn't be a poor data model rather than the lack of physical resources. I've managed really large tables (+10 billion rows) with incredible performance in "traditional" SQL databases.

I'm not saying it is wrong, just, since of course I cannot assess you in the right data model with such little information about your current solution, but just think about revisiting your data model as an additional option along with the rest since you may find some clue scratching there.

Usually key-value lists are fine as a trade-off when you cannot implement the model in its final state because you don't know the different keys you'll have to face, or when you will need the values of one of the possible keys for a certain element. But when implemented, I usually like to re-think such decisions after a while when you have gathered enough amount of information to identify the common case of use and decide whether data model decision is the best. If you know you'll have a certain number of keys, try to do some benchmark with a design of a regular table in the traditional way

CREATE TABLE benchmarkTable (
  element INTEGER,
  key1 VARCHAR(xx),
  key2 INTEGER,
  key3 DECIMAL(5,2),
...
);

...and adding the corresponding indices. Try it out and measure execution plans with both approaches. You may be surprised specially if you gather more than one key at a time, since, among other advantages the data block size should be reduced and thus the performance would be improved.

Hope this helps, or at least broadens the possibilities and opens a new line for investigation.

  • I appreciate your answer, but in fact, the situation is so, that we really do not know the structure of the data. We store forms data and we do not know the structure of the model of the form. We know of course in the application, but it is dynamic and can be changed at any time. – thst Aug 27 '15 at 18:56
  • Understood. I don't know how challenging is this but as an idea to try, would it work to create a table containing the pool of common keys referenced in the user filled table by a performing FK, maybe an INTEGER? Maybe it is a bit better performing than indexing a varchar column that, if it is changing very dynamically I guess it will not be short. And it would reduce the size of the index as well. – LironCareto Aug 28 '15 at 9:19
  • 1
    This leads away from the question, but we have discussed certain limitations on user possibilities. For example reduce the max app-table fields to 10 vanilla varchar db-fields. This is a denormalization of the schema to select basically the head dataset and 10 app-column values in one go or with max one join on the extra db-table. On changing the relevant values, we would have to modify this one db-row in code as well. This seems feasible and reduces the amount of joins by up to 10 for a select to display the app-table. Yet, changing the user's app-column definition is very expensive then. – thst Aug 28 '15 at 11:40
  • 1
    It is ok, don't worry. I think I see your point, and your approach looks for me as a good trade off between performance improvement and feasibility. It is important to have statistics of use, obviously, to determine those fields. Have you benchmarked it? At least it may buy you some time until you find a (better? definitive?) solution or maybe discover that you can run with this for a long time. – LironCareto Aug 28 '15 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.