Depends on ASP.NET cache using SqlCommand
Is this an efficient way to set a query-specific cache item?
HttpRuntime.Cache.Insert(
"ListLanguages",
list,
new SqlCacheDependency(command),
DateTime.UtcNow.AddMinutes(AppConfiguration.CacheExpiration.MinimumActivity),
Cache.NoSlidingExpiration);
is a SqlCommand, initialized earlier as:
SqlCommand command = new SqlCommand("Listlanguages", connection);
where "ListLanguages" is a stored procedure that is just a selection.
I find this to be a simpler and more fault tolerant method than the aggregated cache dependency (I mean fault tolerance because I don't have to aggregate tables myself! :).
What do more experienced programmers think?
source to share
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.
Personally, I avoid SqlCacheDependency, I am always worried that the request may have something in it that the broker system based on it cannot cope with, and I cannot always remember what it is. It also seems a little too complicated under the hood, so I'm worried it might be on the fragile side.
Edit
In the specific case of a user updating their profile, I will have a code that updates the profile, deletes the cached copy.
More generally, I would set an acceptable delay for getting actual information and set an absolute expiration for it.
In the case of expensive SQL queries, I would consider creating general summaries in other tables and have code that updates that data (like SP), tweak or delete staged data.
I am not saying that I will never use SqlCacheDepencency, but so far I have not come across a scenario where its the only reasonable option, although I'm sure they exist. My guess is that scenarios like this can arise where you don't have complete control over all the code that can change the database.
What is "updated"?
In a web application, the most recent information the user can see is what is stated in the last answer. Here are some things to consider.
- Say they brought something, but then they are interrupted by a phone call for 5 minutes. How conscious are they that the data they return to view is 5 minutes long and might now be out of date?
- The user is getting something just a few milliseconds before the data is sent, which would change what they see, how much of a problem is that?
- Someone is entering some new data but haven't submitted it yet, is it safe to say that the current data in the database itself is out of date?
What I'm getting at is that no matter how many cache skills we put into systems, there is inevitable latency, and we accept that latency without giving it much thought.
Given this, in many situations where we may feel obligated to provide the most up-to-date information, such an obligation is not really justified. CA is a good example of this. Many of the query results we see here are actually cached and allow us to see data that doesn't quite match the changes we know we have made. However, other people are not aware of our changes, and it does not matter that they see them the first second we create them.
source to share