THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

SQL Cache Dependencies

This blog has moved! You can find this content at the following new location:

http://greglow.com/index.php/2008/02/26/sql-cache-dependencies/

Published Monday, February 25, 2008 10:26 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

JX said:

and not to say, the difficulty to incoporate the use of this cache notification into the layered data access architecture.

June 6, 2008 9:03 AM
 

JX said:

The notification implementation for sql 2005 is not good at all. basically it's something that wants to cover all situations, it's too complex, its performance gain, i dont know, if any,  very limited, the extra cost in query plan, and the timeout pulse period can not be configured, and the too many constraint if you really want to use this mechanism. Basically, it's something that sounds nice, but not practical.

June 6, 2008 9:04 AM
 

Greg Low said:

Have to disagree I'm afraid. I've used it quite a bit with cache refreshing and it works a treat. I'm not sure why you suspect it has a great cost in the query plan or why you feel it's complex. From ado.net for example, you instantiate a sqldependency object, wrap it around a command and then handle the event that occurs when the data changes. It's hard to imagine it being much easier than that.

What timeout pulse period are you looking to configure? It is event driven not polled. The only restrictions are on the types of query that can be used but these are no different to those for indexed views.

June 6, 2008 9:50 AM
 

ES said:

Does this operate successfully with stored procedures?

July 8, 2008 9:19 AM
 

Edmartinez said:

Good Work Greg! Very detailed post on SQL Cache dependencies, well my opinion is bit different ASP.NET cache is good but only on a single server environment. As we try to scale out to a server farm; we start facing problems. I think instead of relying on SQL Cache dependencies in ASP.NET Cache we should go for distributed caching because it is scalable and highly reliable.

November 19, 2008 5:01 AM
 

Denis Gobo said:

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

December 31, 2008 10:37 AM
 

Rich said:

Is this something that can be done for some users and not for others. For instance wrap it in something like

if StaffID = 13 then

do cachedependecy and refresh

else

regualr db refresh

end if

Thanks!

June 10, 2009 4:42 PM
 

Greg Low said:

On a per-user basis you could decide where you get the data from (ie: cache or not) but that's certainly not the standard use case.

June 10, 2009 11:43 PM
 

Sameer Chachad said:

Hi,

This was a indeed a very good article.

There are a few things that I would like to know - What are the differences of Data Caching and SQL Cache dependancy.

We had implemented the SQL Cache dependancy in our last project but had to discontinue it as due to notifications some of the transactions were very slow.

Currently I am designing a framework for my client and I have implemented Data caching.

The problem with this feature is that, currently I am doing a POC on a single Web Server, but the application has to be deployed on the proper multi server environment (maybe web farm). If I am using Data caching then it doesnt help as multiple users would access the same cache, and if cache is invalidated, then the data would be inconsistent for some users. One approach is attaching the SessionID (UserID) with the Cache object for each user, but then if you have more number of concurrent users then I think it is not advisable to have this feature as the Server would become very heavy.

I read an article on Velocity, but cannot implement that for a POC.

Kindly suggest.

Thanks in advance.

December 17, 2009 2:03 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement