Tenzin kabsang

Whenever I approach a programming task my model is "first make it work", "then make it work right", "then make it work fast". Now I am not suggesting that during the first and second phase of the process that you completely ignore all performance related issues, such as looping through millions of strings or worse making a database call within a foreach loop, just use common sense and follow best software practices and give each line of code you write – the respect it deserves.

Caching is one of those mechanisms that I like to focus on at the very end, because if you’ve architected your system properly then you should be able to plug it in/out without affecting any of your other layers or components. The .NET framework supports two types of caching:

– OutputCache: the entire rendered html page is cached on the server during the first request, after that all subsequent requests simply gets the cached page for however long you have set the duration for.  There are different attributes that you can set, for instance “vary by param” “location” and bunch of other useful settings that makes it very flexible.

– Application Data Cache: You cache your application data to avoid making expensive database calls. A common example would be for an e-commerce site to cache all products. In this case products is a perfect candidate, since it doesn’t change that often and usually products belongs to some sort of a category hierarchy and you can cache all that related data for quick and easy access.

Putting x in cache and retrieving x from cache is the easy part, but what happens if the actual row in the database that x represents changes. Most of the times you can get away with it by relying on one of your expiration timers because it’s not that big of a deal to have out of sync data on your page for some time. However, on occasions where you do need consistent data on your page all the time, you can rely on one of the cache dependency mechanisms provided to you by the .NET framework. If your cached data depends on a file (rarely) then you can set cache dependency on that file so when any changes to the file happens your cache is updated accordingly. Most of the times however, your cached data depends on data from your database, in that case you can implement SqlCacheDependency. There are two ways of doing this:

1. Polling (older version):

a. Limited to data changes at the table level

b. An asp.net process polls your database at specified interval and invokes the necessary stored procedures (running the aspnet_regsql.exe tool installs these stored procedures).

c. This can be used with SQL Server Express version as I will demonstrate later.

2. Notification:

a. Can apply dependency on a SQL command (queries and stored proc) rather than only at table level

b. SQL Server sends notifications to subscribed application servers whenever the result set for the specified command changes.

c. Not available for SQL Server Express versions

Lets implement SqlCacheDependency using polling.  First open up visual studio command prompt in admin mode and run the following command to install the necessary stored proc on your database.  Here is the reference for all the command flags Aspnet_regsql.

Basically, I give the exe my database name along with proper credentials and it does the rest.  And here are the procs that it installed on my Shop database.

Once you are done with that, run the following command to tell which tables you want the SqlCacheDependency to monitor.  In my case there is just one “Products” and it creates a new table in your database that keeps a record of it.

This is all you need at the database end, so now lets jump in visual studio and wire it all up. 

Add the following section in your web.config, ensure that the connectionStringName attribute matches your actual connection string name defined in the <connectionStrings> section.  I have set mine to poll every 15 minutes for this demo.

Now when you insert data in your cache, specify that you want to use SqlCacheDependency by instantiating a new instance of it and passing it as one of the parameters for the Cache.Insert method.  In an actual application you probably should enable AbsoluteExpiration as well as NoSlidingExpiration by setting it to the appropriate values.

So, after these 4 steps we have a fully configured Caching mechanism (at least for the Products table) that uses SqlCacheDependency via polling to determine whether any products data has changed, and if so invalidates it and evicts it from the cache on our behalf – not too bad.

References: ASP.NET Caching Overview, Sql Cache Depedency, Improving Performance with Output Caching, Caching Architecture Guide, Caching