taowen

You might be wondering when you are able to use those cool nosql database in your project. But why? You manager might ask. You'd better be prepared. I see nosql database provides two benefits:

1. Scalability By removing schema, the data entry can be very easily replicated. By removing foreign constraint, the data entry can be replicated without replicating all the entries it references. Then we can build shading around user boundaries.
2. Productivity The object in the memory and the persisted object state in the database are really the same thing. If we need to do significant mapping between these two models, there must be something wrong, the productivity might hurt as well as the performance. If the two are really the same thing, why we have to store the object state as relational data? Using nosql database, the persistence of objects can be as easy as serialization.

If you think the second one is more likely to attract you and your manager, then SQLServer might be used as nosql database for your project. SQLServer? Yes, actually any RDBMS could be used as nosql database. The problem we are trying to solve is how to persist objects to RDBMS optimizing for developer productivity and optional runtime performance and scalability.

1. OR-Mapping constraints the objects design and it is a overhead just like the memory management in C++ programming. It is become very annoying when you refactor often.
2. Query design. The complex query requires highly skilled SQL writer. Even you can get it running correctly, but might have a problem to make it performant. The ORM solution add another level of complexity to require you specify the loading strategy.
3. N+1 problem. Loading a deeply nested object graph very often leads to N+1 problem. The so called ripple loading is probably the most often seen performance problem while using ORM.

By using the idea of nosql database, we can can overcome those things and let your developers only focus on using object technology to implement the business requirement. The first thing we need to do to claim being nosql is not "not using sql" but "not using the schema".

We create a table called "EntityState", with two columns: id, xml. With the id being the id of the entity persisted, and the xml being the content of the entity state. So, the "EntityState" state is essentially a key/value pair database, but with more features actually.

But how can I get the object into xml? Thousands of ways, I have to say. The most important thing is to classify your objects into two categories: Entity or Aggreated. Being entity means it has a id, and every reference to this object should reference by id instead of serialize the content into xml. Being aggregated, means its state will be part of the xml. After we have done this, the circular reference problem might encounter is also prevented.

The loading of the object is even more likely to run into the N+1 problem if we do not take it into consideration. Say we load object with id 1, and it reference objects with id 2 and 3. And object with id 2 reference objects with id 4, 5, 6... Then SQL issued for loading a single object can be as many as one thousand. This is obviously a problem. The rough idea is using callbacks or continuation like structure. The detailed solution will be described in the later article.

The complex query you used to write is actually doing two things. It query, of course. Also, it build the model to query on the fly. If the model you persist the object happens having the column to query, the query can be as easy as one line. If the model is very far from the thing you want to query about, then you might need to join several tables and doing some SUM calculation in the SQL. If we can create index tables according to the query might have, then the problem become very trivial. The only problem is ensuring the index table get updated when the "EntityState" table getting updated.

By doing this, your SQLServer database is no long the RDBMS you and your DBA familiar with. It might sounds scary, but it might worth trying if you start to think about NHibernate/Hibernate might not be the best solution. I will write more articles on this addressing:

1. Avoiding N+1 problem
2. Avoiding N+1 problem (2)
3. Data Migration
4. Data Migration (2)
5. Flatting & Rebuilding
6. Data Migration (3)

# posted by taowen @ 22:11