Data Caching

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 6.2. 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.

Figure 6.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.

Listing 6.10. Creating a Broker Endpoint in SQL Server 2005

USE master GO CREATE ENDPOINT BrokerEndpoint     STATE = STARTED     AS TCP ( LISTENER_PORT = 4037 )     FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) GO ALTER DATABASE Northwind SET ENABLE_BROKER; GO

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.

Listing 6.11. Granting Permissions for SQL Notifications

- sql_dependencey_subscriber role in SQL Server EXEC sp_addrole 'sql_dependency_subscriber' - Permissions needed for users to use the Start method GRANT CREATE PROCEDURE to startUser GRANT CREATE QUEUE to startUser GRANT CREATE SERVICE to startUser GRANT REFERENCES on CONTRACT:: [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to startUser GRANT VIEW DEFINITION TO startUser - Permissions needed for users to Execute GRANT SELECT to executeUser GRANT SUBSCRIBE QUERY NOTIFICATIONS TO executeUser GRANT RECEIVE ON QueryNotificationErrorsQueue TO executeUser GRANT REFERENCES on CONTRACT:: [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to executeUser EXEC sp_addrolemember 'sql_dependency_subscriber', 'executeUser'

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.

If the user doesn't have correct permissions, then you may see an error such as:

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'.

Query Syntax

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 6.2). 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.

Listing 6.12. Caching Based on SQL Commands

<%@ Page Language="C#" ... %> <%  OutputCache Duration="30" VaryByParam="none"     SqlDependency="CommandNotification" %> <html> <form>   <h1><%=DateTime.Now %></h1>   <asp:SqlDataSource  runat="server"     ConnectionString="<%$ConnectionStrings:NorthwindConnectString%>"     SelectCommand="SELECT [ProductID], [ProductName], [UnitsInStock],                    [UnitsOnOrder] FROM [dbo].[Products]'>   </asp:SqlDataSource>   <asp:GridView  runat="server"     AutoGenerateColumns="False" DataKeyNames="ProductID"     DataSource AllowPaging="True">     <Columns>       <asp:BoundField DataField="ProductID" HeaderText="ProductID"         InsertVisible="False" ReadOnly="True" />       <asp:BoundField DataField="ProductName"         HeaderText="ProductName" SortExpression="ProductName" />       <asp:BoundField DataField="UnitsInStock"         HeaderText="UnitsInStock" SortExpression="UnitsInStock" />       <asp:BoundField DataField="UnitsOnOrder"         HeaderText="UnitsOnOrder" SortExpression="UnitsOnOrder" />     </Columns>   </asp:GridView> </form> </html>

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.

Listing 6.13. Adding Caching to the SqlDataSource Control

<asp:SqlDataSource  runat="server"   EnableCaching="true" SqlCacheDependency="CommandNotification"   CacheDuration="30"   ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"   SelectCommand="SELECT [ProductID], [ProductName], [UnitsInStock],                  [UnitsOnOrder] FROM [dbo].[Products]"> </asp:SqlDataSource>

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.

Listing 6.14. Caching Using Data Layers

<%  Page Language="C#" ... %> <%  OutputCache Duration="30" VaryByParam="none"     SqlDependency="CommandNotification" %> <html> <form>   <h1><%=DateTime.Now %></h1>   <asp:ObjectDataSource  runat="server"       SelectMethod="Read" TypeName="ProductsDataLayer">   </asp:ObjectDataSource>   <asp:GridView  runat="server"     DataSource AllowPaging="true" /> </form> </html>

The data layer simply fetches the data, as shown in Listing 6.15.

Listing 6.15. A Data Class Used in a Cached Page

public static class ProductsDataLayer {   public static DataTable Read2()   {     using (SqlConnection conn = new        SqlConnection(ConfigurationManager.ConnectionStrings[        "NorthwindConnectString"].ConnectionString))     {       conn.Open();       SqlCommand cmd = new SqlCommand("usp_GetProductsOrdered",                                      conn);       cmd.CommandType = CommandType.StoredProcedure;       DataTable tbl = new DataTable();       tbl.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));       return tbl;     }   } }

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);

Listing 6.16. SqlCacheDependency from Code

using (SqlConnection conn = new    SqlConnection(ConfigurationManager.ConnectionStrings[    "NorthwindConnectString"].ConnectionString)) {   conn.Open();   SqlCommand cmd = new SqlCommand("usp_GetProductsOrdered", conn);   cmd.CommandType = CommandType.StoredProcedure;   DataTable tbl = new DataTable();   SqlCacheDependency dependency = new SqlCacheDependency(cmd);   tbl.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));   Response.AddCacheDependency(dependency);   return tbl; }

If this code is in a class in the App_Code directory, the Response can be accessed with the HttpContext object:

HttpContext.Current.Response.AddCacheDependency(dependency);

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.

Another method of caching is to cache only the data, leaving the page uncached. This works in a similar way to fragment caching, or adding the cache details to the data source control.

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.

Listing 6.17. Explicitly Caching Data

public static class ProductsDataLayer {   public static DataTable ReadCached()   {     if (HttpContext.Current.Cache["Products"] == null)     {       using (SqlConnection conn = new          SqlConnection(ConfigurationManager.ConnectionStrings[          "NorthwindConnectString"].ConnectionString))       {         conn.Open();         SqlCommand cmd = new SqlCommand("usp_GetProductsOrdered",                                         conn);         cmd.CommandType = CommandType.StoredProcedure;         DataTable tbl = new DataTable();         SqlCacheDependency dependency = new SqlCacheDependency(cmd);         tbl.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));         HttpContext.Current.Cache.Insert("Products",           tbl, dependency);         return tbl;       }     }     return (DataTable)HttpContext.Current.Cache.Get("Products");   } }

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:

• You have to explicitly enable caching on a database and table level.

• A new table is created that has one entry for each table upon which cache dependencies exist. There is only one row per enabled table, so the number of rows in this table will never exceed the number of tables in the database.

• Triggers are added to tables enabled for caching, so that data changes result in an update to the notifications table.

• A background thread in ASP.NET polls the change notifications table for changes. If a row in the change notifications table has changed, then the page dependent upon this table is evicted from the cache.

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 Table 6.6.

Table 6.6. Options for aspnet_regsql

Flag	Description
?	Displays a help listing of the various flags supported by the tool.
S	Names the SQL Server to connect to. This can be either the computer name or the IP address.
U	Names the user to connect as when using SQL Server Authentication (e.g., the SQL Server administrator account, sa).
P	Used in conjunction with the U flag to specify the user's password.
E	Connects to the SQL Server when using Windows Authentication and the current user has administrator capabilities on the database. The U and P flags are not needed when using E.
t	Specifies the table to apply necessary changes for SQL Server cache invalidation to.
d	Specifies the database to apply changes for SQL Server cache invalidation to.
ed	Enables a database for SQL cache dependency. This requires the d option.
dd	Disables a database for SQL cache dependency. This requires the d option.
et	Enables a table for SQL cache dependency. This requires the t option.
dt	Disables a table for SQL cache dependency. This requires the t option.
lt	Lists all tables enabled for SQL cache dependency.

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]

If you have a separate database server and don't have ASP.NET 2.0 installed, then you can enable the database on any server and simply move the database files to the database server.

Figure 6.3 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.

Figure 6.3. Enabling a database for SQL cache invalidation

This new table contains the columns shown in Table 6.7.

Table 6.7. Columns of AspNet_SqlCacheTablesForChangeNotification

Column	Description
tableName	Stores the name of all tables in the current database capable of participating in change notifications.
notificationCreated	Sets the timestamp indicating when the table was enabled for notifications.
changeId	Sets the numeric change ID incremented when a table is changed.

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 6.4.

Figure 6.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.

Listing 6.18. SQL Server 2000 Cache Configuration

<caching>   <sqlCacheDependency enabled="true" pollTime="10000">     <databases>       <add name="Northwind" connectionStringName="Northwind2000"         pollTime="5000" />     </databases>   </sqlCacheDependency> </caching>

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).

1. The user creates the page default.aspx and instructs the page to output to the cache and be dependent on the Northwind database's Products table.

2. The page is requested.

   1. SqlCacheDependency is created and polling begins.

   2. An entry in the cache is created for the Products table (e.g., Products_Table) by ASP.NET. This entry stores the changeId value returned from the database.

   3. The output-cached page is made dependent on the Products_Table cache entry.

3. The page is output cached and subsequent requests draw the page from the cache.

4. A sales manager updates the Products table for a new Web site special sale.

   1. The Northwind Products table changes and the changeId for this table is updated in the AspNet_SqlCacheTablesForChangeNotification table.

   2. The next poll by ASP.NET gets the new changeId value for the Products table.

   3. The Products_Table cache key is updated with the new changeId value, causing all dependent cache keys to be invalidated, including the default.aspx page.

5. The next request to the ASP.NET application causes the page to re-execute (because it is no longer in the output cache) and get added again.