How to efficiently store Heatmap data in SQL Server column

2
votes

I am using the javascript plugin heatmap.js (http://www.patrick-wied.at/static/heatmapjs/) to track mouse movements and clicks. This plugin generates huge amounts of data 

"data":[{"x":"17","y":"-8","radius":8,"value":1},
        {"x":"23","y":"-47","radius":8,"value":1},...
       ]

approximately 10000 points /20 secs of activity.

What is the most efficient way to store this data in SQL server.

Currently i am using varchar(max) to store every 10000 points. But wanted to see if there is a more efficient way to compress and store this info.

1
c#sql-serverjsonheatmap
How are you going to use the data later on? - Raj More
Currently i read the varchar(max) back and in javascript i do the following: var dataString = JSON.parse(columndata); and then set the heatmap with this dataString value heatmap.setData(dataString); - user1071979

1 Answers

0
votes

You could create a table with a schema that matches the schema of the JSON nodes - in this case it looks like it would have columns for x, y, radius, and value. This would reduce the storage by length of the attribute headers (x, y, radius, and value) and the delimiters ({, :, ", }, and ,), and storing the values as numbers rather than strings would require less disk space.

Another approach would be to use a compression library like zlib to compress and decompress your raw data before storing it as a binary blob. Most compression algorithms would effectively eliminate the overhead of the repeated tokens in your stream (the same attribute headers).