Database cache dependency must’ve been among the most anticipated additions in ASP.NET 2.0. Caching, in general, calls for some tough decisions. How long to cache data for? Can we afford to present stale data to the user by way of caching? What kind of data are we dealing with and can we even afford it to be stale? How do we invalidate the cache once the data source changes? and so on.
Sql cache dependency allows to simplify this decision-making process. In a nutshell, with the SqlCacheDependency class you can cache some data and make its expiration (invalidation) dependent on changes (updates, deletes, inserts) in a particular table.
Last year I wrote something similar for 1.x (see Lightweight Database Cache Dependencies part 1 and part 2). I know Jon Galloway took a stab at it too. I’ve seen many other people devise something along these lines, which clearly shows this feature was begging to be introduced.
Configuring SQL Cache Dependency (Sql Server 7 and 2000)
There’s some manual configuration of web.config required to specify which connection string to use, how often to poll the database for changes, etc. You can set up more than one database connection and poll several databases—all from the same web application!
You also need to “enable a database for SQL cache dependency” by running Aspnet_regsql.exe. Pay attention to the -ed (enable database), -t (table) and -et (enable table) switches.
For example, to enable a cache dependency on a particular database, run this command:
aspnet_regsql.exe -S server -U user -P password -d database -ed
This creates a new table, AspNet_SqlCacheTablesForChangeNotification, in the designated database. Next, several AspNet_SqlCacheXxxx stored procs are created in the same database.
The way SqlCacheDependency works is it ties a cached item to a table. Therefore you need to decide which tables to poll for changes in the first place. You “enlist” tables by running aspnet_regsql.exe again:
aspnet_regsql.exe -S server -U user - P password -d database -t table -et
Once you run this command, the specified table name is added to AspNet_SqlCacheTablesForChangeNotification. Also, a trigger is set up on your table. Any time an update, delete or insert takes place, a “change counter” is incremented in AspNet_SqlCacheTablesForChangeNotification, and any data, cached with a dependency on this table, is flushed within the polling interval.
Getting Permissions Right
Rigt off the bat, I was greeted with a stored proc permissioning error. Whenever we create a database, we assign it its own login with the bare minimum of permissions (read more)—db_datareader and db_datawriter.
If any of the AspNet_SqlCacheXxxx stored procs gives you grief, try this:
GRANT EXECUTE ON dbo.AspNet_SqlCacheXxxx TO public
Multiple Table Dependencies
It’s not immediately obvious how to tie cached data to multiple tables. I doubt there are too many applications where data is fetched strictly via single-table SELECT statements, so a more realistic scenario is to have data whose “freshness” depends on changes in several tables.
Luckily, there’s another new class, AggregateCacheDependency, which comes to the rescue:
The AggregateCacheDependency class monitors a collection of dependency objects so that when any of them change, the cached item is automatically removed. The objects in the array can be CacheDependency objects, SqlCacheDependency objects, custom objects derived from CacheDependency, or any combination of these.
The AggregateCacheDependency class differs from the CacheDependency class in that it allows you to associate multiple dependencies of different types with a single cached item.
Consider this C# snippet:
SqlCacheDependency
dep1 = new SqlCacheDependency ("MyDb", "Users"),
dep2 = new SqlCacheDependency ("MyDb", "UserRoles"),
dep3 = new SqlCacheDependency ("MyDb", "Policies");
AggregateCacheDependency aggDep = new AggregateCacheDependency ();
aggDep.Add (dep1, dep2, dep3);
Cache.Insert ("SomeKey", someData, aggDep);
There are dependencies on tables Users,
UserRoles and Policies, all tied together by an aggregate cache dependency. Anything changes in one of these tables—the cached item is invalidated, and it’s time to bring in and cache fresh data.
Conclusion
As with any caching approach, you win some and you lose some. The jury is still out on performance of Sql cache dependency. I don’t know if having a trigger on every monitored table is a good thing or a bad thing, but change detection boils down to a simple database read, and it doesn’t get much simpler than that.
The discussion above is limited to Sql Server 7 and 2000, which have only the polling mode of change detection available. I haven’t had a chance to play with the Sql Server 2005 notification-based cache invalidation, but it looks very promising.
