Editorials

Two Stage Persistence

In dealing with high performance systems there is another method of addressing the normalization of data when it is persisted to an SQL relational database with appropriate normalization. This was alluded to by Eilenblogger in his comments yesterday. The method uses two stages of persistence.

First, the data is persisted to a temporary data store with extremely fast performance. There are a number of different implementations. It could be a NoSql data store using a Key Value pair or something of that nature. It could be a message queue. It could even be de-normalized staging tables in an SQL database. In another stage the data is then normalized and brought into your SQL Server database at a later time.

We already do this kind of thing naturally today by having reporting databases and transactional databases containing the same data. The difference in this situation is that we are having separate data stores for extremely fast storage compared to long term traditional storage designed for other purposes.

You are asking yourself why would you even consider such a design in the first place? It is a lot of extra work. Why not simply save to the relational database in the first place. The answer is based on capacity and cost. You can simply save directly to a relational database. As your volume grows you will require bigger and more expensive hardware. That is really the only issue.

As consumers we have grown to expect shorter and shorter response times from our computer systems. We like the kind of performance you get from systems like Google. There comes a point where even the largest mainframe does not have enough power to handle the load. Even IBM Watson consists of multiple machines working on a single problem.

So, in order to keep you reasonably priced SQL Server working with higher and higher transactional volumes, you need to learn patterns to optimize the load it handles, and/or to spread the load out to times that have a lower transactional load. Sometimes there are no low volume periods and you need to become even more creative. We’ll take a look into some of those scenarios next week.

Cheers,

Ben