SQL Cache Dependency
Caching is a very useful way for you to retain pages or data across HTTP requests, so that you can reuse them without the recreating them.
In ASP.net 2.0, it makes you easier to implement data caching. SQL cache dependencies are one of ASP.net 2.0 coolest features, which allows you to create dependencies between cached items and database entities. This enables cached query results to be automatically evicted from the cache if the underlying data changed. This feature applies to SQL Server 7, 2000 and 2005.
SQL cache dependencies are more powerful when paired with SQL Server 2005
- you can set the cache dependencies are more granular
- this is not rely on polling but on query notifications from SQL Server 2005
- this require no preparation of the database
Here are the steps on how to enable the SQL cache dependency on a table.
1. Enable notifications using SQL registration tool
To enable SQL cache dependency, first you have to register the database, tables by using the Sql registration tool (C:\Windows\Microsoft.NET\Framework\v2.0.50727)
At the command prompt, type
aspnet_regsql -S [servername] -E -d [databasename] -ed -et -t [tablename]
-S = Server
-E = TrustedConnection
-d = Database
-ed = Enable caching at database
-et = Enable caching at table
-t = table
other options, -u = username, -p = password
After you run the statement, you hit refresh at your database.
You will notice there is an extra table created to store the cache data and a trigger created for the particular table. When there is changes on the table, the trigger will be fired up and write the changes to the cache table.
2. Register the notification in the Web Configuration
Enable the sql cache dependency at the web configuration file and set the poll time.
<system.web>
<caching>
<sqlCacheDependency enabled="true" pollTime="2000">
<databases>
<add name="conn" connectionStringName="conn"/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
The poll time is specifies how often the application checks to see whether the data has changed.
3. Define SQL Dependency
There are two options to define the SQL dependency
- You can specify on the OutputCache directive
<%@ OutputCache Duration="10" VaryByParam="*"
SqlDependency="[sqldependencyname]:[tablename]" %>
- or, specify directly on a datasource control
<asp:SqlDataSource EnableCaching="true" CacheDuration="Infinite"
SqlCacheDependency="[sqldependencyname]:[tablename]" ... />
After you have defined the SQL dependency, any changes that made to the database will be reflected on your web pages immediately though you have set the cache duration on the page.
This can make sure that you users always get the latest data from the server.
4. Notification-based cache invalidation
If you are using SQL Server 2005, you can use the Notification-based cache invalidation.
Unlike polling based validation, no <sqlCacheDependency> needs to be registered in your application's configuration. Furthermore, no special configuration using the aspnet_regsql.exe tool is needed.
A notification based dependency is configured on the OutputCache directive using the string CommandNotification.
<%@ OutputCache Duration="10" VaryByParam="*" SqlDependency="CommandNotification" %>
This value indicates to ASP.NET that a notification based dependency should be created for the page or datasource control.
System.Data.SqlClient.SqlDependency.Start() method must be called before the first SQL query is executed. This method could be placed in Application_Start() event in global.asax file.
void Application_Start(object sender, EventArgs e)
{
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
}
This option is much more efficient as there is no pollung going on but the ASP.net acts as client, only if there is changes, ASP.net will get notified.
You may use SQL profiler to monitor the perfomance of your database to compare before and after you apply this SQL cache dependency.