ASP.NET provides several convenient ways to cache data. You can either cache entire pages or only their parts via OutputCache directive, or you may choose Cache API to do the same. With Cache API you get a very cool feature—cache dependencies. For example, you can cache contents of a file and set a dependency on this file. Should the file change, the cached data will be flushed. This is an excellent way to make sure that what you serve from cache is always fresh and up to date.
One type of cache dependency that has been missing in ASP.NET since day one is database cache dependencies. What is this beast?
Suppose you’re building an online bookstore. You have a web page that displays all books of some category, let’s call it "Automotive". You read all books in the "Automotive" category and bind them to a nice datagrid, datalist or repeater. You do this every time the page is requested.
To improve performance (something you should always strive for!) you may choose to cache the category list. This will work much faster because cached data lives in memory.
Now, imagine you’ve added a couple new books to "Automotive". Your changes won’t be reflected because cache is blind to database changes. You may choose to cache the book list in a way that it gets flushed every 6 hours, for example. But untill it happens the changes won’t be reflected on your site.
The million dollar question is: How do we cache something and make it aware of changes to the underlying database? This much coveted feature is currently missing in ASP.NET 1.x, but will be available in ASP.NET 2.0.
I can’t afford to wait a whole year until 2.0 to get database cache dependencies. Since the majority of web apps built with ASP.NET 1.x are database driven there’s a lot of demand for this feature.
A More Excellent Way?
With great interest I tuned to a web cast, Caching in ASP.NET 2.0, conducted by Rob Howard. If y’all never listened to his web casts, let me tell you: he’s an engaging speaker! Mainly I wanted to spy around and see how 2.0 implements database cache dependencies and roll them out myself for ASP.NET 1.1.
What I understood from Rob’s presentation, in 2.0 you will need to run a command-line tool, aspnet_regsqlcache.exe:
SQL cache invalidation in SQL 7/2000 requires a one-time setup for each database/table that you want to monitor for changes. This setup is accomplished through the aspnet_regsqlcache.exe command-line tool.
According to Stephen Walther
…A new database table named AspNet_SqlCacheTablesForChangeNotification is added to the database. This table contains a list of all of the database tables that are enabled for SQL Cache Invalidation. The command also adds a set of stored procedures to the database.
After you enable a database for SQL Cache Invalidation, you must select the particular tables in the database that you will enable for SQL Cache Invalidation…
A trigger is added to the database table. The trigger fires whenever you make a modification to the table and it updates the AspNet_SqlCacheTablesForChangeNotification table.
As you see, the set up involves creation of a new table which keeps tabs on every table you monitor for changes, and triggers—one for each table.
Why I Think This Is Wrong
First off, about aspnet_regsqlcache.exe. Some companies (both large and small) are extremely paranoid about anyone touching their database servers. We’ve dealt with this before. You will need to convince them to run a command line tool against their database server. An example? Home Depot. Their bloated IT staff is so paranoid of any changes that you’ll bang your head against the wall.
Also, having triggers on a bunch of databases and serializing all changes to a special table is not something I’m excited about. I’m apprehensive of performance implications.
When Rob showed a slide with all this setup, I thought, Oh my gosh, this is way too bulky. There’s no way I’m doing it.
If you’ve ever attempted to keep session state in SQL Server and do it via a login with db_datareader and db_datawriter permissions you know my pain.
Thou Shalt Not Run As Admin
By that time I had seen several solutions of database cache dependencies for ASP.NET 1.1. I want to acknowledge ingenuity of people who come up with these awesome ideas. However, all of the solutions I’ve seen bank either on triggers, extended stored procedures written in C++ (which require manual deployment in SQL Server), empty "token" files, etc.
I’ve noticed one consistent misconception: they all act as if they run as sa (admin). That’s a pretty dangerous requirement.
To create a "notification" table or a trigger on the fly you have to run your code with more than db_datareader and db_datawriter. But aren’t we constantly taught to NOT run as admins? Isn’t security the hottest topic these days?
My rule is to set up a SQL login that has ONLY db_datareader and db_datawriter rights to my database. You try one of those stunts above and you get CREATE TRIGGER permission denied on object or something like that.
None of the solutions I’ve seen would work in a real-life situation when the deployment requirement is to use the safest account possible (non-admin) with the least amount of external dependencies (triggers, extended procs, "token" files, etc).
What I’m going to present in Part II of this discussion is my own custom database cache dependency. I’m already using it on this site, and we’re test-driving it at work. So far so good, I’m pleased with it. It ain’t perfect, but it meets the above requirement.
Conclusion
I hope y’all stayed awake this far. I wanted to give you a scoop of the problem I wanted to tackle. I decided to split this treatise in two posts so that you don’t end up reading a huge one.
I would also like to ask folks who publish articles on MSDN and elsewhere to make sure their sample code runs not only on development boxes, but also in tight corporate environments where any act of unbalancing the Matrix is not welcome. ;)
