AzureSql as Json Serializer : Blazing fast microservice

The startup site I was working, dubbed the educational network, lists courses from partners. To amplify engagement with users, we needed an elegant but simple commenting system.  Users when logged in can comment on a course they have taken and providers can reply to it – akin to airbnb or expedia site.
Before we shape this requirement into a microservice, lets see what Martin Fowler’s take on this: to quote him: One reasonable argument we’ve heard is that you shouldn’t start with a microservices architecture. Instead begin with a monolith, keep it modular, and split it into microservices once the monolith becomes a problem. (Although this advice isn’t ideal, since a good in-process interface is usually not a good service interface.) So we write this with cautious optimism. So far, we’ve seen enough about the microservice style to feel that it can be a worthwhile road to tread. We can’t say for sure where we’ll end up, but one of the challenges of software development is that you can only make decisions based on the imperfect information that you currently have to hand.)

As most startups’ backend architecture starts as a monolith API with an eye for future scalability, our startup site treads the same path but with two stern demands.

  1. design for exit, so that a future standalone microservice from monolith is a easier transition
  2. extract the max out of the given cloud resources and make this API as scalable now and then.

We’ll explore how we accomplished the above two tenets using AzureSql and ASP.NET Core tweaking ‘design & infra’ choices. We utilized a 5 eDTU 2GB Sql Server Db (costing $5/month, cheapest hosted db in azure) and a spare windows VM that can host this microservice. Backend was EF Core with hierarchical LINQ & Newtonsoft as Json Serializer. Performance was dismal and this necessitated a redesign to use AzureSql’s native JSON capability to hierarchical-ize and serialize results. proved nifty in load testing the API and triage the problem areas and achieve our goals.

Conclusion: Core 1.1 with Dapper can achieve 300+ API calls in a minute with a total throughput of 4MB data returned with median response time of 101 ms using merely one 5 eDTU SQL Server database (the very basic entry level db in azure)  hosting ~3 million comments and a million users.

This blog article can can also be used as a walk through to recreate the whole experience yourself – essentially you need a Azure Subscription and local SQL Server! It covers the use case – Disqus like Comment/Reply System, design methodology, query design, issues encountered and SQL db Json Serialization technique, API testing tool and all important load test results. The code is hosted @ github

What the business wanted? – “Comment & Reply” Requirements:

  1. Logged in users be able to comment on each course/service
  2. Course/Service Provider can reply to those comments
  3. Ability to have hierarchical comments but for now restricted to 1 level
  4. Multiple comments on a course/service by users allowed (no hierarchical – comment on comment)
  5. Provider can reply to a comment and alter it (no reply on reply)
  6. While browsing a course, able to see comments by users and replies to them by their providers if any

Entity Design

Table Utility
Users Users registered in the system
Courses Services provided by Users (registered as service providers)
Comments Comment for a course/service: Rating, Title, Remarks, CreatedOn – for which CourseId
CommentSnapshots First and Last Comment for User/Service combo

The above ERD depicts a run down version of the actual entities involved in the design, all attributes avoided for confidentiality. These attributes suffice for a base design of the problem we are discussing.

The idea to snapshot first and last comment is to provide a quick way to retrieve a comment by a user with intermediate comments are retrieved on demand – this is useful when comments per user is viewed either by an Admin or by user.  Also it’ll be useful to limit the search whether a user has really made at least a comment for a given service rather than searching the entire comment history in comments table to ascertain that. Again there could be even a better design but we started off with this which fulfills all the requirements outlined above.

We avoided having foreign keys as the system is destined to be compartmentalized and modular microservices is the final implementation where in each entity will be in its own domain and have their own services.

Infra Choice

Being a Azure shop, we decided to use a Azure SQL Server with the very basic offering: a 5 e-DTU 2GB db at USD5 per month.

App Backend
Windows 2016 VM with 2GB RAM and SSD with ASP.NET Core Web API and IIS, We started with core 1.1 and also tested the solution using Core 2.0, the latest release to compare performance.

Data Prep

To test this system realistically, we’re looking at 1 million customers, 30k services/courses with ~2.5 to 3M comments. To create the customers, restore BigTestData.bak into a local SQL database from BigTestData.rar (refer to my GitHub dataspring/Retail and look for Getting started – Environment: Windows 7 and above with SQL Server Express 2012 and above – Steps to Generate Data)

  1. Create Comments database –> run 01-DataPrep-CreateDb.sql
  2. Create Functions and Indexes –> run 02-DataPrep-CreateFnsAndIndexes.sql
  3. Create ~1M Users –> 03-DataPrep-CopyUsers.sql
  4. Create ~2M Comments & ~1M Replies –> 04-DataPrep-FillData.sql (takes a while…..)

The gist of data generation:

  1. Pick Users with user id (<50000) to be providers (aka assumed -registered as providers)
  2. Create 40000 courses with providers iterated from a specific id of users
  3. Use a random user ID (between 500K to 1M) to create 15 comments for each of the 40K course
  4. Capture 1st and last comment in to CommentsSnapShot
  5. Create Reply for each of the comment
  6. Also randomly vary the content in the Title and Remark to be realistic
  7. Ensure all 15 comments have sufficient and proper chronological order
 ASP.NET Core & EF.Core – Some Thoughts

ASP.NET Core benchmarks are astounding given there was a blog before that I read and not sure it’s ‘use case’ is relevant but the load test we’re planning to do due course (as explained below) is a practical test with pragmatic data though.  Always micro ORM like Dapper keeps beating EF Core to the core as in this blog and I wanted to try Dapper as well in the load test.

Coding the API

Fire up your VS 2017 community and look for ASP.NET Core 1.1 Web API template and create your Web API project – Core1dot1Service and save the resulting solution as CoreBenchMarks. You can copy to entire code @ github and follow along as well.

I was contemplating on the final requirement (point 6.) and started off with the EF core and LINQ but there wasn’t lot of examples to do hierarchical queries in EF as clearly and succinctly on the web.
So I headed to do on my own and created a http get method with this LINQ query:

public async Task<List<CommentBlock>> GetFromLinq(string ratingType, int courseId, int? userId = null, int skip = 0, int size = 10, int skipThread = 0, int sizeThread = 10 )
	return await
		.Where(r => r.CourseId == courseId && r.UserId == (userId ?? r.UserId) && r.CommentType == ratingType)
		 r => r.UserId,
		 u => u.UserId,
		 (r, u) => new CommentBlock
			 UserDisplayName = u.DisplayName,
			 UserRating = r.LastRating,
			 Comment = r.LastRemarks,
			 UserLastUpdate = r.LastUpdate,
			 Comments = _dbContext.Comments.Where(c => c.CourseId == r.CourseId && c.UserId == r.UserId && c.CommentType == ratingType)
									 .Select(cm => new Comment
										 CommentId = cm.CommentId,
										 Rating = cm.Rating,
										 Remarks = cm.Remarks,
										 CreatedDate = cm.CreatedDate,
										 Reply = _dbContext.Comments.Where(rp => rp.ParentId == cm.CommentId && rp.CommentType == (ratingType + "Reply"))
												 .Select(ply => new Reply
													 Remarks = ply.Remarks,
													 CreatedDate = ply.CreatedDate
									   .OrderByDescending(o => o.CreatedDate)
		 .OrderByDescending(o => o.UserLastUpdate)

Hierarchical Design:
For a given Course ID/Service ID and Rating Type (‘Course’) :

  • extract Last Comment from ‘CommentSnapShots’ table (if a user ID is provided, filter by it)
    • and then all Comments reverse chronologically from ‘Comments’ table
      • and replies for every comment if any from service providers

and return whole set as hierarchical json object. As proved and expected LINQ queries are notoriously inefficient and so happened that during the load tests, no data were returned, as we can see in the next section which covers load testing.

Load Testing : Abandon VS Load Testing Tool & Embrace Artillery

Since I had VS 2013 Ultimate, wanted to give a try to see how good the load testing can be. It’s intuitive to record if you have an GUI for your APIs or you have to manually do your GET requests and record it in IE to be captured. With Windows 10, you have Edge but VS Load Testing recording still depends on IE and hence you got to install additional stuff. There was no great way to do POST API calls easily and randomizing data inputs, reading data from text files and integrating into the test was a pain that I had to abandon the whole exercise and move to best alternative – open source – Artillery.IO fits the bill fantastically and I was able to learn the whole thing within few hours. It was such a pleasant thing to do load testing on APIs with a simple and easy to understand yaml file and NodeJS.

Ensure you have latest Node and just follow getting started with Create a solution folder under Solution called and ‘Artillery.LoadTests’ . Now there are 2 steps, generate random data to use and create load test script:

Just generate the data and copy it to folder where artillery yaml file is located

Select Top 5000
,[Skip] = [dbo].[Random_Range](0,3)
,Size = [dbo].[Random_Range](2,10)
,SkipThread = [dbo].[Random_Range_With_Default](0,1,0,8)
,SizeThread = [dbo].[Random_Range](2,10)
from [dbo].[CommentSnapShots]

If you’re hosting the .NET Core wherever, accordingly change the target.

        target: ""
        target: ""
        target: "http://localhost:43182"
  #target: ""
      - duration: 30
        arrivalCount: 10
        name: "Warm up phase"
      - duration: 60
        arrivalRate: 1
        name: "High load phase"
  processor: "./proc-functions.js"
      path: "./testData.csv"
          - "SnapShotId"
          - "CommentType"
          - "CourseId"
          - "UserId"
          - "Skip"
          - "Size"
          - "SkipThread"
          - "SizeThread"
      #order: "sequence"
# scenario definitions
  - name: "Stress Test JsonFromLinq API - where JSON is returned from LINQ"
    - get:
          #----------- just for a given course ID -----------------------------
          url: "/api/comments/method/jsonfromlinq?ratingType={{CommentType}}&courseId={{CourseId}}&skip={{Skip}}&size={{Size}}&skipThread={{SkipThread}}&sizeThread={{SizeThread}}"
          afterResponse: "logResponse"
          #think: 5
    - log: "jsonfromLinq api call : ratingType={{CommentType}}, courseId={{CourseId}}, skip={{Skip}}, size={{Size}}, skipThread={{SkipThread}}, sizeThread={{SizeThread}}"

We’re using a simple loading pattern to start with:

  • A phase which generates a fixed count of new arrivals over a period of time : 10 users in 30 seconds
  • A phase with a duration and a constant arrival rate of a number of new virtual users per second : 1 user / second for 60 seconds
  • In total : 70 requests in 1.5 minute or 90 seconds

As you can see the below performance snapshot, EF Core LINQ is very performant on the Laptop (perhaps spec is good) but when ported to Azure VM with 5 DTU Auzre SQL, simply doesn’t work!
To mitigate this performance issue, we have to redesign the whole data access and perhaps relinquish the abstraction which LINQ provides and need to go bare metal – to database level and unravel how far we can stress the system to be performant. Options available to accomplish this are both from code and infra:

  1. Scale Azure SQL to 30 or more DTUs
  2. Use a 3rd party Json Serilaizer with existing LINQ query
  3. Partition LINQ query into individual queries in option 2
  4. Abandon LINQ and go bare metal on SQL : Stored Proc and Json Serializatioin in SQL Server

We embarked on option 4 which provides cost effective solution and can be quick win if we need to scale within budget.

LINQ query was redesigned as stored proc with TSQL’s powerful JSON capability to hierarchical-ize and serialize the result and return json text.

Proc Design – Version 1

	SELECT u.displayName
		SELECT t.commentId
				SELECT r.commentId
					,(Select top 1 displayName from Users usr where usr.UserId = r.UserId) as displayName
				FROM Comments AS r
				WHERE r.CourseId = t.CourseId
					--AND r.UserId = t.UserId
					AND r.CommentType = t.CommentType + 'Reply'
					AND r.ParentId = t.CommentId
				) AS reply
		FROM Comments AS t
		WHERE t.CourseId = c.CourseId
			AND t.UserId = c.UserId
			AND t.CommentType = c.CommentType
			--AND t.ParentId = 0
		ORDER BY t.CreatedDate DESC
		OFFSET @SkipThread ROWS
		) AS thread
	FROM CommentSnapShots AS c
	INNER JOIN Users AS u ON c.UserId = u.UserId
	WHERE Isnull(c.CourseId, '') = Isnull(COALESCE(@CourseId, c.CourseId), '')
		AND c.UserId = COALESCE(@UserId, c.UserId)
		AND c.CommentType = @RatingType
	ORDER BY c.LastUpdate DESC

SqlServer as Json Serializer is achieved using the FOR JSON construct and iterating the design through its options makes the result nearly similar to what you get from LINQ based hierarchial results serialized by Newtonsoft serializer.

Issues in Version 1 and Mitigation:

  1. TSQL has a nice feature called COALESCE function which comes handy if any of the filter fields are null or not provided, we can easily manage the WHERE clause but it hurts performance hugely and either you have to use a dynamic SQL or altogether remove COALESCE function in the WHERE clause.
  2. Key Lookup is a costly affair in the SQL execution which is evident from peeking into the execution plan, hence you need to have a corresponding non-clustered index fields matching the query WHERE clause fields and Include columns matching the selected fields…great example here.
  3. Yet another aspect is to accept dirty reads – which I’ve not tried here but worth if a slight marginal error is acceptable. You can use NOLOCK which his functionally equivalent to an isolation level of READ UNCOMMITTED. If you plan to use NOLOCK on all tables in a complex query, then using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is easier, because you don’t have to apply the hint to every table.

Version 2 removed coalesce and created a handful of non-clustered indexes with INCLUDE columns to remove all key lookups and literally leap-frog query performance including selected columns in the indexes themselves. Final stored proc code is here and the corresponding Web API is using json string pass-through to send teh results back from db without attempting any .NET Core level serialization.Check out the code below.

public async Task<ContentResult> GetFromDapper(string ratingType, int? courseId, int? userId = null, int skip = 0, int size = 10, int skipThread = 0, int sizeThread = 10)

	using (var connection = new SqlConnection(ConnectionConfig.DefaultConnection))

		DynamicParameters dp = new DynamicParameters();

		dp.Add("@RatingType", ratingType ?? (object)DBNull.Value, DbType.String);
		dp.Add("@CourseId", courseId ?? (object)DBNull.Value, DbType.Int32);
		dp.Add("@UserId", userId ?? (object)DBNull.Value, DbType.Int32);
		dp.Add("@Skip", skip, DbType.Int32);
		dp.Add("@Size", size > 20 ? 20 : size, DbType.Int32);
		dp.Add("@SkipThread", skipThread, DbType.Int32);
		dp.Add("@SizeThread", sizeThread > 20 ? 20 : sizeThread, DbType.Int32); ;

		var results = await connection.QueryAsync<string>("GetComments", dp, commandType: CommandType.StoredProcedure);

		List<string> jsonResults = new List<string>();

		return Content(string.Join("", results.ToArray()), new MediaTypeHeaderValue("application/json"));


Load Test Results
Armed with the optimized stored proc that also does JSON serialization, next is to really test this to ascertain how much the minimum infra can withstand when goes live. It seems the App VM wasn’t the bottleneck but the DB in the end. A simple yet comprehensive load testing regime was used here to compare and contrast and conclude.

Following were the versions tried:

  1. API as such with LINQ query – with ASP.NET Core 1.1 & EF Core 1.1
  2. API with optimized Stored Proc (that hierachial and serilizaes results within) – with ASP.NET Core 1.1 & EF Core 1.1
  3. API with optimized Stored Proc (that hierarchical-izes and serializes results within) – with ASP.NET Core 1.1 & Dapper (the best ORM out there)
  4. Point 2 & 3 – with ASP.NET Core 2.0 and EF Core 2.0

Load Pattern: Ramp-up with 10 users/calls in 30 sec and add 1 user/call every sec for next 60 seconds

Results are here:

Pattern with Random Data: Ramp-up with 10 users/calls in 30 sec and add 5 user/call every sec for next 60 seconds and the results, as you can see data throughput more or less same :

The conclusion is clear, winner is Core 1.1 with Dapper and can achieve 300+ API calls in a minute with a total throughput of 4MB data returned with median response time of 101 ms using merely 5 eDTU SQL Server database hosting ~3 million comments and a million users.

Test results are available @ Github for console outputs and json results during artillery load testing.



2 thoughts on “AzureSql as Json Serializer : Blazing fast microservice

  1. You probably want to use .AsNoTracking() for EF and your LINQ query doesn’t look ideal. Why are you joining manually rather than using relationships?

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s