Introduction

In the previous part of this series, we've explored a typical usage of data caching - via a helper class that encapsulates the caching management. And also, we've covered one case of complex caching - file dependencies. In this third part, we will continue to explore the complex caching dependencies - SQL Cache Notification Dependencies.

As with the first two parts, all the related samples in this part have been tested against the latest ASP.NET MVC 3 RC 2.

Introducing SQL Cache Notification Dependencies

Compared with other kinds of caching item dependencies, SQL Cache Notification Dependencies is a more advanced and complex topic. You can set a cache item to expire when the results of a given SQL query change. SQL cache dependencies give you the ability to automatically invalidate a cached data object (such as a DataSet) when the related data is modified in the database.

An important point to bear in mind is the underlying plumbing is quite a bit different between SQL Server 2000 and SQL Server 2005 databases. Concretely, for SQL Server 7 and SQL Server 2000 databases, this is achieved by a polling mechanism. For SQL Server 2005 and later, however, they use the database's built-in Service Broker to avoid the need for polling.

Cache Notifications in SQL Server 2000 and SQL Server 7

ASP.NET adopts a polling model for SQL Server 2000 and SQL Server 7. But, older versions of SQL Server and other databases do not directly support this model.

With the polling model, ASP.NET keeps a connection open to the database and uses a dedicated thread to check periodically if a table has been updated. For the polling model to be effective, the polling process needs to be quicker and lighter than the original query that extracts the data.

There are a couple of things we need to take care of in order for the caching to actually work.

Enabling cache notification on the database tables

Before using SQL Server cache invalidation, you need to enable notifications for the database. To do this, Microsoft provides a command-line tool named aspnet_regsql.exe, which is located in the c: \Windows\Microsoft.NET\Framework\v2.0.50727 directory (in the case of .NET 2.0) or C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319 (in the case of .NET 4.0).

There are a couple of switches and parameters ready for you to use the tool aspnet_regsql.exe. Some of the most important parameters associated with cache notification are as follows:

-S <server>: Specifies the name of the computer running SQL Server where the database will be installed, or is already installed.

-U <login ID>: The SQL Server user ID to log in with. This option also requires the password (-P) option. But it is not necessary if you are authenticating using Windows credentials (-E).

-P <password>: The SQL Server password to log in with. This option is used together with the user ID (-U) option. But it is not necessary if you are authenticating using Windows credentials (-E).

-E: Authenticates using the Windows credentials of the currently logged-on user.

-d <database>: Specifies the name of the database to create or modify for use with application services. If the database is not specified, the default database name of "aspnetdb" is used.

-ed: Enable notifications on specified database, used together with -d <database>.

-et: Turn on the notifications on table, used together with the -t <table> parameter (to name the specified table).

In our case, to enables notifications for the Northwind database on the current server we can use the following statement:

When you take this step, a new table named SqlCacheTablesForChangeNotification is added to the Northwind database.

Figure 1: Table SqlCacheTablesForChangeNotification is added to the Northwind database

Table SqlCacheTablesForChangeNotification is generated to track changes. Essentially, when a change takes place, a record will be written into this table. The SQL Server polling queries this table.

This kind of design bears at least the following benefits:

• Because the change notification table is much smaller than the table with the cached data, it's much faster to query.
• The change notification table isn't used for other tasks, hence reading these records won't risk locking and concurrency issues.
• Multiple tables in the same database will use the same notification table, so that you can monitor several tables at once without materially increasing the polling overhead.

In fact, after the database is configured you will also notice the following five new stored procedures are added to the database:

• AspNet_SqlCachePollingStoredProcedure
• AspNet_SqlCacheQueryRegisteredTablesStoredProcedure
• AspNet_SqlCacheRegisterTableStoredProcedure
• AspNet_SqlCacheUnRegisterTableStoredProcedure
• AspNet_SqlCacheUpdateChangeIdStoredProcedure

After we create the SqlCacheTablesForChangeNotification table, we still need to enable notification support for each individual table. To do this, we still fall back upon the tool aspnet_regsql. In our case, to enable notifications for the Employees table we can use:

Executing the above command will generate the notification trigger for the Employees table.

Configure and enable ASP.NET polling

To get all of the above working we also need to add an entry into the file web.config, which tells the caching framework how to connect to your database, and other parameters like poll time and so on.

To enable the polling service, you use the <sqlCacheDependency> element in the web.config file. You set the enabled attribute to true to turn it on, and you set the pollTime attribute to the number of milliseconds between each poll. You should supply the connection string information as well.

In our case, this following web.config file checks for updated notification information every 20 seconds:

Cache dependency programming

Before the story goes on, we'd first create a stored procedure named GetTestData in the sample database Northwind.

Listing 1: Create a SP GetTestData to retrieve data

Then, run this script in SQL Server Management Studio letting the system add a stored procedure GetTestData to the sample database Northwind. After a while, we'll invoke it from the ASP.NET MVC action.

Above, we are simply to use the stored procedure GetTestData to retrieve our interested fields (right corresponding to the properties defined in the class MiniEmp) from the table Employees.

Now, let's look at how to put all the above preparation into action. First of all, let's create a sample action named PollingTest.

1. Create Action

The PollingTest action defined below just plays a role of triggering the related view.

Then, right click the above action and create a corresponding view with the same name.

2. Create the sample view

For brevity, we only put a invocation to the Html.RenderAction() helper method. Note Html.RenderAction() is very simple to learn (but with strong functionality): it can call any action method in your application, and it injects that action method's output into the HTML response.. When you do this, we call the target action the child action. Any action can invoke any number of child actions, and these in turn can invoke their own child actions. For more details about the helper method, please refer to MSDN.

Next, let's track to see how the action GetTestData is implemented.

3. Create another action GetTestData

Till now, you may have guessed that the action GetTestData plays the real vital role.

Here, first if the cached item is null we need to re-retrieve the data. Then, you see the common SQL programming in traditional ASP.NET applications. Herein, we invoke the above-defined stored procedure GetTestData to obtain the interested data. And then, via the while loop we construct a List<MiniEmp> structure - this is the real object form we want to cache. Then, by calling the method HttpContext.Cache.Insert we put the List<MiniEmp> object into the cache. Note also, by passing a SqlCacheDependency typed parameter with our target table as the mini parameter the backend database system gets notified what happens. Finally, we return a PartialView passing the cached data as the lone parameter.

4. Create the partial view MyPartialView

Now, what really interests you may be the strong type passed into the partial view, as indicated below.

5. Watching the result

Now, let's run the above view PollingTest to see what will happen. Figure 2 illustrates the initial running-time snapshot. Note the employee data list is generated by invoking the help method MyPartialView.

Figure 2: The initial running-time snapshot related to the polling sample

Then, if you press F5 or the "Refresh" button of your browser, you will notice the screen be quickly re-rendered. This owes to the buffering supported implemented above.

Till now, the story does not end. In fact, if you run the following script inside SQL Server Management Studio:

Listing 2: Create a script to insert new data into the table Employees

And then, press F5 in your browser as quickly as you can. You will find the new data get rendered onto the screen. This owes to the polling policy we adopt above.

At last, note, for simplicity, we've only focus upon the interested topic but not spent time loosely coupling the sample. And also, you can also perform the like trick using polling policy together with MVC output caching. But all these should be your homework.

Cache Notifications via Service Broker in SQL Server 2005 and SQL Server 2008

SQL Server 2005 and 2008 both support cache notification solution. The notification infrastructure is built into the database with a messaging system, called the Service Broker. The Service Broker manages queues, which are database objects that have the same standing as tables, stored procedures, or views.

Introducing Service Broker

Service Broker is a new feature in SQL Server 2005 or later that brings queuing and reliable messaging to SQL Server. Service Broker provides the "plumbing" to let you pass messages between applications, using SQL Server as the transport mechanism. Applications can use a single shared SQL Server database for this purpose or distribute their work across multiple databases. Service Broker handles all of the details of message passing, which involves a lot of concepts and components, such as message, conversation, dialog, initiator, target, conversation group, contract, queue, etc. Detailing into Service Broker deserves a thick book; you can refer to SQL Server 2005, 2008 related books. This article will only show you the basic Service Broker usage associated with cache notification.

To use cache notification in SQL Server 2005 and 2008 is much easier than the poll mechanism in SQL Server 2000 and SQL Server 7.0. In the following sample, we'll only consider SQL Server 2008 (while for SQL Server 2005 things are the same). To achieve this, you can take up the following steps.

Enabling Service Broker

Start up SQL Server Management Studio (for SQL Server 2008). To use notification support, we should make sure the target database (Northwind in our case) has the ENABLE_BROKER flag set. This is accomplished by creating and running the following script:

In fact, before running the preceding script, we can first detect whether we have enabled Service Broker upon the target database using the following command:

If the above returning result is 1 it shows Service Broker has already been enabled while the result 0 indicates Service Broker has not been enabled.

Grant permissions to your database access account

According to MSDN, the database user that executes a command that contains a notification subscription request must have been granted the SUBSCRIBE QUERY NOTIFICATIONS permission on the database where the command is executed. The following example shows the syntax for the GRANT command:

In my case, I used the following form:

A notification subscription is owned by the account public that executes the command that registers the subscription. And also, according to MSDN, the account that creates the notification must have the following permissions in the database where the query runs to successfully create a notification using SqlDependency:

• CREATE SERVICE
• CREATE QUEUE
• CREATE PROCEDURE

Note the above step is very important. If not granted, the database will not be able to receive notification of changes, as a result of which the cache will never be refreshed. I started is to use the sa account, life and death are not refreshed.

Start up SqlDependency listening

To initialize the listening service on the web server, we also need to call the static SqlDependency.Start() method. This needs to be performed only once for each database connection. A recommended place to call the Start() method is in the Application_Start() method of the global.asax file.

This method will open a new, non-pooled connection to the database. ASP.NET checks the queue for notifications using this connection. The first time you call Start(), a new queue is created with a unique, automatically generated name, and a new corresponding notification service is created for that queue.

Then, the listening begins. When a notification is received, the web server pulls it from the queue, raises the SqlDependency.OnChange event, and invalidates the cached item.

One thing to note is there is only one connection that is opened per process (even if there are multiple calls to the default Application_Start method). This connection then pulls the change notification from the server queue and raises the appropriate event.

Finally, you can use the following code to detach the listener:

Create the Action method

Now, let's create a detailed sample application to illustrate the implementation with the cache notifications in SQL Server 2008.

Let's first create the action, as shown below.

In the above code, first judge whether the interested key/value pair in the cache is null. If so, invoke the helper method UpdateCache to recreate the cache data. Finally, convert the cached data to the strongly-typed List<MiniEmp> and return to the view.

Let's next follow up the scent to look at the detailed implementation of the method UpdateCache, as shown below.

On the whole, there are several points worth noticing in the above method:

1. Create a SqlCommand object using the SQL string obtained from the web.config file.

2. Create a SqlDataAdapter object that is attached to the previously-created SqlCommand object.

3. Create and initialize an instance of the System.Web.Caching.SqlCacheDependency class, using the supplied System.Data.SqlClient.SqlCommand to create a cache-key dependency.

4. With the help of the SqlDataAdapter instance obtain the buffered DataSet object.

5. With a bit tricky LINQ to DataSet programming we obtain a List<MiniEmp> object out of the DataSet object.

6. Invoke the method HttpContext.Cache.Add to store the List<MiniEmp> object in the buffer with the key "BufferedMiniEmp".

7. We also define a CacheItemRemovedCallback typed callback function named DataDiff with which to notify application when a cached item gets expired and is removed from the System.Web.Caching.Cache.

As for the callback function DataDiff, things are simple - just to invoke the UpdateCache again.

Create the View

Right click the above action SQSERVER2008QueryNotification to create a strongly-typed view with the same name. The crucial part of the view is shown below.

The really interesting thing to you may be the last Ajax invocation statement. But, let's first look at the running-time snapshots of the above view. Figure 3 illustrates the initial snapshot.

Figure 3: The initial running-time result

If you click the button "Refresh" or press F5 (in my case I used Internet Explorer 8.0) you will find the employee list be re-rendered in no time. This is owing to the buffering mechanism established above.

If in SQL Server Management Studio you run the following script:

And then, again press the button "Refresh" or press F5 you will find the updated employee list be rendered on the screen (see Figure 4 below).

Figure 4: The updated employee list

Till now, we should be aware that with the SqlCacheDependency object operating when the data in SQL Server 2008 changes the cache gets expired automatically. This is just what the SQL caching dependencies wants to achieve.

Next, let's look at link "Clear Cache" related behind code, as listed below.

Easily seen, in the above experiment you can press the link "Clear Cache" to trigger the above method calling to clear cache explicitly. Then, you can refresh the above view again to watch what will happen.

Simple Comparison between SQL Server Polling and Query Notification

SQL Server 2005/2008's notification model is using a push modal, while the old SQL Server 2000 adopts a polling model. Then, what is the difference between the two models? Let's make a short and simple summarization.

1. Initiation

Polling is initiated by the Web application. The application executes a related statement to validate whether data is available for polling, and then initiates polling by executing the polling statement if some data is available for polling. Query notification, however, is initiated by SQL Server. The notification statement issued by the Web application just instructs the database to initiate notification in case there is a change in the result set of the statement.

2. Notified objects

Polling informs you about the actual data that has changed, while query notification informs only about the types of change in the data such as Insert, Update, and Delete.

3. Immediacy of response

In the polling policy, the data-change notification depends on the polling interval; hence, the Web applications are informed about the data changes at the end of every polling interval. The data-change notification, however, can provide instantaneous response.

4. Time consuming

In polling model, the polling has to be done continuous once in a while. In SQL Server 2005/2008's push model, then this continuous polling will be saved.

5. Easy to implement

The old SQL Server polling model is easier to incorporate into current 3-tier architecture (data access layer, business layer, and presentation layer), while the notification model is not so easy (since it will involve SQL Server 2005/2008 Query Engine, Broker Service, the system storage procedure sp_DispatcherProc, SqlNotification, SqlDependency, Cache, etc).

Please notice that plenty of practices have showed that polling works best with data that's used heavily and changes infrequently. That way, the system can minimize the overhead of the notification process.

Moreover, facts show that polling can give you better throughput in scenarios where the data changes are happening continuously, and you do not want to be notified of each change as and when it happens. Instead, you specify a polling interval after which you want to be notified of all the changes that have happened since the last change notification.

Anyway, all the above enumerations are only for your reference and all waits to be proved in real environments.

Summary

As is well-known, the best way to improve the performance of an ASP.NET MVC application is by caching. The slowest operation that you can perform in an ASP.NET MVC application should database access. The best way to improve the performance of your data access code is to avoid accessing the database at all. Caching enables you to avoid accessing the database by keeping frequently accessed data in memory.

In this article, we've mainly discussed SQL cache notification dependencies related topics, by developing two ASP.NET MVC sample applications by polling policy and query notification policy respectively. Some of the techniques in them are only for your reference in the real scenarios.

In fact, the caching stories related to ASP.NET MVC Web application development are far from over. Under more complex cases, such as you want to develop and deploy your web application using a Web farm or Web garden architecture, you have to be faced up with more complex caching tools such as distributed cache frameworks. So, the entire cache story just begins.

About Xianzhong Zhu

I'm a college teacher and also a freelance developer and writer from WeiFang China, with more than fourteen years of experience in design, and development of various kinds of products and applications on Windows platform. My expertise is in Visual C++/Basic/C#, SQL Server 2000/2005/2008, PHP+MyS... This author has published 81 articles on DotNetSlackers. View other articles or the complete profile here.