Technical Concept
Author: Minh Nguyen
Version 0.1
Status: new
Author
| AUTHOR | FIRM/DEPARTMENT |
| Minh Nguyen | Software Development |
HISTORY
| VERSION | DATE | CHANGES |
| 0.1 | 14.09.2006 | First draft |
OPEN ISSUES
Content
1 Introduction 4
2 How to use 4
2.1 Web.config 4
Above is the a piece configuration in Web.config file. 5
2.2 Enable Service Broker in SQL Server 2005 5
2.2.1 Create Service Broker endpoint 5
2.2.2 Enable Service Broker for a database 5
2.3 Enable Query Notification in SQL Server 2005 6
2.3.1 Enable Query Notification in SQL Server 2005 by Command Line 6
2.3.2 Enable Query Notification in SQL Server 2005 by Programming 6
2.4 Code using 7
3 Some issues 7
3.1 Performance 7
3.2 SQL Command 7
3.3 Supported SELECT Statements 8
3.4 Duplicate Subscriptions 9
4 Reference 9
In ASP.NET 2.0, caching has been improved in a couple of notable ways. Probably the most interesting feature is the introduction of database-triggered cache invalidation. In ASP.NET 1.x, you can invalidate a cached item based on some pre-defined conditions such as change in an XML file or change in another cache item. Using this feature, you can remove or invalidate an item from the cache when the data or another cached item changes. However, the ASP.NET 1.x Cache API does not allow you to invalidate an item in the cache when data in a SQL Server database changes. This is a very common capability most applications will require. ASP.NET 2.0 addresses this by providing the database triggered cache invalidation capability to ensure that the items in the cache are kept up-to-date with the changes in the database. You can accomplish this using any one of the following methods.
· Declarative Output caching - This is similar to declarative output caching in ASP.NET 1.x, wherein you configure caching by specifying the OutputCache directive and their related attributes.
· Programmatic Output caching - In this method, you will use the SqlCacheDependency object programmatically to specify the items to be cached and set their attributes.
· Cache API - In this option, you will use the static methods of the Cache class such as Insert, Remove, Add and so on to add or remove items from the ASP.NET cache, while still using the SqlCacheDependency object to trigger the cache invalidation.
Another important caching feature in ASP.NET 2.0 is the ability to create custom cache dependencies, which is not possible with ASP.NET 1.x Cache API. To accomplish this, you need to inherit from the CacheDependency class. Since the CacheDependency is a sealed class in ASP.NET 1.x, you can't inherit and extend it. However, in ASP.NET 2.0, this is no longer the case. You can inherit from CacheDependency class and create your own custom cache dependencies. This opens up a world of opportunities where you can roll your own custom cache dependencies required for a particular class of applications. For example, you can create a StockPriceCacheDependency class that automatically invalidates the cached data when the stock price changes.
This document will mention to SQL Server 2005 only, the DBMS that use in the project.
The main points are:
· How to use: explain some important features, notices and example coding to use SqlCacheDependency.
· Some issues: answer some technical question about this class.
To use this class, you need to configure some setting on SQL Server 2005; this is optional step, Web Application configuration and use code as below sample.
In order to let the Web application understand and run the SqlCacheDependency modules, below configuration need to add to Configuration file (Web.config, app.config).
...
<connectionStrings>
<add name="LP_Shop_Instance" connectionString="Database=LP_Shop_Test;Server=172.16.6.3SQL2005;uid=lp_shop;pwd=lp_shop;" providerName="System.Data.SqlClient"/>
connectionStrings>
...
<system.web>
<caching>
<sqlCacheDependency enabled="true" pollTime="1000">
<databases>
<add name="LP_Shop_Instance" connectionStringName="LP_Shop_Instance" pollTime="1000"/>
databases>
sqlCacheDependency>
caching>
....
system.web>
- ConnectionString: represent the connection string to database
- pollTime: the interval polling in millisecond, if no invalidation occurs
- connectionStringName: the name of ConnectionString declare in ConnectionString section
To use the SqlCacheDependency correctly, the Service broker must be enable.
Use SQL Server Management Studio or a similar tool to execute the following command to create a Service Broker endpoint:
USE master;
GO
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS );
GO
Enable Service Broker for a given database (for example, LP_WebShopLabel_DB) with this command:
ALTER DATABASE LP_WebShopLabel_DB SET ENABLE_BROKER;
GO
Now you can use SQL cache dependencies against the LP_WebShopLabel_DB database.
Once you configure the table to send notifications, any time data in the table changes, it notifies ASP.NET to invalidate the specific item in the cache. For the purposes of this article, consider the aspnet_regsqlcache utility to configure the tables. Basically this utility creates an extra table named AspNet_SqlCacheTablesForChangeNotification that is used to keep track of the changes to all the monitored tables in the database. It also creates a number of triggers and stored procedures to enable this capability.
There are 2 ways to enable the Query Notification in SQL Server 2005:
- By aspnet_regsqlcache utility
- By programming
To run the aspnet_regsqlcache utility, open up the Visual Studio .NET command prompt and enter the command shown in the following screenshot.
![]()
In the above command:
S - Name of the Server
U - User ID to use to connect to the SQL Server
P - Password to use to connect to the SQL Server
d - Specifies the name of the database
t - Table to configure
et - enables the tables for SQL Server database triggered invalidation
With SQL Server 2005, the above configurations are not necessary. Moreover the cache invalidating mechanism works through a highly efficient notification model, wherein the Notification Delivery Service component of SQL Server directly notifies IIS using TCP Port 80 when the data in a SQL Server changes.
To enable the Query notification in SQL Server 2005, in the code line, add the following code:
SqlCacheDependencyAdmin.EnableNotifications(DB.CurrentConnectionString); SqlCacheDependencyAdmin.EnableTableForNotifications(DB.CurrentConnectionString, "CMRC_Products");
In the above example,
- The SqlCacheDependency is the member of namespace: System.Web.Caching
- DB.CurrentConnectionString represents the ConnectionString to Database.
- “CMRC_Products” is the name of the table need to be enable Query notification.
This code of line will enable the table CMRC_products for any change will be notified to the web application.
We also can get the list of table has notification enabled by:
SqlCacheDependencyAdmin.GetTablesEnabledForNotifications();
After enable the table need to be notify, we can use it in the code.
Example: on Page_Load event:
DataSet ds;
ds = (DataSet)Cache["Customer"];
if (ds == null)
{
System.Data.SqlClient.SqlCommand sqlCmd = new
System.Data.SqlClient.SqlCommand();
sqlCmd.CommandText = "Select ProductID, Product From dbo.CMRC_Products";
sqlCmd.Connection = new
System.Data.SqlClient.SqlConnection(DB.CurrentConnectionString);
SqlCacheDependencyAdmin.EnableNotifications(DB.CurrentConnectionString); SqlCacheDependencyAdmin.EnableTableForNotifications(DB.CurrentConnectionString, "CMRC_Products");
SqlCacheDependency dependency = new SqlCacheDependency(sqlCmd);
Database db = DatabaseFactory.CreateDatabase(DB.DatabaseInstance);
DbCommand dbCommand = db.GetStoredProcCommand(sqlCmd.CommandText
ds = db.ExecuteDataSet(dbCommand);
this.Cache.Insert("Products", ds, dependency);
Label1.Text = "Page created on: " + DateTime.Now.ToString();
}
else
Label1.Text = "
Get from cache";
GridView1.DataSource = ds;
GridView1.DataBind();
SqlCacheDependency improves the performance of the ASP .NET website a lot. Especially the feature notification help the site update the latest version of data from the Database immediately.
When try to run the Query Profiler to monitor the traffic between the Database Server and the application using the SqlCacheDependency and with the application without using SqlCacheDependency, the performance is not increase so much, but the traditional caching method will not update the data immediately but after an interval.
Question: If in 1000 records of data, one record change while the data is in cache, the SqlCacheDependency is enough intelligent to replace only the record changed in the cache or all of data?
Answer: All the data will be pulled out. But the duration will be reduced a lot.
In general, you can request notification for any query that can be used to create an indexed view. You can set up notifications for the following statements:
- SELECT
For requirements and limitations specific to SELECT, see "Supported SELECT Statements" below. For more information on the SELECT statement. - EXECUTE
In this case, SQL Server registers a notification for the command executed rather than the EXECUTE statement itself. The command must meet the requirements and limitations for a SELECT statement. For more information on the EXECUTE statement.
When a command that registers a notification contains more than one statement, the Database Engine creates a notification for each statement in the batch.
If a subscription request is made for a batch or stored procedure, a separate subscription request is made for each statement executed within the batch or stored procedure.
EXECUTE statements will not register a notification, but will flow the notification request to the executed command. If it is a batch, the context will be applied to the executed statements and the same rules described above apply.
But, when I run the SQL Profiler, I found that when registering Execute notification, I found it sometime does not works until the Cachce expired.
Query notifications are supported for SELECT statements that meet the following requirements:
- The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
- The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
- The statement may not use unnamed columns or duplicate column names.
- The statement must reference a base table.
- The statement must not reference tables with computed columns.
- The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.
- A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
- The statement must not include PIVOT or UNPIVOT operators.
- The statement must not include the INTERSECT or EXCEPT operators.
- The statement must not reference a view.
- The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
- The statement must not reference server global variables (@@variable_name).
- The statement must not reference derived tables, temporary tables, or table variables.
- The statement must not reference tables or views from other databases or servers.
- The statement must not contain subqueries, outer joins, or self-joins.
- The statement must not reference the large object types: text, ntext, and image.
- The statement must not use the CONTAINS or FREETEXT full-text predicates.
- The statement must not use rowset functions, including OPENROWSET and OPENQUERY.
- The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
- The statement must not use any nondeterministic functions, including ranking and windowing functions.
- The statement must not contain user-defined aggregates.
- The statement must not reference system tables or views, including catalog views and dynamic management views.
- The statement must not include FOR BROWSE information.
- The statement must not reference a queue.
- The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).
- The statement can not specify READPAST locking hint.
- The statement must not reference any Service Broker QUEUE.
- The statement must not reference synonyms.
- The statement must not have comparison or expression based on double/real data types.
Submitting a duplicate of an active subscription causes the existing subscription to be renewed using the new specified time-out value. A duplicate subscription is one that meets the following conditions:
- The query is submitted by the same user under the same database context.
- The same template, parameter values, notification ID, and delivery location are used.
This means that if a notification is requested for identical queries, only one notification is sent. This applies to a query duplicated in a batch, or to a query in a stored procedure that is called multiple times.
