Caching in .NET and SQL Server Notifications

This article provides an example of caching techniques in ASP.NET code project. Technologies shown here are C#\.NET and MS SQL Server. In the example, I am using ASP.NET MVC but the same approach can be used in any C# solution.

When developing Web pages, performance always becomes an issue. Interaction with the database can significantly reduce page loading speed. This is especially true for unchanging or infrequently changing data (aka dictionaries). In order to speed up your web or cloud platform, you can use caching as one of the best practices for optimizing performance. Using capabilities of .NET and SQL Server I will show you how to implement cache without a lot of changes in the existing code.

In this article, firstly we will explore how ASP.NET caching works in practice, its pros and cons, and how to make the right decision about cache selection. Then I will show you an ASP.NET cache example in the form a simple demo project for manual method results caching and how to improve it with attribute. In the last section, I will cover the ability of SQL Server to notify about data changes and how we can use it for cache updating.

Written by:
Maria Kontorovskaya,
Software Designer of Web Development Team

Demo solution

In order to demonstrate how to use caching on a practical example, I am using a simple ASP.NET MVC solution for managing orders in a restaurant. It consists of the following products:

And their categories:

And the main task is to optimize time it takes to output all of the categories and to create a new product, because they are rarely changed and  frequently used.

Caching basics

How the caching works

First, let us explore on a basic level what is cache in ASP.NET. The concept of caching is straightforward and the following logic can be applied to any type of caching, such as HTML5 application caching and explicit caching in Java. When accessing any resource, there is a cost involved in fetching that resource from its owner.

If certain resource needs to be used repeatedly, it is more efficient to keep a copy somewhere local where it can be fetched more cheaply rather then returning to the owner each time. So, instead of executing a database query every time the result set is needed, results can be stored in memory the first time they are received, and fetched from memory when necessary. In ASP.NET we can do page caching if we need to store the whole page, or perform fragment caching, if only a part of the page needs to be stored.

Pros and cons

Caching is all about performance, using the right caching techniques can substantially improve the performance of your solution. You will see improvement in the response time of your website or your API along with more effective utilization of your infrastructure. When server is able to it caches the result of an SQL query and avoids running that query again, thus, not only does service layer save the cost of calling the database, but the database also saves the cost of executing the query. And if the database is used as efficiently as it can be, then it can support greater load, and as solution gets more popular, we push toward the point where we need to scale the hardware. The same is true of computational resources in your service and presentation layers.

The downside is that my local copy of the resource is a snapshot at a point in time. The longer I make use of that copy the better value I get for the original cost affecting it, but the greater the risk of my local copy becoming outdated.

If the database is updated, the copy stored in memory will be stale and it will only become apparent at the next database query.

The complexity of .NET caching lies in getting the balance right between reusing cached copy as much as possible, with the risk that it becomes outdated, and the cost of requesting up-to-date copy from the owner, or checking if the cached copy is still up-to-date.

Decision matrix for cache selection

In order to cache object in ASP.NET effectively, we need to make sure that we are using the right type of cache. There is a set of criteria for choosing the most appropriate cache for an item:

• Cost of fetching from the owner. It is more beneficial to fetch an expensive to access item from cache.
• Breadth, or how widely object is reused throughout the solution. Return on investment for the original fetch will be much better for frequently used item.
• Longevity, or how long the data stays fresh. The longer we can reuse an item, the more benefit we get from the initial fetch.
• Size of the cached object.

The criteria are not a hard set of rules, but they are good guidelines to make sure we are thinking about the best way to store ASP.NET cache in order to reuse cached item in a most efficient way.

If I have a remote cache, which has a high capacity, but is relatively expensive to access, I would not want to fill it with objects that are cheap to fetch and have limited breadth and longevity. It would be more efficient to use a local cache there.

Similarly if I have a local cache with a small capacity I would not want to fill it with large objects that are expensive to fetch and have wider breadth and longevity. Those objects would get better reuse from a remote cache.

We can get a good feel for the decision matrix by scoring each attribute on a scale of 1 to 10 and drawing it up on a radar chart for each item. Let us do that with the demo categories:

That gives us a very distinctive shape, heavy in the bottom right corner, which is telling us the object is small and cheap to get hold of, which may challenge the need to cache it. But, it is also very long lived and widely used, so you expect good gains from caching it.

It is also worth noting, that unlike in JavaScript, there is no simple way to implement client side caching in ASP.NET using C#. It is often best to store data on a server side dataset.

How to cache

Method results caching

Basically, I have a method in the domain model for getting the list of all the categories from the database. Examples shown below are created with ASP.NET 4.5 and will not work “as is” for 3.5 and 4.0 versions. I am using a simplified code style (without full separation of data access) to show described functionality in one place:

public class CategoryDomainModel
    {
        private ProductsContext db = new ProductsContext();

        public IEnumerable<Category> GetAllCategoriesNoCache()
        {
            return GetAllCategories().ToList();
        }

        protected IQueryable<Category> GetAllCategories()
        {
            return db.Categories.AsQueryable();
        }
    }

So let us stop calling database every time, and cache the response from the initial call.

For this demo I’m using good implementation of different caches for .NET https://github.com/sixeyed/caching. The cache class in this library lets us use different caches in the same way.

Explicit caching

Let’s start by manually caching the results from method, to get a feel for the basic pattern of caching. In this example, I will use the ICache.Get method that encapsulates cache.insert. To do that, before calling database, we should check the cache to see if response is already there. When executing get on the cache, I need to assign a unique key. So I will use the name of the method to start with. So if the list of categories is null, I let the code run as before. But once I have the list of categories from the database, I will enter it into the cache.

public IEnumerable<Category> GetAllCategoriesManualCache()
    {
        var categories = Cache.Memory.Get<List<Category>>("GetAllCategories");
        if (categories == null)
        {
            categories = GetAllCategories().ToList();
            Cache.Memory.Set("GetAllCategories", categories);
        }
        return categories;
    }

This is a base pattern for any caching in ASP.NET:

1. I get from the cache.
2. I check if there’s a hit.
3. If there is, return it.
4. If not, go through and execute the method.
5. Store results in the cache.
6. And then return.

AOP style

For the manual caching, we have to take care of many details ourselves. We have to work out a unique cache key, specify the type of cache we want to use and duration for which an item will live in the cache. Now if I want to use a different type of cache, or if I need to change the lifespan of an object, then I will have to make a code change and redeploy.

With the aspect oriented programming approach, we are going to pull out our manual implementation and replace it with a completely generic technique for method level caching. AOP allows us to put hooks into our code to implement cross cutting concerns, like caching. When we access objects through the AOP framework, methods that are decorated with hooks get intercepted at runtime. The interceptor can run before, after, or instead of the method.

So it is a natural fit for abstracting our caching pattern. The handler, which incepts the cache hook, runs before the method is called and checks the cache. If the result is in the cache, it bypasses the underlying method and returns the cached result to the caller. If there’s no cache hit, the interceptor lets the method run and then caches the result, before returning it to the caller.

In my demo I implement it with the same library like this:

[Cache(CacheType.Memory)]
    public virtual IEnumerable<Category> GetAllCategoriesAOPCache()
    {
        return GetAllCategories().ToList();
    }

How to update cached data

SQL server query notifications

Built upon the Service Broker infrastructure, query notifications allow applications to be notified when data has changed. This feature is particularly useful for applications that provide a cache of information from a database, such as Web applications, and need to be notified when the source data is changed.

This technique is suitable for caching rarely changing data (aka “dictionaries”) and is as follows:

1. Form a simple SQL database query (SELECT) and bind it to the SQL Server notifications system (through the event). Such linking works only for very simple queries and has a number of limitations (https://msdn.microsoft.com/library/ms181122.aspx).
2. We execute a query and cache obtained data in any convenient location for us. In this case, the data duration should not be limited, because SQL Server is responsible for notification about data aging.
3. We continue to work with the cache until the event is triggered (SQL Server notification).
4. Upon receiving notification from SQL Server, we remove the cached data and start again from step 1, including re-tying to a SQL Server notifications system.

To enable SqlDependency in the database you need to execute the following command:

alter database [Your database name here] set enable_broker with rollback immediate

You can check created subscription in the following table:

select * from sys.dm_qn_subscriptions

Note that subscription disappears after the first data change.

Then make “sa” dbo (necessary for notice changes in data)

alter authorization on database :: [Your database name here] to [sa]

If you have problems with sending, notification error information can be viewed in the following table:

select * from sys.transmission_queue

How to implement cached data updates with ADO.NET

The basic implementation in .NET is:

void Initialization()
{
    // Create a dependency connection.
    SqlDependency.Start(connectionString);
}

void SomeMethod()
{
    // Assume connection is an open SqlConnection.

    // Create a new SqlCommand object.
    using (SqlCommand command = new SqlCommand(
        "SELECT CategoryId, Price, Name, TipPercent FROM dbo.Products", 
        connection))
    {
        // Create a dependency and associate it with the SqlCommand.
        SqlDependency dependency = new SqlDependency(command);

        // Subscribe to the SqlDependency event.
        dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

        // Execute the command.
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Process the DataReader.
        }
    }
}

// Handler method
void OnDependencyChange(object sender, SqlNotificationEventArgs e )
{
  // Handle the event (for example, invalidate this cache entry).
}

void Termination()
{
    // Release the dependency.
    SqlDependency.Stop(connectionString);
}

As you can see, when working with SQL Server, notifications are sent through SqlDependency class and include the following steps:

1. SqlDependency.Start and SqlDependency.Stop – starts and stops the listener for receiving dependency change notifications (query result). These actions should be executed once in the application, for example, at the start and when closing the application.
2. new SqlDependency (command) – create a new object dependencies with reference to our request.
3. dependency.OnChange – subscribe to an event to find out when the data in the database has changed.
4. Perform query – only then SQL Server will create a subscription to change.

How to implement cached data updates with Entity Framework and LINQ

The following solution is a convenient point to start for all your LINQ needs:

This library provides AsCached extension method for IQueryable interface and can be used as follows:

public IEnumerable<Category> GetAllUsersSQLDependencyCache()
    {
        return GetAllCategories().AsCached(“Categories”).ToList();
    }

Among the shortcomings is the need to manually set the key for the cache (“Categories”) – this problem is easily solved by creating automatic key:

public static IEnumerable<T> AsCached<T>(this IQueryable<T> query)
    {
        return query.AsCached(query.ToString().GetHashCode().ToString(), null);
    }

Conclusion

In this article, I summarized basic principles of caching, and provided a simple tutorial with a practical example of how to cache an object in ASP.NET.

To conclude, let us summarize the article as the set of simple guidelines.

Firstly, to cache items effectively, we need to understand how they are used. We look on a decision matrix, where you take into account the cost of fetching an object, the breadth of use it has, its longevity before it gets stale, and the size of an object. I don’t think you need to put together a radar diagram for every item you want to cache, but knowing the profile of your cache items lets you decide the best data caching strategy.

We need a configurable framework for caching that lets you select items to cache and switch items between caches easily.  We provided examples using an AOP framework, which you can use in your own solutions. With caching inside the solution, you control the cache doors, so you can afford to be aggressive and aim to cache as much as possible.

The last question that needs to be solved is a cache refreshing. For the rarely changing data I used built-in SQL Server notification.

Download sample project.

Read next:

SaaS Project Development Lifecycle

SQL Server Query Optimization