WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

SQL キャッシュ依存関係

by WebSurfer 28. March 2011 21:44

SQL キャッシュ依存関係の検証

SQL キャッシュ依存関係 (SQL Cache Dependency) について調べて、いろいろわかったことがありますので、備忘録として書いておきます。自分なりに解釈したところもありますので、ひょっとしたら間違っているところがあるかもしれませんが。(汗)

まず、キャッシュ全般に関する予備知識として、MSDN ライブラリの ASP.NET のキャッシュの概要 に一通り目を通しておくことをお勧めします。

ASP.NET のキャッシュは、ファイル、データベースなどの他の要素に依存するように構成でき、依存する要素が変更されると、ASP.NET は関係する項目をキャッシュから削除するように設定できます。そうすることで、最新のデータをユーザーに提供するとともに、キャッシュの内容も最新に保つことができます。

SQL キャッシュ依存関係とは、ASP.NET のキャッシュと SQL Server DB のテーブルやレコードとの間に依存関係を持たせ、当該テーブル/レコードが変更されたら ASP.NET のキャッシュを削除し、次のリクエストでは新しいデータを DB から取得してユーザーに提供するとともに、新しいデータをキャッシュできるようにするための機能です。

そのために、SQL Server 側で依存する項目が変更されたら、その通知を SQL Server から ASP.NET に渡してやる必要があります。通知を渡す仕組みに以下の 2 種類があります。

  1. テーブルポーリング (table polling)
  2. クエリ通知 (query notification)

ここでは、前者の「ポーリング」を利用した場合について、その仕組みとアプリケーションの作成例を書きます。実は、「クエリ通知」の方は、SQL Server 関係の設定が難しく、まだうまくいってませんので。(笑)

ポーリングは、事前に設定したインターバルで自動的に ASP.NET から SQL Server に変更の有無を問い合わせる仕組みです。

ポーリングを行うためには、SQL Server 側と ASP.NET 側の両方に準備が必要です。

まず、SQL Server 側の準備です。SQL Server 側でポーリングによる通知を有効にするには、aspnet_regsql.exe コマンドラインツールを使用し、コマンドラインから以下のように実行します。

aspnet_regsql -S <サーバー名> -E -d <DB 名> -ed
aspnet_regsql -S <サーバー名> -E -t <テーブル名> -d <DB 名> -et

オプションのパラメータの設定方法詳細については MSDN ライブラリ を参照してください。SQL キャッシュ依存関係オプションは SQL Server 7.0, 2000, 2005 と書いてありますが、SQL Server 2008 も同じです。

aspnet_regsql.exe の実行

サーバー名のところは、例えば SQL Server をインストールしてあるコンピュータ名が papiko-pc で SQL Server が Express Edition のときは papiko-pc\sqlexpress とします。成功すると左の画像に示したようになります(クリックすると拡大画像が表示されます)。

開発環境で SQL Server Developer Eidtion と Express Edition の両方をインストールしてある場合など、インスタンスが複数ある場合、インスタンス名(上記の場合 sqlexpress)も指定するところがミソです。

省略すると既定のインスタンス(MSSQLSERVER)を指定したとみなされるらしく、Developer Edition の方に設定されてしまいます。自分はこれに気づかず、半日ぐらいハマってしまいました。(笑)

生成されたポーリング先のテーブル

aspnet_regsql.exe を実行することによって、SQL Server にポーリング先のテーブル、ポーリングに使うストアドプロシージャ、更新の検出のためのトリガを生成するストアドプロシージャなどが自動生成されます。

ポーリング先のテーブルは AspNet_SqlCacheTablesForChangeNotification という名前で、tableName, notificationCreated, changedId という 3 つのフィールドを持ちます。左の画像(クリックすると拡大表示されます)は、SQL Server Management Studio でそのテーブルを表示したものです。

生成されたストアドプロシージャ

ポーリング先のテーブルに加えて、左の画像(クリックすると拡大表示されます)に示す 5 つのストアドプロシージャが自動生成されます。

これらは SQL Server に更新状況を問い合わせたり、更新の検出のためのトリガを生成するために使用されます。

ポーリングに使うのは SqlCachePollingStoredProcedure という名前のストアドプロシージャで、以下のような内容になっています。これが web.config で設定したポーリング間隔で呼び出されます。

ALTER PROCEDURE [dbo].[AspNet_SqlCachePollingStoredProcedure] AS
SELECT tableName, changeId 
FROM dbo.AspNet_SqlCacheTablesForChangeNotification
RETURN 0

次に、ASP.NET 側の準備です。まず、web.config で caching 要素の設定を行います。以下の例では、SQL キャッシュ依存関係を構築するためにポーリングを有効にし、ポーリング間隔を 10 秒に設定しています。さらに、依存関係を持たせるデータベースを接続文字列によって指定し、その名前を Northwind に設定しています。

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

ASP.NET のキャッシュには、アプリケーションキャッシュページ出力キャッシュ およびデータソースコントロール(SqlDataSource, ObjectDataSource など)の持つキャッシュがありますが、いずれも SQL キャッシュ依存関係を構築することが可能です。

以下の例は、MDSN ライブラリの SqlCacheDependency クラス の説明に記載されていたサンプルコードに手を加えたものです。この記事の一番上の画像を表示させたものです。

Northwind データベースの Employees テーブルから DataTable を作成し、それをアプリケーションキャッシュに格納するとともに GridView で表示しています。キャッシュと SQL Server との間に依存関係を持たせ、Employees テーブルが更新されたらキャッシュを廃棄するようにしています。

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="EmployeesDataSetTableAdapters" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">   

  // SqlCacheDependency クラスのサンプル。SqlDataSource 
  // をキャッシュしてもうまくいかないので DataTable を
  // キャッシュするように変更。
    
  protected void Page_Load(object sender, EventArgs e)
  {
    string dateTimeNow = DateTime.Now.ToString();
    Label1.Text = dateTimeNow;
    Label2.Text = dateTimeNow;
        
    SqlCacheDependency dependency = null;
    string connectionString =
      ConfigurationManager.
      ConnectionStrings["Northwind2"].ConnectionString;

    if (Cache["EmployeesDataTable"] == null)
    {            
      try
      {
        dependency = 
          new SqlCacheDependency("Northwind", "Employees");
      }
      catch (DatabaseNotEnabledForNotificationException)
      {
        try
        {
          SqlCacheDependencyAdmin.
            EnableNotifications(connectionString);
        }
        catch (UnauthorizedAccessException)
        {
          Response.Redirect("ErrorPage.htm");                    
        }
      }
      catch (TableNotEnabledForNotificationException)
      {
        try
        {
          SqlCacheDependencyAdmin.
            EnableTableForNotifications(
            connectionString, "Employees");
        }
        catch (SqlException)
        {
          Response.Redirect("ErrorPage.htm");
        }
      }
      finally
      {
        EmployeesTableAdapter adapter = 
          new EmployeesTableAdapter();
        EmployeesDataSet.EmployeesDataTable table = 
          adapter.GetData();
        GridView1.DataSource = table;
        GridView1.DataBind();
        Cache.Insert("EmployeesDataTable", 
          table, dependency, 
          DateTime.Now.AddMinutes(60), 
          Cache.NoSlidingExpiration);                
      }
    }
    else
    {
      GridView1.DataSource = 
        (EmployeesDataSet.EmployeesDataTable)
        Cache["EmployeesDataTable"];
      GridView1.DataBind();           
    }
  }

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
  <title>SQL Cache Dependency</title>
  <style type="text/css">
    #UpdatePanel1
    { 
      width: 250px; 
      height: 370px; 
      border: gray 1px solid;
      margin-left: 10px; 
      margin-top: 10px;
      padding: 0 5px 0 5px;
      background-color: #eeeeee;
    }
  </style>
</head>
<body>
  <form id="form1" runat="server">
    <h1>SQL Cache Dependency</h1>
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>

    <asp:Label ID="Label1" runat="server">
    </asp:Label>        

    <asp:UpdatePanel ID="UpdatePanel1" runat="server" >
      <ContentTemplate>
        <p>UpdatePanel</p>
        <hr />
        <p><asp:Label ID="Label2" runat="server">
           </asp:Label></p>  
                
        <asp:GridView ID="GridView1" 
          runat="server" 
          AutoGenerateColumns="False" 
          DataKeyNames="EmployeeID"
          EnableModelValidation="True" 
          EnableViewState="False" 
          BackColor="White">
          <Columns>
            <asp:BoundField 
              DataField="EmployeeID" 
              HeaderText="EmployeeID" 
              InsertVisible="False" 
              ReadOnly="True" 
              SortExpression="EmployeeID" />
            <asp:BoundField 
              DataField="LastName" 
              HeaderText="LastName" 
              SortExpression="LastName" />
            <asp:BoundField 
              DataField="FirstName" 
              HeaderText="FirstName" 
              SortExpression="FirstName" />
          </Columns>
        </asp:GridView>

        <asp:Button ID="Button1" 
          runat="server" 
          Text="Refresh Panel" />
 
      </ContentTemplate>
    </asp:UpdatePanel>        
  </form>
</body>
</html>

aspnet_regsql.exe を使わなくても、SqlCacheDependencyAdmin クラスのメソッドを呼び出して SQL キャッシュ依存関係を動的に設定することができるそうですが、上記にはそのためのコードが含まれています。

ただし、SQL Server データベースへのアクセスで使用するアカウント(ワーカープロセスのアカウント。IIS6, 7 のデフォルトで NETWORK SERVICE)に、テーブルおよびストアドプロシージャを作成できる権限が必要です。さらに、通知を有効にするテーブルでトリガを作成できる権限が必要です。その設定には SQL Server の管理者特権が必要で、SQL Server の設定に詳しくないと難しそうです。

Tags:

Cache

About this blog

2010年5月にこのブログを立ち上げました。その後 ブログ2 を追加し、ここは ASP.NET 関係のトピックス、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  October 2020  >>
MoTuWeThFrSaSu
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678

View posts in large calendar