Invalidating cached result sets using SQL Server Query Notification
Anyone who has written a web application has probably experienced the cost of doing a database round trip for fetching a result set everytime a web user loads a page. In fact, handling a page request may involve more than one queries (imagine 20 drop-down boxes, each of which must be populated). A fix to this problem is caching frequently requested result sets in memory to avoid the database round trip. The cache can be invalidated periodically get a more recent version of the actual data. This technique seems to work fine if the data is not updated frequently and the application does not really need the result sets to be fresh. What would be ideal in this case is for the database to notify the cache when it gets dirty so that the application can fetch the updated result set on demand. Most database engines have the infrastructure for making this possible and it is used for maintaining materialized views. SQL Server 2005 provides a mechansim to hook into this plumbing in order to allow external applications to be notified when views are invalidated. The feature is called Query Notification.
The figure above shows typical interactions for the desired behaviour:
- Gerald sends a web-request to the web-server.
- Page handler performs a SELECT query on the database.
- Database returns a result set.
- a. Web-server generates a response page and sends it back to Gerald.
b. Web-server stores the result set in its cache. - Ivan updates the table which would affect the result of the SELECT query above.
- Database fires a query notification.
- Web-server issues the SELECT query again.
- Database returns the new result set.
- Web-server updates the cache with new result set.
- Gus requests the same web-page.
- Page handler finds cached version of the result set.
- Web-server generates a response page and sends it back to Gus.
Query notifications use Service Broker underneath to send notification messages to a subscribed service. You can subscribe for query notifications and have a custom message be sent back in the body of the notification when the query's results are invalidated. The notification message looks something like this:
<qn:QueryNotification
xmlns:qn="https://schemas.microsoft.com/SQL/Notifications/QueryNotification"
Type="change" Source="data" Info="insert">
<qn:Message>https://rushi.desai.name/catalog.aspx?Category=Software</qn:Message>
</qn:QueryNotification>
The <qn:Message/> element contains the custom payload and could be a URL, a GUID or anything you like. I tried to create a sample web application that caches result sets and uses query notifications for maintaining coherency. I have built web-apps in the past using PHP and Java Servlets, but ASP.NET is completely new territory for me. So I'm sure there are better ways of doing this. While looking at ASP.NET and ADO.NET, I realized there are at least half a dozen ways of using query notifications -- from the simplest that require no lines of code, just some declarations:
<form id="form1" runat="server">
<asp:GridView ID="grdItems" Runat="server"
DataSourceId="SqlDataSource1"/>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
EnableCaching="true"
SqlCacheDependency="CommandNotification"
ConnectionString="<%$ ConnectionStrings:mySqlServer %>"
SelectCommand="SELECT i.Name as Item, i.UnitPrice as UnitPrice, c.Name as Category FROM dbo.Item i JOIN dbo.Category c ON i.CatId=c.CatId ORDER BY i.Name"
/>
</form>
to actually implementing the service that receives the notifications. This article by Bob Beauchemin goes into the details of using Query Notifications in ADO.NET 2.0.
But for our sample, we do not use ASP.NET’s Cache object just in order to illustrate how the plumbing works underneath. I want to cache the result set in the Application’s context and so I add it to the Application.Contents hashtable. We begin with the Global.asax file that actually implements the caching logic as below:
<script runat="server">
void Application_Start(Object sender, EventArgs e)
{
Refresh();
}
void Refresh()
{
using (SqlConnection conn = new SqlConnection(
ConfigurationSettings.ConnectionStrings[
"mySqlServer"].ConnectionString))
{
// Construct the command to get Items data
SqlCommand cmd = new SqlCommand(
"SELECT i.Name as Item, i.UnitPrice as UnitPrice, " +
"c.Name as Category FROM dbo.Item i JOIN " +
"dbo.Category c ON i.CatId=c.CatId ORDER BY i.Name",
conn);
// Create a dependency on this query.
SqlDependency dependency = new SqlDependency(cmd);
// Register the event handler which will be called
// when the base tables are updated.
dependency.OnChanged +=
new OnChangedEventHandler(Dependency_OnChanged);
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet("MyDataSet");
adapter.Fill(ds, "Items");
lock (Application)
{
Application.Contents["ds"] = ds;
// To ensure that object is not garbage collected
Application.Contents["dep"] = dependency;
}
}
}
private void Dependency_OnChanged(object sender,
SqlNotificationEventArgs e)
{
Refresh();
}
</script>
The key idea is to create a SqlDependency object with the appropriate event handler to bind to the SqlCommand. When the command is executed, the user subscribes to the query notification. SqlDependency uses a default stored proc that is internally activated. The stored proc uses a TCP or HTTP channel to actually fire an event in the client process. In our event handler, we re-fetch the result set and register another SqlDependency.
The front-end webpage could simply use the object from the cache as follows:
<%@ Page Language="C#" ClassName="qncache_aspx" Debug="true" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<script runat="server">
private DataSet ds;
public void Page_Load()
{
DataSet ds;
lock (Application)
{
ds = (DataSet)Application.Contents["ds"];
}
if (ds != null)
{
grdItems.DataSource = ds.Tables[0];
grdItems.DataBind();
}
}
</script>
<html>
<head>
<title>Output Cached Items</title>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView ID="grdItems" Runat="server" />
</form>
</body>
</html>