Sql Dependency with MVC Output Cache

MVC supports Output caching pretty much out of the box, all that is required is the addition of the OutputCache attribute on a Controller or Route and the entire page output will be cached.

There are a number of possible options such as VaryByParam and Cache Duration which allows a lot of customization. This is very useful if you want to speed up a page where perhaps the output is determined by some db records which do not change often. There is also an option to invalidate the cache based on a change to a specific table via the SqlCacheDependency.

Since .NET v2 there has been a SqlCacheDependency implementation which can be setup fairly quickly for an existing site. This method relies on database triggers and polling from the website to determine when cache should be invalidated. Obviously this is not a very efficient method since the website still has to poll the db frequently it also means that the cache may not be updated as quickly as a user would expect in all cases.

A preferable method is to use the service broker in sql so that the database will instead notify the application of updates, I might cover this in another post later.    

Method 1 Setup using aspnet_regsql

The first step is to configure the database for polling. There are a number of stored procedures that will be created so ensure the db user has EXECUTE permissions

GRANT EXECUTE TO "database_user"

Next the aspnet_regsql.exe tool needs to be run to setup the database and tables from the command line. You may need to open a command prompt as admin and then change directory depending on your Server installation normally the tool will be under C:\Windows\Microsoft.NET\Framework\v2.0.50727.

First run this command to enable sql cache dependency for the database

aspnet_regsql.exe -S sql_server_name -E -d database_name -ed

This will create a new table AspNet_SqlCacheTablesForChangeNotification and a set of stored procedures for the database. Each individual table needs to be registered as a dependency by running the following command.

aspnet_regsql.exe -S sql_server_name -E -d database_name -t table_name -et

This will create new entries in the new table, and a trigger on the table specified, this is only required for tables that will be monitored for change notifications.

Method 2 Scripts

It is possible to perform the aspnet_regsql tools job with custom scripts if this is preferable, here are the minimal steps needed to get things running:

Create the change notification table

CREATE TABLE [dbo].[AspNet_SqlCacheTablesForChangeNotification] (
    [tableName] [nvarchar](450) NOT NULL, 
    [notificationCreated] [datetime] NOT NULL, 
    [changeId] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
( 
    [tableName] ASC
) WITH ( PAD_INDEX = OFF, 
              STATISTICS_NORECOMPUTE = OFF, 
              IGNORE_DUP_KEY = OFF, 
              ALLOW_ROW_LOCKS = ON, 
              ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AspNet_SqlCacheTablesForChangeNotification] ADD DEFAULT (getdate()) FOR [notificationCreated]
GO

ALTER TABLE [dbo].[AspNet_SqlCacheTablesForChangeNotification] ADD DEFAULT ((0)) FOR [changeId]
GO

Create the minimal set of stored procedures

CREATE PROCEDURE [dbo].[AspNet_SqlCachePollingStoredProcedure] 
AS SELECT tableName, changeId FROM dbo.AspNet_SqlCacheTablesForChangeNotification GO CREATE PROCEDURE [dbo].[AspNet_SqlCacheQueryRegisteredTablesStoredProcedure]
AS
SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification GO CREATE PROCEDURE [dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure] @tableName NVARCHAR(450)
AS BEGIN UPDATE dbo.AspNet_SqlCacheTablesForChangeNotification WITH (ROWLOCK) SET changeId = changeId + 1 WHERE tableName = @tableName END GO

For each table that should notify of changes add the entry and trigger

INSERT INTO [dbo].[AspNet_SqlCacheTablesForChangeNotification] ([tableName]) VALUES ('table_name')

CREATE TRIGGER [dbo].[{table_name}_AspNet_SqlCacheNotification_Trigger] ON [dbo].[table_name] FOR INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'table_name' END

Configure the MVC application for Output Cache

Once the database has been configured the application needs to be told what to monitor. In the MVC web application add the following to the web.config

<system.web>
    <caching>
        <sqlCacheDependency enabled="true">
            <databases>
            <add name="SqlCacheDependencyName" connectionStringName="ConnectionStringName" pollTime="500"/> 
            </databases> 
        </sqlCacheDependency>
    </caching>
</system.web>

Note the connection string needs to be a standard sql connection string i.e not entity framework for this to work.

Finally add the OutputCache attribute to a controller or route

[OutputCache(SqlDependency = "SqlCacheDependencyName:table_name")]

The page will be cached and only refreshed if there is a change to the specified table. It is also possible to specify multiple tables as follows.

[OutputCache(SqlDependency = "SqlCacheDependencyName:table_name;SqlCacheDependencyName:table_name2;")]

Essentially all that happens is Sql server will update the  AspNet_SqlCacheTablesForChangeNotification table when an update is made via triggers.

The Output Cache will poll this table to determine if the cache should be invalidated. The poll time can be modified depending on how quickly you want the site to react to db changes.

This is a quick way to use SqlDependency but it is limited to entire table updates for more granular requirements it may not be best suited.

One added benefit is any post db processing that you may be doing is also cached making the page load nice and fast.