Overview: Page Output and SQL Dependency Caching

There are a variety of caching mechanisms available in ASP.NET 2.0. Caching is straightforward and very important for proper Web application performance. Every developer should learn how to use caching properly, for with it you can easily turn a slow, overburdened application into a high performance Web site.

Here are some simple scenarios to help you better understand the power of caching:

Scenario 1—Page Output Caching: You have an ASPX Web page that is complex and shows information that is relatively dynamic.  Changes to the underlying data occur fairly frequently, but the end user does not need to be aware of these changes every time the data is updated. For this scenario you can use Page output caching. This approach caches the page's contents, serving it from memory until it expires instead of serving up a new page each time. You can control how long the page caching is maintained per page. A simple modification of the page directive enables this type of caching.

Scenario 2—SQL Dependency Caching: You have data-bound controls that display data from SQL Server. As you probably know, round trips to the database are very expensive. If you could cache the underlying data displayed by one or more controls, this would boost the performance of both your database server and Web server. The catch is that you want to invalidate the cached data not according to a timestamp (like the above scenario), but rather when the data changes in the database. For this scenario you can use SQL dependency caching.  SQL Server 2005/SQL Express is able to notify the Web server whenever a change occurs in your database tables. This allows you to invalidate the cache immediately and automatically refresh the control's data. VWD also ships with utilities that allow you to enable your SQL 7/2000 databases to support SQL dependency caching.

SQL Server 2005/SQL Express Dependency Caching

Caching works differently in SQL Express than it does in SQL Server 7/2000. Whenever changes occur in a database table, SQL Express proactively notifies ASP.NET via IIS that the related cache must be invalidated:

The plumbing to support caching is built into VWD, .NET and SQL Express, so implementation is greatly simplified compared to SQL 7/2000 dependency caching.

SQL Server 7/2000 Dependency Caching

There is no notification model built into SQL Server 7/2000. In other words, earlier versions of SQL Server will not proactively notify a Web server of changes to the data. Instead, ASP.NET must monitor database tables that you specify.

The good news is that VWD ships with utilities that automatically configure a database table for dependency caching.  After running these utilities, additional cache tables are created in your database.  Additionally, triggers are added to your tables to populate the new tables.  Whenever a change occurs in your table, the data in the cache tables is also modified.  A process within ASP.NET periodically monitors the cache table and determines whether or not a change has occurred. You can then use these change detections to programmatically invalidate the cached data.