The Power of Batching

Recently I coded up a very naive message handler in C# that read in received messages from a certain service in an ASP.NET MVC controller method and then inserted each message into a SQL Server table for processing. When hundreds of calls flooded my system per second, it quickly fell to it’s knees.

Why?

Inserting one object at a time to SQL server required a separate network call to the SQL Server database, and did not take advantage of any bulk insert capabilities in SQL Server. I quickly realized that I needed to do something fast as this was a production system. I decided that I would batch up all of the messages every few seconds and then bulk insert them into SQL Server.

Imagine this truck carrying one package at a time to it’s destination and returning. Not very efficient.

Since all web services can have many concurrent connections, I needed a data structure that could add and remove items in a thread safe way. The Microsoft .NET developers have done a great job with System.Concurrent.Collections. Specifically, System.Concurrent.Collections.BlockingCollection is a great way to represent a thread safe queue. You can add items to the back and take them to the front.

In my new implementation, when I get a call to my controller method, I simply insert a message into a static instance of a BlockingCollection and that is all. I then have a timer that fires every few seconds, gets the count of the blocking collection, takes that many items off and then bulk inserts the messages into SQL Server. This new implementation is orders of magnitude faster than the one at a time implementation.

In short, if you have any system that handles messages and puts them into any kind of database, be sure to do batching. Even a batching interval of 1 second will give you vast improvements in performance.