Caching in on Scale and Performance – Part I

he Technologies covered: Azure SQL Database , Azure Redis Cache

Say it’s lunch time and you are standing in line for a sandwich. What’s the first thing you do? Check your wallet for cash? Right? If you don’t have cash, you go to an ATM to get it. But you most definitely don’t go to the bank branch and withdraw cash for this. And when you go to the ATM, do you withdraw just enough cash for the Sandwich. No. You take out an amount that you think you will need for a few days.

Going to your SQL Server Database (or any RDBMS) to fetch data every time your client application needs is like going to the Bank to withdraw cash every time you need cash. And while going to the bank to withdraw money might have been the modus operandi a few decades ago, it most definitely is not the right way to operate today. The same holds for Applications. A decade ago Client-Server systems operated this way. But with all the various scalable caching options available today, it makes no sense to keep going to the database for fetching every piece of data.

For over a decade, I have been helping customers scale their Client-Server or Web applications. A decade ago no one thought about Caching. Which was obvious. There weren’t too many Caching/No-SQL technologies and RDBMS was the only Database choice. But, I observe that even today, with all the No-SQL and Caching technologies available, many customers have still not given it a thought.

So, I decided to demystify the importance of caching in a 3-part blog series. This one covers the “Why Cache?”. The next will cover “What can you cache?”. And in the third one I will cover Caching Design and Architecture with Redis cache as an example.


With a host of distributed No-SQL Database technologies available, there might be other Database options that can get you similar benefits. So, this blog series will focus on Traditional Client-Server systems built on SQL Server like RDBMS technologies either On-Premise or in the Cloud.

The Build-up to a clog

This is a typical development pattern that I have observed around how companies land up in situation where their systems start stalling and are unable to scale.

Stage 1: All new application development starts from a developer writing code against a test database. Every time he hits the F5, the application zips to the database and shows the data on his application at lightning speed. And why wouldn’t it? He and a few others are the only ones connected to the database.

Stage 2: This application is then deployed to production and starts getting used by a few hundred people. Performance drop is not particularly evident.

Stage 3: As the popularity of the application grows and more and more people start using it, there is a noticeable drop in performance and that is when the Development team goes back to the whiteboard and starts to find solutions. But usually at this point, re-architecture is a huge cost.

Hence, building Caching into your architecture and design right from the beginning!

A caching example

Lets us walk through the process of building a simple web page that uses Redis Cache as opposed to a Database. I have built two web-based application applications on Azure. One connected to a Azure SQL Database and one connected to Azure Redis Cache. This article assumes that you have a basic understanding of Azure Services and .Net coding.

The database used is the AdventureWorks sample available on Azure when you set up a Azure SQL Instance.

Azure DB Create 2

Then I setup a Redis Cache service on Azure and called it AWCache

Azure Redis Cache Create 3

Once the database and cache were setup, I built a Console Application that builds the Cache from the Database.

Here are a the basic steps of constructing the Cache:

Prerequisites: NewtonSoft JSon, StackExchange Redis Library . Use Nuget to import the latest packages into Visual Studio.

Build a Join Query that gets ProductCategory X Product on Category ID:

select SalesLT.ProductCategory.ProductCategoryID,
from SalesLT.Product, SalesLT.ProductCategory 
where SalesLT.ProductCategory.ProductCategoryID = SalesLT.Product.ProductCategoryID 
order by SalesLT.ProductCategory.Name

Build a Cache Structure to load the Join query into.

//Structure to store Category X Products
 public class CategoryXProduct
    public string CategoryID;
    public string CategoryName;
    public List<Product> lstProducts;
    public CategoryXProduct()
       lstProducts = new List<Product>();

 //Structure to store Products
 public class Product
    public string ProductID;
    public string ProductName;

    public Product(string ID, string Name)
        ProductID = ID;
        ProductName = Name;

Connect to the Cache and the SQL Database

//Connect to Cache Database with a ConnectionMultiplexer Helper Class
 IDatabase cache = Helper.Connection.GetDatabase();
 var server = Helper.Connection.GetServer("");
 //Clear the Cache

//Connect to AdventureWorks Data on Azure SQL
 dbConn = new SqlConnection(",1433;Initial Catalog=AdventureWorksDB;MultipleActiveResultSets=True; Persist Security Info=False;User ID=anideswandikar;Password=@ni123@ni123;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");

//Run a loop on the DataReader and load the results of the query to the Cache.
 cxp = new CategoryXProduct();

 string categoryID = reader.GetValue(0).ToString();
 string categoryName = reader.GetValue(1).ToString();
 string productID = reader.GetValue(2).ToString();
 string productName = reader.GetValue(3).ToString();

 cxp.CategoryID = categoryID;
 cxp.CategoryName = categoryName;
 cxp.lstProducts.Add(new Product(productID, productName));
 //Remember the current CategoryID to detect a change
 prevCategoryID = categoryID;

 int i = 1;

 //Loop and watch for CategoryID Change
 while (reader.Read())
   categoryID = reader.GetValue(0).ToString();
   categoryName = reader.GetValue(1).ToString();

   //If Category is same, add product
   if (categoryID == prevCategoryID)
      productID = reader.GetValue(2).ToString();
      productName = reader.GetValue(3).ToString();
      cxp.lstProducts.Add(new Product(productID, productName));
   //Add the Object to Cache and start a new Object
     cache.StringSet(prevCategoryID, JsonConvert.SerializeObject(cxp));
     cxp = new CategoryXProduct();
     //Get te new Category name and first product

     productID = reader.GetValue(2).ToString();
     productName = reader.GetValue(3).ToString();
     cxp.CategoryID = categoryID;
     cxp.CategoryName = categoryName;
     //Add the CategoryXProduct item to Cache
     cxp.lstProducts.Add(new Product(productID, productName));
     //Remember the new CategoryID
     prevCategoryID = categoryID;


Once the cache is built, I built two very similar looking Web Applications. Once connects to the Database and one to the Cache. The Cache code looks something like this:

 string id = DLCategory.SelectedValue;
 CategoryXProduct item = 
 foreach (Product p in item.lstProducts)


And voila! We just built a page in an Application that never has to go to the database for getting Data. But, what if a new Product is added or discontinued? Or a a new Product Category gets added. That is what we will cover in the next topic. What to cache? And the various caching methodologies.




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