June 26, 2011, 7 p.m.
posted by pumi
One of the greatest performance benefits can be achieved with caching of data, the fetching of which always involves some form of overhead. For XML files, this is the file system, and for databases, it is the connection and physical extraction of the data. Two types of data are generally displayed in Web pages: data that changes often, and data that doesn't change often. If the data doesn't change often, there is no point in going through the expensive operation of connecting to the database to get the data and using valuable database resources. A better solution would be to cache the data, thus saving the time and resources of the database server.
The problem with caching data, or even caching entire ASP.NET pages that have database-driven data on them, is what to do if the data changes. In fact, how do you even know if the data has changed? ASP.NET 2.0 provides features that allow its built-in cache to be invalidated when data changes so that the page is regenerated. This brings the best of both worldsfresh data, but cached for increased performance.
The features of cache invalidation depend upon the database server, and both SQL Server 2000 and 2005 support this, although with different features.
SQL Cache Invalidation with SQL Server 2005
SQL Server 2005 supports notifications via a service brokera feature that allows it to notify client applications when data has changed. This can be combined with ASP.NET's cache so that pages can be notified when the data they rely upon changes. When the data changes, SQL Server notifies ASP.NET, the page is evicted from the cache, and the next request will see the page regenerated with the fresh data.
Cache invalidation works with both SQL Server 2005 and the Express editions, but with the Express edition it will only work if user instancing is not used. That means that you cannot have the User Instance=true keywords in your connection string, and that you must have an attached database.
In operation, SQL Server cache invalidation is seamless, but it does require some initial setup.
Cache Invalidation Setup
The setup required depends upon how you connect to SQL Server and whether the user is a database owner (and hence has administrative rights in the database). Whatever permissions the user has, there is a one-time setup, involving ensuring that the database is at the correct version number and that the service broker endpoint is created.
For new databases, the version number will be correct, but for old databases that you have attached, it may not be. You can check this by issuing the sp_helpdb command in a new query, which will return a list of all databases and associated details, as shown in Figure. Here you can see the list of databases; one of the columns is compatibility_level, which must be 90 for the service broker to work.
2. The move toward managed execution
You can upgrade the compatibility level of a database by executing the following simple command:
exec sp_dbcmptlevel 'Northwind', '90'
You simply supply the database name and the level to upgrade to. When the version is correct, you can create the broker endpoint, using the script shown in Listing 6.10.
Creating a Broker Endpoint in SQL Server 2005
This script is available as CreateAndEnableServiceBroker.sql in the databases directory of the downloadable samples.
If you are connecting to SQL Server as a trusted user, such as using integrated security, and that user has administrative rights, then this is all you require for the configuration. If you're not an administrative user, whether using integrated security or not, then you need to grant the database user additional permissions.
Cache Invalidation Setup for Non-Administrative Connections
For non-administrative users, the setup is also a one-time affair, but it is necessary to grant permissions so that the user can create the required objects. For this, you should run the script detailed in Listing 6.11.
Granting Permissions for SQL Notifications
This script is available as EnableServiceBrokerNonAdmin.sql in the databases directory of the downloadable samples.
Three sections appear in Listing 6.11. The first simply creates a new role for the subscriber of notifications. The second creates the permissions for the user to execute the Start methodthis is something we'll be covering soon. The third section creates permissions for the user executing the database query. The startUser and executeUser can be the same user and can be a Windows user account (such as ASPNET) or an explicit SQL Server user account.
Cannot find the contract 'http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification ', because it does not exist or you do not have permission. Invalid object name 'SqlQueryNotificationService-d1963e55-3e62-4d54- b9ca-b4c02c9e6291'.
Once the database and permissions have been configured, you can start using SQL notifications, but one important point to note is that the syntax used for the query must follow certain conditions. The first is that you cannot use * to represent all columnscolumns must be explicitly named. The second is that the table name must be qualified with its owner. For example:
SELECT ProductID, ProductName FROM dbo.Products
If you think you have everything configured correctly, but your pages don't seem to be evicted from the cache when you change the data, then you need to check the query as well as the database compatibility version (see Figure). Once permissions are correct, you will not see any exceptions regarding cached pages dependent upon SQL data, because failures happen silently.
Using SQL Server 2005 Cache Invalidation
Using the SQL Server 2005 cache invalidation is extremely simple, because you use the same features as you use for standard page caching, but this time you add the SqlDependency attribute:
<%@ OutputCache Duration="30" VaryByParam="none" SqlDependency="CommandNotification" %>
The page would now be output-cached, but a dependency would be created on any data commands within the page. Using CommandNotification means that the page is cached until notified by SQL server. For example, consider Listing 6.12, which has output caching enabled, based on SQL commands. The data source and grid controls contain no additions to take care of the caching, and were there more data controls with different queries, then a change to either data source would result in the page being evicted from the cache.
Caching Based on SQL Commands
This scenario can easily be tested by calling the page and clicking Refresh. The date should remain the same. But if you modify a row in the Products table and then click Refresh, the page will be updated with a new date. Because this query selects all rows, any change to the underlying data will result in the cache being invalidated. However, if the query had a WHERE clause, invalidation would only take place if the changed data was part of the set of rows returned by the query; changes to rows not part of the query have no effect upon the cache.
If you wish to cache only the data on a page, you have two options. You can wrap the data (data source and grid) up in a user control and use fragment caching, or you can add the caching dependency to the data source control directly and remove it from the page, as shown in Listing 6.13.
Adding Caching to the SqlDataSource Control
In effect, this is similar to fragment caching for any controls on the page that are bound to the data source.
Caching Using Business and Data Layers
If you wish to use a business or data layer to abstract your data access code, caching can still be used, and there are two ways to achieve this. The first is to use output caching and have the page dependent upon the data, and the second is to only cache the data. For the first option, you use the same method as previously shown, adding the OutputCache directive to the page with the SqlCacheDependency attribute set to CommandNotification. An ObjectDataSource control can be used to fetch the data from the data layer, as shown in Listing 6.14.
Caching Using Data Layers
The data layer simply fetches the data, as shown in Listing 6.15.
A Data Class Used in a Cached Page
The query can be a SQL statement or a stored procedure, as long as the actual SQL statement follows the rules for query notificationsexplicit column names and two-part table names. In addition, you should not use SET NOCOUNT ON in a stored procedure or the rowset will not be cacheable.
If you do not wish to place cache details within the page, it can be done programmatically by way of the SqlCacheDependencyClass and a method on the Response object. For example, consider Listing 6.16, which returns a DataTable, perhaps as a function within a page. Here the SqlCacheDependency object is created explicitly, with the SqlCommand object passed in as a parameter. This creates a dependency based upon the command. The dependency is then added to the list of dependencies of the ASP.NET cache using the AddCacheDependency method.
As well as adding items to the cache, the API also exposes other features. For example:
Response.Cache.SetExpires(DateTime.Now.AddSeconds(30)); Response.Cache.SetCacheability(HttpCacheability.Public) ; Response.AddCacheDependency(dependency);
SqlCacheDependency from Code
If this code is in a class in the App_Code directory, the Response can be accessed with the HttpContext object:
This is not something you'd want to explicitly do in business or data layers though, because it ties the method to the interface, which could reduce reuse of this code for other scenarios.
Listing 6.17 shows a standard pattern for caching using the Cache object of ASP.NET; the Cache object is the API into the underlying caching mechanism, so you can manipulate it directly as well as through page and control attributes.
In the ReadCached method, the first action is to check the Cache for an item; the cache provides a simple dictionary approach, so items can be accessed by a key value, Products in this case. If the item doesn't exist in the cache, the command is executed to fetch the data; note that a SqlDependency is explicitly created (and it has to be created before the command is executed). Once the data has been fetched, it is placed into the cache using the Insert method; the first parameter is the key, the second is the data being stored, and the third is the dependency. Once stored in the cache, the data is returned. The final code line will only execute if the item is already in the cache, so the Get method is used to fetch the item using its key value. The item is returned from the cache as an Object and thus has to be cast to its original data type, a DataTable.
Explicitly Caching Data
This code doesn't affect the page caching but uses the same mechanism. If the data changes, the cache receives notification from SQL Server, and the item is evicted from the cache.
SQL Cache Invalidation with SQL Server 2000
Caching using SQL Server 7 and 2000 uses many of the same constructs as for SQL Server 2005, but works in a different way. The first thing to note is that SQL Server 2000 does not use notifications, which means that caching is polling-based. The database isn't continuously polled, so there is no huge overhead. It works like this:
The second point to note is that with SQL Server 2000, cache invalidation is based upon any changes to the entire table. So even changes to rows that are not part of the result set you are displaying will affect the page cache.
Enabling SQL Server 2000 for Cache Invalidation
To enable SQL Server 2000 for cache invalidation, you need to run a command line tool, aspnet_regsql, stored in the framework directory (\WINDOWS\Microsoft.NET\Framework\v2.0.50727). This tool has several uses, including adding application services such as membership and personalization to databases, and there are a number of command-line switches. The options for cache invalidation are shown in Figure.
Enabling a Database for Cache Invalidation
Before a database table can participate in SQL cache invalidation, both the database and table must be enabled. To enable a database on a machine, use the following command:
aspnet_regsqlcache.exe -U [user] -P [password] -ed -d [database]
Figure shows an example of enabling a SQL Server running on the local machine. The E flag is used for Windows authentication. The ed flag is used to enable the database, and the database is specified with the d flag. This creates a new table named AspNet_SqlCacheTablesForChangeNotification.
3. Enabling a database for SQL cache invalidation
This new table contains the columns shown in Figure.
Now that the database is enabled for change notifications, you need to enlist tables that you wish to watch for changes.
Enabling a Table for Cache Invalidation
After you enable the database for change notifications, you need to enlist selected tables for change notifications, and for this you use the et and t flags:
aspnet_regsqlcache.exe -U [user] -P [password] -et -t [table] -d [database]
For example, if you want to enable the Products tables in the Northwind database, you execute aspnet_regsql as shown in Figure.
4. Enabling a table for SQL cache invalidation
This creates a trigger Products_AspNet_SqlCacheNotification_Trigger on the Products table and also adds an entry into the AspNet_SqlCache TablesForChangeNotification table for the Products table. Whenever data within the Products table is updated, inserted, or deleted, the trigger causes the changeId value stored in the AspNet_SqlCacheTablesForChangeNotification table to be incremented.
Configuring SQL Server 2000 Cache Invalidation in ASP.NET
When you use SQL Server 2000 cache invalidation, ASP.NET polls the database for changes. The information about the polling is defined in web.config, in the caching section, as shown in Listing 6.18.
SQL Server 2000 Cache Configuration
The SqlCacheDependency section contains two attributes: enabled, to turn the feature on or off, and pollTime, which is the time in milliseconds between polls of the database. The pollTime defaults to 5000. The databases sections details the databases upon which polling will take place, and follows the standard provider pattern of having add and remove elements. For add, the name is the key and doesn't have to correspond to the database being polled, although obviously a similar name makes sense. The connectionStringName identifies the connection string from the connectionStrings section, and pollTime specifies the polling time for this particular entry, overriding the pollTime set on the sqlCacheDependency element.
Using SQL Server 2000 Cache Invalidation in ASP.NET
The use of SQL Server 2000 for cache invalidation is similar to that for SQL Server 2005; the attributes and usage of controls is the same, but the dependency differs. For SQL Server 2000, instead of CommandNotification, you use the key name from the configuration and the table name, separated by a colon (:). For example:
<% OutputCache Duration="30" VaryByparam="note" SqlDependency="Northwind:Products" %>
In use, the page works exactly the same as for SQL Server 2005 notifications; upon first request, the page will be cached and will not be evicted from the cache until data has changed. Of course, the eviction doesn't happen immediately after the data changes but only after the poll time has elapsed.
The replacement of CommandNotification with the cache key and table applies to the API as well, when you create the SqlCacheDependency:
SqlCacheDependency dependency = new SqlCacheDependency("Northwind", "Products");
Here the first parameter is the key into the databases section of the caching configuration, and the second parameter is the table name.
How Polling Works
On the first poll, the list of notification-enabled tables is returned from the database. This list of tables is used to construct a cache entry for each table returned. Any dependencies requested through SqlCacheDependency are then made on this hidden cache entry. Thus, multiple SqlCacheDependency instances can be made for the same table, all dependent on one entry in the cache. When the table cache entry changes, it invalidates all dependent cache items.
The following is an example session (which assumes that the Northwind database and Products table are already configured for change notifications).