Shane Duffy

SQL Caching

Posted on: July 28, 2007

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 2.0, it makes you easier to implement data caching. SQL cache dependencies are one of 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.
sqlCacheDependency enabled=true pollTime=2000>
add name=conn connectionStringName=conn/>

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)

This option is much more efficient as there is no pollung going on but the acts as client, only if there is changes, 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.

2 Responses to "SQL Caching"

Nice post, very help full.

Many Thanks,

Hi you,

I am using SqlCacheDependency to know data changed in server. But if i only want to set cache to null on insert new data into table not update or delete. How can i do?
Sorry for my english.

Thanks so much.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Subscribe to this blog now!

July 2007
« Jun   Aug »
%d bloggers like this: