SQL Cache Invalidation

1      Introduction

Caching has been one of the most efficient features in ASP.NET to dramatically improve the performance of a web application. Caching can be defined as storing the frequently used items in memory. This document provides bottlenecks of Caching in data driven web applications, introduces a new feature “SQL Cache Invalidation” in ASP.Net 2.0, and how it can be implemented to overcome the current drawbacks in ASP.Net 1.x.

2      Caching in ASP.NET 1.x

In the existing ASP.NET 1.x framework, the one and only drawback to caching is the problem of stale data. If you cache the contents of a database table in memory, and the records in the underlying database table change, then your Web application will display old, inaccurate data. For certain types of data you might not care if the data being displayed is slightly out of date, but for other types of data—such as stock prices and auction bids—displaying data that is even slightly stale is unacceptable.

The initial release of the Microsoft ASP.NET framework did not provide a good solution to this problem. When using the ASP.NET 1.x framework, you just had to live with this tradeoff between performance and stale data. Fortunately, the Microsoft ASP.NET 2.0 framework includes a new feature called SQL Cache Invalidation that solves this problem.

3      SQL Cache Invalidation

SQL Cache Invalidation is one of the most anticipated new features of the ASP.NET 2.0 framework. By taking advantage of SQL Cache Invalidation, you get all of the performance benefits of caching without the problem of stale data. SQL Cache Invalidation enables you to automatically update data in the cache whenever the data changes in the underlying database.

SQL Cache Invalidation works only with SQL Server database with version 7.0 and higher. Behind the scenes, SQL Cache Invalidation works differently in SQL Server 7.0/2000 and SQL Server 2005 code named Yukon.

3.1    SQL Cache Invalidation in SQL Server 2005

Whenever changes occur in a database table, Yukon proactively notifies ASP.NET via IIS that the related cache must be invalidated. The plumbing to support caching is built into ASP.NET and SQL Server 2005, so implementation is greatly simplified compared to SQL 7/2000.

3.2    SQL Cache Invalidation in SQL Server 7.0/2000

Cache Invalidation in SQL Server 7.0 and SQL Server 2000 are handled in the same manner.  Rather than being proactively notified, such as caching dependency in Yukon, ASP.NET must monitor the database table that you have a caching dependency on.  There is no notification model built into SQL Server 7.0 or SQL Server 2000.

.NET Framework 2.0 ships with utilities that automatically prepare 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 cache tables are altered.  A process within ASP.NET periodically monitors the cache table and determines whether or not a change has occurred.  If a change has occurred, this process invalidates the respective cache items.

The following figure describes at a high level the caching in SQL Server 7.0/2000.

It is important to understand that SQL Cache Invalidation only works with Microsoft SQL Server version 7 and higher. You cannot use this feature with other databases such as Microsoft Access or Oracle.

You can use SQL Cache Invalidation when caching the output of an entire page, when working with the DataSource controls, or when working directly with the Cache object. We'll examine all three scenarios.

4      Configuring SQL Cache Invalidation

Before you can take advantage of SQL Cache Invalidation in your Web application, you must first perform some configuration steps. You must configure Microsoft SQL Server to support SQL Cache Invalidation and you must add the necessary configuration information to your application's Web configuration file.

4.1    Configuring SQL Server

There are two ways that you can configure SQL Server. You can either use the aspnet_regsql command line tool, or you can take advantage of the SqlCacheDependencyAdmin class.

 4.1.1 Enabling SQL Cache Invalidation with ASPNET_REGSQL

The aspnet_regsql tool enables you to configure SQL Cache Invalidation from the command line. The aspnet_regsql tool is located in your Windows\Microsoft.NET\Framework\[version] folder. You must use this tool by opening a command prompt and navigating to this folder.
In order to support SQL Cache Invalidation when using the Pubs database, you need to execute the following command.

aspnet_regsql -E -d Pubs -ed 

The -E option causes the aspnet_regsql tool to use integrated security when connecting to your database server. The -d option selects the Pubs database. Finally, the -ed option enables the database for SQL Cache Invalidation.
When you execute this command, a new database table named AspNet_SqlCacheTablesForChangeNotification is added to the database. This table contains a list of all of the database tables that are enabled for SQL Cache Invalidation. The command also adds a set of stored procedures to the database.
After you enable a database for SQL Cache Invalidation, you must select the particular tables in the database that you will enable for SQL Cache Invalidation. The following command enables the Titles database table.

aspnet_regsql -E -d Pubs -t Titles -et 

The -t option selects a database table. The -et option enables a database table for SQL Cache Invalidation. You can, of course, enable multiple tables by re-executing this command for each database table.
When you execute this command, a trigger is added to the database table. The trigger fires whenever you make a modification to the table and it updates the AspNet_SqlCacheTablesForChangeNotification table.

Finally, if you want to get the list of tables that are currently enabled for SQL Cache Invalidation in a particular database, you can use the following command.

aspnet_regsql -E -d Pubs -lt 

This command selects the list of tables from the AspNet_SqlCacheTablesForChangeNotification. Alternatively, you could retrieve this information by performing a query directly against this database table.

 

4.1.2 Using the SqlCacheDependencyAdmin Class

Behind the scenes, the aspnet_regsql tool uses the methods of the SqlCacheDependencyAdmin class to configure Microsoft SQL Server. If you prefer, you can use the methods of this class directly from within an ASP.NET page.

The SqlCacheDependencyAdmin class has five important methods:

·         DisableNotifications—Disables SQL Cache Invalidation for a particular database.

·         DisableTableForNotifications—Disables SQL Cache Invalidation for a particular table in a database.

·         EnableNotifications—Enables SQL Cache Invalidation for a particular database.

·         EnableTableForNotifications—Enables SQL Cache Invalidation for a particular table in a database.

·         GetTablesEnabledForNotifications—Returns a list of all tables enabled for SQL Cache Invalidation.

 

For example, the ASP.NET page in Listing 2 enables you to configure SQL Cache Invalidation for any table in the Pubs database (see Figure 2).

Figure 2. Enabling SQL Cache Invalidation from an ASP.NET page

Listing 2. EnableSCI.aspx (C#)

<%@ Page Language="c#" %>

<%@ Import Namespace="System.Web.Caching" %>

<script runat="server">

    const string connectionString = "Server=localhost;Database=Pubs";

    void Page_Load()

    {

        if (!IsPostBack)

        {

            SqlCacheDependencyAdmin.EnableNotifications(

            connectionString);

            SqlDataSource1.SelectParameters.Add("connectionString", 

            connectionString);

        }

    }

    void EnableTable(Object s, EventArgs e)

    {

        try

        {

            SqlCacheDependencyAdmin.EnableTableForNotifications(

              connectionString, txtTableName.Text);

        }

        catch (Exception ex)

        {

            lblErrorMessage.Text = ex.Message;

        }

        txtTableName.Text = "";

    }

</script>

<html>

<head runat="server">

    <title>Enable SQL Cache Invalidation</title>

</head>

<body>

    <form id="form1" runat="server">

    <h1>SQL Cache Invalidation</h1>

    The following tables are enabled for SQL Cache Invalidation:

    <p>

    <asp:GridView id="grdTables" 

      DataSourceID="SqlDataSource1" CellPadding="10" 

      ShowHeader="false" Runat="Server" />

    </p>

    <asp:ObjectDataSource 

        ID="SqlDataSource1" 

        TypeName="System.Web.Caching.SqlCacheDependencyAdmin"

        SelectMethod="GetTablesEnabledForNotifications"

        Runat="Server" />

    <p>

    <asp:Label ID="lblErrorMessage" EnableViewState="false" 

      ForeColor="red" Runat="Server" />

    </p>

     <asp:TextBox ID="txtTableName" Runat="Server" /> 

     <asp:Button Text="Enable Table" OnClick="EnableTable" 

       Runat="Server" /> 

    </form>

</body>

</html> 

In Listing 2, the connectionString constant is used to select the database for which SQL Cache Invalidation is enabled (You can change the value of this constant when you want to enable SQL Cache Invalidation for a database other than the Pubs database). Within the Page_Load method, the EnableNotifications method on the SqlCacheDependencyAdmin class is called to enable SQL Cache Invalidation for the database specified by the connectionString constant.

The GridView in Listing 2 displays all of the database tables that are currently enabled for SQL Cache Invalidation. The GridView is bound to an ObjectDataSource control that calls the GetTablesneabledForNotifications method for its SelectMethod.

Finally, you can use the page in Listing 2 to enable additional tables for SQL Cache Invalidation. When you enter the name of a table in the textbox and click the Enable Table button, the EnableTableForNotifications method is called.

4.2  Web Configuration Settings for SQL Cache Invalidation

The next step, before you can use SQL Cache Invalidation in your ASP.NET application, is to update your Web configuration file. You need to configure the ASP.NET framework to poll the databases that you have enabled for SQL Cache Invalidation.
The Web configuration file in Listing 3 contains the necessary configuration information to poll the Pubs database.

Listing 3. Web.Config

<configuration>

  <connectionStrings>

    <add name="mySqlServer" 

      connectionString="Server=localhost;Database=Pubs" />

  </connectionStrings>

  <system.web>

    <caching>

      <sqlCacheDependency enabled="true">

      <databases>

      <add

            name="Pubs"

            connectionStringName="mySqlServer"

            pollTime="60000" />

      </databases>

</sqlCacheDependency>

    </caching>

    </system.web>

</configuration> 

The Web configuration file in Listing 3 contains two sections. The <connectionStrings> section is used to create a database connection string to the Pubs database named mySqlServer.
The caching section is used to configure the SQL Cache Invalidation polling. Within the <databases> subsection, you can list one or more databases that you want to poll for changes. In Listing 3, the database represented by the mySqlServer is polled once a minute (every 60000 milliseconds).

You can specify different polling intervals for different databases. The server must do a little bit of work every time the database is polled for changes. If you don't expect the data in the database to change very often, then you can increase the polling interval.

5      Using SQL Cache Invalidation

You can use SQL Cache Invalidation in three scenarios: when caching the output of an entire page, when working with the DataSource controls, or when working directly with the Cache object. We'll examine these three scenarios.

1.1    Using SQL Cache Invalidation with Page Output Caching

Now that we've gotten all of the configuration steps for SQL Cache Invalidation out of the way, we can start taking advantage of it in our ASP.NET pages. One way that you can use SQL Cache Invalidation is with page output caching. Page output caching enables you to cache the entire rendered contents of a page in memory. By taking advantage of SQL Cache Invalidation, you can automatically update the cached page when, and only when, a change is made to a database table.
For example, the page in Listing 4 displays the contents of the Titles database table in a GridView control. At the top of the page, the OutputCache directive is used to cache the contents of the page in memory. The SqlDependency attribute causes the page to be updated whenever the Titles database table changes.

Listing 4. OutputCacheTitles.aspx

<%@ OutputCache SqlDependency="Pubs:Titles" 

    Duration="6000" VaryByParam="none" %>

<html>

<head runat="server">

    <title>Output Cache Titles</title>

</head>

<body>

    <form id="form1" runat="server">

    <%= DateTime.Now %>

    <asp:GridView 

      ID="grdTitles" 

      DataSourceID="SqlDataSource1" 

      Runat="Server" />    

    <asp:SqlDataSource

      ID="SqlDataSource1"

      SelectCommand="Select * FROM Titles"

      ConnectionString="<%$ ConnectionStrings:mySqlServer %>"

      Runat="Server" />

    </form>

</body>

</html> 

Notice that the SqlDependency attribute references the name of the database defined within the Web configuration file. Since we specified that the Pubs database should be polled once every minute for changes, if a change is made to the database the page in Listing 4 will be updated within a minute.

You can list more than one database and/or more than one database table for the value of the SqlDependency attribute. To create more than one dependency, simply separate each dependency with a semicolon.

5.2    Using SQL Cache Invalidation with the DataSource Control

As an alternative to using SQL Cache Invalidation with page output caching, you can use SQL Cache Invalidation directly with the DataSource controls. You should consider using SQL Cache Invalidation with the DataSource controls when you need to work with the same database data in multiple pages. The SqlDataSource, AccessDataSource, and ObjectDataSource controls all support a SqlCacheDependency property.

For example, the page in Listing 5 uses the SQL Cache Invalidation with the SqlDataSource control.

Listing 5. SqlDataSourceCaching.aspx

<html>

<head id="Head1" runat="server">

    <title>SqlDataSource Caching</title>

</head>

<body>

    <form id="form1" runat="server">

        <%= DateTime.Now %>

        <asp:GridView 

            ID="grdTitles" 

            DataSourceId="SqlDataSource1"

            Runat="server" />

        <asp:SqlDataSource 

            ID="SqlDataSource1" 

            EnableCaching="true"

            SqlCacheDependency="Pubs:Titles"

            SelectCommand="select * from titles"

            ConnectionString="<%$ ConnectionStrings:mySqlServer %>"

            Runat="server" />

    </form>

</body>

</html> 

In Listing 5, the SqlDataSource control is declared with both an EnableCaching attribute and a SqlCacheDependency attribute. The SqlCacheDependency property uses the same syntax as the OutputCache directive's SqlDependency attribute. You list the name of the database, followed by the name of the database table.

5.3    Using SQL Cache Invalidation with the Cache Object

A final option is to use SQL Cache Invalidation with the Cache object. This option provides you with the greatest degree of programmatic control over SQL Cache Invalidation.
To use SQL Cache Invalidation with the Cache object, you need to create an instance of the SqlCacheDependency object. You can use the SqlCacheDependency object when inserting a new object into the Cache with the Insert method.
For example, the page in Listing 6 displays the number of records in the Titles database table. The count is cached with a dependency on the underlying database table.

Listing 6. DisplayTitleCount.aspx (C#)

<%@ Page Language="c#" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">

    void Page_Load() 

    {

        int count = 0;

        if (Cache["TitleCount"] != null)

        {

            count = (int)Cache["TitleCount"];

        }

        else

        {

            string connectionString = 

              ConfigurationSettings.ConnectionStrings[

              "mySqlServer"].ConnectionString;

            SqlConnection con = new SqlConnection(connectionString);

            SqlCommand cmd = new 

              SqlCommand("SELECT Count(*) FROM Titles", con);

            con.Open();

            count = (int)cmd.ExecuteScalar();

            con.Close();

            Cache.Insert("TitleCount", count, 

              new SqlCacheDependency("Pubs", "Titles"));

        }

        lblTitleCount.Text = count.ToString();

    }

</script>

<html>

<head runat="server">

    <title>Display Title Count</title>

</head>

<body>

    <form id="form1" runat="server">

    <asp:Label ID="lblTitleCount" Runat="Server" />    

    </form>

</body>

</html>

6      Conclusion

Caching has a dramatic impact on the performance of database-driven Web applications. Fortunately, the ASP.NET 2.0 framework includes a number of significant new enhancements that make it easier to take advantage of caching in your applications.

The new support for SQL Cache Invalidation enables you to automatically reload database data in the cache whenever the data is modified in the underlying database. This feature provides you with all the performance benefits of caching, without the worries of stale data.