How to safely and effectively cache ADO.NET commands?

Question

I want to use this pattern:

SqlCommand com = new SqlCommand(sql, con);
com.CommandType = CommandType.StoredProcedure;//um
com.CommandTimeout = 120;
//com.Connection = con;  //EDIT: per suggestions below

SqlParameter par;
par = new SqlParameter("@id", SqlDbType.Int);
par.Direction = ParameterDirection.Input;

com.Parameters.Add(par);

HttpContext.Current.Cache["mycommand"] = com;

Obviously I don't want to run into odd problems like person A retrieving this from the cache, updating param1, person 2 getting it from the cache and updating param2 and each user running the command with a blend of the two.

And cloning the command taken out of the cache is likely more expensive that creating a new one from scratch.

How thread safe is the ASP.NET Cache? Am I missing any other potential pitfalls? Would this technique work for parameterless commands despite threading issues?

Clarefication: If I want to metaphorically shoot myself in the foot, how do I aim? Is there a way to lock access to objects in the cache so that access is serialized?

Answer 1

The Cache itself is thread-safe but that doesn't confer thread-safety on the objects that you place within it. The SqlCommand object is not Thread-safe and therefore not the sort of thing you would want to cache.

The most important thing in this scenario is the caching of the connection which is handled for you and you should not attempt to look after this yourself.

The creation of command object (even one with many parameters) is still going to be peanuts compared with its execution. Unless you have evidence to the contray do not attempt to cache them.

The biggest risk to your project is premature optimisation.

Answer 2

Quite simply: don't. If you can't see that this is wrong, you need to read up more on ADO.NET. There is plenty of literature that explains the right way to do it: just create connections and commands when you need them, and make sure you dispose them properly.

Answer 3

As others have stated, this is just an all around bad idea. There are a number of reasons why it is a bad idea.

More than anything, if you are in a high load situation, storing the command for each and every user is going to really quickly fill up the cache, and depending on priorities, etc, will start to cause other items to fall out of the cache, that should REALLY still be there.

With ADO.NET you really should be creating, using, then disposing of your commands and connections as you use them. Performance wise I have NEVER had to change this system.....and I have not really heard of many others that have as well.

Also, as others mentioned with your code sample, the connection, which is needed to actually execute would be lost anyway.

Answer 4

Why would you ever cache the command? The overhead on the creation of a command is minuscule-you're just newing up a couple of objects and setting some properties. I can't ever see that being a bottleneck..

You want to cache the results of the command, as actually executing the command is (relatively) expensive. And, iin general, you want to treat shared cache as readonly so that you don't have to worry about locking and synchronizing access. Caching the results achieves that.

Answer 5

Cache your results, and only create the connection (and command) if the resultcache is null:

PsuedoCode:

result = getResultFromCache(CacheKey)
if (result == null)
{
     result = getResultFromDB();
     InsertIntoCache(result,cacheKey);
}

return result;

Answer 6

I should have asked how to lock an item in ASP.NET cache, instead of saying what I was intending to put in the cache.

lock(Cache)
  {
    // do something with cache that otherwise wouldn't be threadsafe
  }

Reference: http://www.codeguru.com/csharp/.net/net_asp/article.php/c5363