locked
Parameter caching RRS feed

  • Question

  • User-1638510185 posted
    The parameters for a stored proc are obtained (via SqlCommand.DeriveParameters) and stored in a syncronized Hashtable. Does anyone how to flush this Hashtable. I changed the parameters of a stored proc, and am having trouble rerunning the sp because the parameters don't match. The Hashtable must be stored outside of the areaa of the executing program.
    Friday, June 17, 2005 5:02 PM

All replies

  • User1416329745 posted

    You mean SQL Server Procedure Cache, it is not a hashtable because it is almost 1gig in size 800 megs.  If you want to change a stored proc you use ALTER Procedure and yes you can flush the Procedure Cache with DBCC FREEPROCCACHE and all your stored procs will need to be recompiled.  It is not something to do because ALTER will change only that one which needs to be recompiled.  Try the link below.  Hope this helps


    http://www.novicksoftware.com/Articles/minimizing-stored-procedure-recompilation-page2.htm

    Friday, June 17, 2005 6:54 PM
  • User-1638510185 posted
    Unfortunately, I don't mean the Sql Server procedure cache. If you look at the code in the MS Data Access App Block, you'll see that it's using a hashtable to store the SP parameters (using DeriveParameters method). This hashtable is getting cached in ASP.Net.
    I've just written my code, eliminating the Data Access App Block because of this issue.

    Monday, June 20, 2005 4:10 PM
  • User-1027516314 posted
    The parameters for a stored proc are obtained (via SqlCommand.DeriveParameters) and stored in a syncronized Hashtable. Does anyone how to flush this Hashtable. I changed the parameters of a stored proc, and am having trouble rerunning the sp because the parameters don't match. The Hashtable must be stored outside of the areaa of the executing program.


    There is one way you can try to get it to reload your new storedproc again from the database. Even though I'm not 100% certain about this, but by reading the source code, logics tells me that it should work. The 3 classes you should look into are ParameterCache. CachingMechanism and SqlCommandWrapper to get an idea of what the heck is going in there. They do provide a method called Clear to empty the cache in ParameterCache class, unfortunately, it's internal and therefore you have no access from a different namespace. SqlCommandWrapper is indeed public, but again its constructor is declared internal which is not usable outside the namespace. Now, that's a real pain in the ass. The only publicly available method from the ParameterCache class is FillParameters(DBcommandWrapper Command, char ParameterToken). This method simply loads parameters from cache if the DBCommandWrapper you pass in already exists in the cache. Otherwise, it will make a round-trip to the database to query the parameters. I believe this is the behavior you're looking for right?. Bad news again, you have to pass in a new DBCommandWrapper which doesn't exist in the cache. The only way I'm aware of to properly create a new DBCommandWrapper is to take a long walk through the factory process the way they show you:

    ........
    Database db = DatabaseFactory.CreateDatabase();
    string sqlCommand = "GetProductDetails"; // StoredProc name
    DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand);
    ..........

    I was looking for 1 to 2 line codes to get the job done and I got into the exercise of having to read so many of their source codes to discover that there is no easier and simpler way. I do question their design of not exposing more their internal mechanism to the public to make life more easier for us.

    Hope it helps
    Tuesday, June 21, 2005 9:40 AM
  • User-1027516314 posted
    I digged a little bit digger into the Database class, there is a method called ClearParameterCache() to clear all parameters cache for all database. So you can make this call Database.ClearParameterCache();
    Tuesday, June 21, 2005 7:39 PM
  • User-1638510185 posted
    You must be using a newer version of the DAAB. Mine doesn't have ClearParameterCache().
    Tuesday, July 5, 2005 3:09 PM