ASP.NET set cache dependency with a SqlCommand

Question

Is this an effective way to set the cache item dependent on the query?

HttpRuntime.Cache.Insert(
                "ListLanguages",
                 list,
                 new SqlCacheDependency(command),
                 DateTime.UtcNow.AddMinutes(AppConfiguration.CacheExpiration.MinimumActivity),
                 Cache.NoSlidingExpiration);

command is a SqlCommand initialized previously as:

SqlCommand command = new SqlCommand("Listlanguages", connection);

where "ListLanguages" is a stored procedure which is simply a select.

I find this an easier and more failure-proof method than aggregated cache dependency (I mean failure-proof because I don't have to aggregated the tables myself!:).

What do more experienced programmers think?

Answer 1

I don't think you need to use a stored procedure, the command can be based on the select statement that is contained within it directly.

Personally I avoid SqlCacheDependency, I'm always concerned that the query might just have something in it that the broker system its based on doesn't cope with and I can't always remember what they are. It also just seems a little too complex under-the-hood so I worry that it might be on the fragile side.

Edit

In the specific case of a user updating their profile I would have the code that updates the profile delete the cached copy.

In a more general sense I would establish an acceptable latency for receiving up-to-date info and set the absolute expiration to that.

In case of expensive SQL queries I would consider staging common summaries in other tables and have code that updates this data (such as SPs) adjust or delete the staged data.

I'm not saying I would never use SqlCacheDepencency but so far I haven't come across a scenario where its the only sensible option although I'm sure they exist. I guess such scenarios could arise where you are not in complete control of all code that may modify the database.

What is "up-to-date" anyway?

In a Web application the latest information a user can possibly see is that provided in the last response. Here are some things to consider.

• Say they have fetched something but are then interrupted by a phone call for 5 minutes. How conscious are they that the data they the return to look at is 5 minutes old and may now be out-of-date?
• The user fetches something just a few milliseconds before data is submitted that would change what they would have seen, how much of a problem is this?
• Someone is entering some new data but hasn't yet submitted it, could it be said that the data current in the database is itself out of date?

The point I'm leading to is that no matter how much cache cleverness we put into systems there is an inevitable latency and that we accept this sort of latency without giving it much thought.

With that in mind in many situations where we might feel obligated to deliver the very latest info such obligation isn't really warranted. A good example of this is SO itself. Many of the query results we see here are actually cached and its possible to see data that isn't quite in line with changes that we know we've made. However other people are unaware of our changes and it isn't critical that they see them the very second we've made them.