There is nothing more dreadful than imagination without taste. - Goethe
In a previous article, I covered much of the basics of ASP.NET caching. Here I'll narrow the focus on to two specific implementations of caching -- for both ASP.NET Pages and Application Data -- using Database Dependencies.
Most ASP.NET developers get involved with caching as an afterthought, usually implementing some sort of caching only after their web application is completed. I suggest very strongly that this approach is both wrong and flawed. When you start to design an ASP.NET application and you are grappling with how to retrieve and use any kind of object (database data, a page, a control, or other objects) that is expensive to create, the very first thing on your mind should be, "How can I cache this to speed up operations?". Engineer caching into your apps from the very beginning.
I was aware of caching capabilities very early on in the history of ASP.NET, but I didn't really "get the message" as I've described just above until I sat in one of Rob Howard's ASP.NET presentations at Tech-Ed 2004. Rob demonstrated some advanced caching techniques (including one I've adapted for the SQLite database here) and showed real-time App Center Test results, both with and without caching. When you are in a room full of 600 other goons all of whom are having an epiphany at the same time, the message really sinks in. I hope this article helps to get it across for you, too, or at least helps to motivate further study.
"Caching" is a somewhat ambiguous term in that it doesn't always mean using the ASP.NET Cache class. To me, what caching really means is the ability to store expensive-to-create objects or data, and produce them (or their results) on demand without having to recreate the data. The ADO.NET SqlConnection Pool is an example of caching database connections, but it does not use the ASP.NET Caching classes. The use of WeakReferences is another, for caching expensive objects.
Caching is kind of like a hybrid car, having a set of batteries charged up with exactly what you need, that can deliver your power on demand without having to start the expensive gasoline engine. If the batteries are low or empty, the gas engine starts on demand and not only provides your power, it also recharges the batteries so that you can have more efficient throughput. Caching data for as little as 1 second can have a major effect on site performance, as Rob has so astutely pointed out in his Tech-Ed and other presentations.
In ASP.NET 2.0, the caching infrastructure you have to work with has been appropriately "souped up" to provide lots more flexibility and ease of use. You can cache pages ( <%@ OuptputCache Duration="10" VaryByParam="None" %> ), pages based on Query String or FormField parameters, pages based on browser type and / or version, or even by custom developer-defined strings. You can also cache pages based on database dependencies, and that's what we will examine here.
ASP.NET 1.1 offered a number of ways to cache data with dependencies, but using database dependencies wasn't one of them. In ASP.NET 2.0, we can cache pages based on data in a SQL Server database. This includes SQL Server 7.0, 2000, 2005, MSDE, and SQLExpress.
As with most other cache dependencies, the controlling factor is to be able to determine that cache content should be expired based on the knowledge that some data has changed -- in this case, data in the database.
Caching Pages with SqlDependencies
SQL Server 2005 and Express offer built-in notification events, and neither requires any configuration changes to support this other than adding the <sqlCacheDependency> element to the web.config file. With SQL Server 7.0, 2000 and MSDE, the ability to determine if data has changed is provided via a polling mechanism. The table AspNet_SqlCacheTablesForChangeNotification contains a row for each table to be monitored. When data in a monitored table changes, a trigger changes the value in the changeId column for that table and the next polling interval picks this up. With these older databases, your database needs to be altered to support these changes.
There are two options for doing this:
1) aspnet_regsql.exe can be run to configure a database for notifications, and to configure a specific table to be monitored:
Database: aspnet_regsql -S <server> -E -d <database> -ed
Table: aspnet_regsql -S <server> -E -d <database> -et -t <table>
2) Using the SqlCacheDependencyAdmin Class.
Simplified Example:
Database: SqlCacheDependencyAdmin.EnableNotifications(connectionString);
Table: SqlCacheDependencyAdmin.EnableNotifications(connectionString, tableName);
There are corresponding Disable methods as well.
When your database is configured for notifications, you add the <sqlCacheDependency> element to your web.config:
<caching>
<sqlCacheDependency enabled="true" pollTime="30000">
<databases>
<add name="Pubs" connectionStringName="PubsConn" pollTime="20000" />
</databases>
</sqlCacheDependency>
</caching>
Note that you can override the default pollTime attribute for a specific connectionString in the databases node. The pollTime attribute is in milliseconds. The name attribute of the <add> element in the <databases> element defines the name of the database when defining SqlDependencies in your app; it does not have to be the actual database name. The connectionStringName attribute must be set to the name of a connection string stored in the <connectionStrings> element of your web.config.
With your setup complete, you can cache any page based on a database dependency by adding the <@OutputCache page directive, like so:
<%@ OutputCache Duration="100" VaryByParam="none" SqlDependency="Pubs:Books" %>
The SqlDependency attribute is set to the name of the database as defined in web.config "name" attribute, followed by a colon and the name of the table that you want to use as the dependency for the page. Multiple databases and tables can be specified by providing a semicolon-delimited list of database:table pairs.
Caching Application Data with SqlDependencies.
You do not have to cache the entire page with SqlDependencies. You can cache individual controls, or where appropriate, the application data that is used by a control (or the page).
To cache Application Data, you would configure your SQL Server database as described above, and add the <sqlCacheDependency> element to your web.config the same way as for Page caching.
Once you've configured your SQL Server and sqlCacheDependency web.config element, adding data to the ASP.NET Cache with a SqlCacheDependency is simple. You only need to create an instance of a SqlCacheDependency class, specifiying the database (as in the web.config element) and the table, and pass this instance when adding your data to the Cache:
SqlCacheDependency sqlDep =new SqlCacheDependency("Pubs", "Books");
Context.Cache.Insert("myCacheKey", dataSetBookData, sqlDep);
If you are using SQL Server 2005 or Express, you can create a SqlCacheDependency object by passing a SqlCommand object to the constructor. This provides a more granular approach to define the data being monitored and used for expiring the data from the Cache, because a SqlCommand can contain any stored procedure invocation or CommandText including a WHERE clause. For example, "newest employees":
cmdText = "SELECT FIRSTNAME, LASTNAME, EMPLOYEEID FROM EMPLOYEES WHERE HIREDATE >'09/30/2006'" ;
sqlCommand sqlCmd = new SqlCommand(cmdText, sqlConnection);
SqlCacheDependency sqlDep = new SqlCacheDependency(sqlCmd);
// etc.
The above examples are all quite simplified and do not have any exception handling, which of course, I leave to the expert reader to supply.
Caching DataSources
Finally, you can cache datasources. The XmlDataSource, ObjectDataSource and SqlDataSource classes all offer caching:
dataSource.EnableCaching=true;
dataSource.CacheDuration=5;
Summary
The SqlCacheDependency and related tools are an important tool in your arsenal of techniques to be a more professional ASP.NET developer. Developers should strive to create a mindset that includes using caching, optionally with the SqlCacheDependency tools, from the outset when developing ASP.NET 2.0 applications. The result will not only be more scalable web applications, but the recognition of your peers and the customers who consume your work. |