ne of the major advances in ASP.NET 2.0 caching is support for SQL cache invalidation
. This feature lets you retrieve or generate a rowset, usually as a DataSet
, and then hang on to it until the original source data changes. That removes the balancing act formerly required to refresh the data (invalidate the cached rowset) regularly enough to see recently updated values in the data source, yet still hang on to each cached copy long enough to get the required reduction in resource usage and retrieval time when reconstructing the rowset.
Now, with SQL Server versions 7.0, 2000 or 2005, you simply set up a dependency for the database and table(s) you are using, fetch and cache the rowset, and then keep using the cached copy until the database informs your application that the source data has changed. This can obviously provide a huge performance boost, with a corresponding reduction in response times and database/server resource usage.
Configuring SQL Cache Invalidation
In SQL Server 7.0 and SQL Server 2000, you have to pre-configure the database to support SQL cache invalidation, using a utility provided with version 2.0 of the .NET Framework. You'll find this utility, named aspnet_regsql.exe
, in the %windir%\Microsoft.NET\Framework\[version]
folder of your machine after installing .NET 2.0.
|Figure 1. Preparing SQL Server 2000 for SQL Cache Invalidation: The figure shows change notification being configured on a SQL Server 2000 database named "delboy", with the user ID "anon," for the Northwind sample database and the table named Orders.|
Configuring the database involves two steps:
- Enable your database for change notifications, which creates the change notification table and adds the required stored procedures:
aspnet_regsql.exe -S server -U user
-P password -d database -ed
- Enable the table(s) containing the data you are using, which adds the necessary row to the change notification table and adds the required triggers to the source data table:
aspnet_regsql.exe -S server -U user
-P password -d database -t table -et
If you are using a trusted connection to a local server, or a server on your network, you can replace the -S
parameters and values with just the empty parameter -E
. Figure 1
shows change notification being configured on a SQL Server 2000 database named "delboy", with the user ID "anon," for the Northwind sample database and the table named Orders
. Notice how the utility prompts for the password if you do not specify it in the command to execute aspnet_regsql.exe
|Figure 2. Cache Tables and Stored Procedures: After running aspnet_regsql.exe, you can see the new tables and stored procedures added to SQL Server 2000 for SQL cache invalidation.|
shows the results. You can see the new table named AspNet_SqlCacheTablesForChangeNotification
(so it's not likely to clash with the name of an existing table), and in it the row for the Orders
table. The lower section of the window shows the stored procedures used to implement the change notification.
SQL Server 2005 Change Notifications
While this technique does work with SQL Server 2005 ("Yukon"), you should prefer to use the built-in change notification service instead. The new Broker Service in SQL Server 2005 can be configured to monitor executed commands that return rowsets, and invalidate the cache automatically when any
event occurs that could result in different values being returned if the same query were re-executed.
ASP.NET integrates with the Broker Service in SQL Server 2005, and can use the change notifications it produces to automatically invalidate output cached pages that use data generated through explicit code or the implicit commands it uses to extract data for the new data source controls.