Building high performance, high volume, IoT enabled web app using your existing infrastructure with .NET Core, SQL Server and Redis Cache

Posted by Keshavraja Komarshetty on Jul 26, 2018 11:01:52 PM
Find me on:

In one of our recent projects we worked on developing a modern reactive web app, where we had to present IoT device data aggregated and updated in real time. We faced several challenge involving large volume of data being generated from devices and mandate from the client to use their existing infrastructure. Let’s understand the use case first.

We get continuous feed from IoT device, every second a device sends data to the Server, We have about 200 such devices sending data to the server every second. Here is how we get the data from Device.






2018-07-01 15:30:01




We had a need to aggregate the data at minute interval and report it to the users via a live dashboard page. The graphs had to auto update with live data in real-time. Multiple views presented today’s data, along with historical data for weekly, monthly and custom data ranges with multiple Y axis variables, something similar to the graph below



Our client had prior investments in SQL Server and had mandated that use the existing database and infrastructure Here’s the stack at the end of development cycle:

  • React using Material-UI using MvvM - UI
  • .NET Core 2 - Web API
  • SQL Server 2012 - Database
  • Signal R - Web Sockets for realtime connectivity
  • Redis Cache - 

We selected open source MvvM  framework as it baked in reactive backend using SignalR and for two way data binding. The UI widgets were updated in real-time with aggregated and cached data.

During the development phase we tested with a single IoT device and data volume was manageable using stored procedures and queries, However we later realized the volume of data would reach around 6+ billion rows in the primary table every year (200 device data per sec x 86400 seconds x 365 days)

Initial Solution

We went the traditional approach of using LINQ and Stored procedure to fetch data from DB. Linq aggregated the data at minute level. The responses seemed fine when we tested with 1 month of data and a single IoT device.

We used SignalR to push the data from back-end to the UI at specified intervals, the initial responses with our testing data set was quick, but as the data kept growing we started facing latency issues. We tried indexing and DB tuning but it didn't help. We had to address the issues head on so we ventured on step by step optimization journey.

Step by Step Optimization

We realized that we wanted the reporting on the frontend at minute level, seconds level reporting was not planned in near future, so the first step we took was aggregated the data at minute level for each category and device into a new table, this brought down the number of rows to 92 million per year from 6+ billion.

This made some difference in response times, but it wasn’t enough, so we decided to use a Caching engine to store the aggregated data. We narrowed on Redis cache. Since there was no change in data till yesterday, decided to create 2 Redis viz DataTillYesterday and TodaysData, and scheduled 2 events to rebuild the cache:

  • DataTillYesterday cache is being built once every night with data for last year
  • TodaysData cache is being rebuilt with last minute data every few seconds with updated data from devices.

Now there is no need to go to SQL Server and fetch new data or old data. The above 2 Caches have all the data that is required to build Today, Weekly, Monthly and Custom range graphs on the frontend. Response from Redis Cache is in milliseconds and there is no Lag and data is being pushed in realtime to React.

Here’s the code to build DataTillYesterday cache in Redis

IList<GetRotationsDataView> RotationssData;
using (DataContext db = new DataContext())
       DateTime firstDateOfYear = new DateTime(2017, 01, 01, 0, 0, 0);
       DateTime lastNightDate = new DateTime(2017, 10, 21, 23, 59, 59);
       RotationssData = db.RotationsData.FromSql("sp_GetRotationsData @p0, @p1 ", firstDateOfYear, lastNightDate).ToList();
IDatabase cache = RedisConnectorHelper.Connection.GetDatabase();
await cache.KeyDeleteAsync("DataTillYesterday");   //Delete cache
List<RedisValue> lst = new List<RedisValue>();
foreach (GetRotationsDataView o in RotationssData)
await RedisConnectorHelper.RedisCache.ListLeftPushAsync("DataTillYesterday", lst.ToArray());

Code to fetch data from Redis

var redisData = RedisConnectorHelper.RedisCache.ListRange("DataTillYesterday");
if (redisData.Length > 0)
   List<GetRotationsDataView> objs = Array.ConvertAll(redisData, value => JsonConvert.DeserializeObject<GetRotationsDataView>(value)).ToList();

Know more about our experience