Json storage in Sql. A NoSql hybrid Solution

Posted: 2015-04-19 in .Net

Sometimes you just want to store stuff and not use it for traditional relation searches.

Ideally a NoSql solution would fit well in this scenario but don’t rule out Sql for a hybrid style solution.

You just have to be aware of certain limitations but otherwise the results are extremely pleasing.

The basics:

A) A column to store the data.

I chose a varchar 2000 for a rather large data model. The problem with varchar(max) is that if your total column size is over 8000 bytes you will lock the table during indexing (exclude MsSql expensive version). Table locks will cause a Sql time out exception. We index over a million rows and it takes about 6 minutes, which is too long for a customer to wait.

B) PK, Fk, and index columns

You will need to store your PK’s, you should probably also store your FK’s and anything you will search on heavily. This is the hybrid part of the solution.

To do this you will use a DTO, this is used to extract out the properties to columns. We opted to still save the original model, this means during load you don’t need to do data mapping, the negative is you will be storing duplicate data.

If you don’t like the code duplication you can add ignore attributes to the properties and match them back during load of the object. This gets messy, especially when business changes and you required a new indexed column.

C) Column compression.

We compressed, its easy to add a Sql function to uncompress and compress for Sql queries. We used the same CLR code in code and did the uncompression on the servers to move load off the sql server (better vertical scalability).

We compressed due to our requirements, with in a month we had stored 3gb in our database. Compression reduced this by 70%. The compression code is separate but easy to inject.

D) Add a new Fk or index column.

Either through code:

  1. Update the code to include the population during save.
  2. Create a unit test that loads all the items.
  3. Call the save method again.
  4. Update the database to not allow nulls

Or via Sql:

You can use a sql query using a json framework (https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/) if you are feeling tricky.

Code:

Save and load methods provided use a fake car class.
Technologies used are; Newtonsoft Json, MsSql, C#, Dapper.

Gotchas:

This hybrid solution has the same restrictions as any other Json based patterns like REST api, and NoSql.

You need to be careful when inserting, deleting, or altering class properties.

Remember the index problem, we got hit with this on production.

Case Study:

We use this solution for two microservices, most of the data isn’t important for anything other than auditing purposes. For reporting we have a separate service that extracts the needed and runs on a mirrored database, this keeps reports from blocking up our system and leaves us free to change our data models without breaking reports.

Future:

In the interest of the future, it is hoped that Microsoft will bring in native support for Json (like they have for XML).

Sadly it is currently one of the most requested features since 2011 and still hasn’t been implemented (https://connect.microsoft.com/SQLServer/feedback/details/673824/add-native-support-for-json-to-sql-server-a-la-xml-as-in-for-json-or-from-openjson)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s