Wednesday, October 18, 2006
Pass Parameters as Objects to MS Data Access Application Blocks
But in fact, you'll get a performance boost in addition to easier coding, by calling the overload that uses object. That's because the parameters that get created on the first call to that proc or query get cached, so that the SqlHelper object doesn't have to create the SqlParameter objects anew, but only set their values and then give them to the SqlConnection object.
Here's a snippet from the Informit .Net Reference Guide:
Many of the SqlHelper methods query the database to discover stored procedure parameters. This is a huge benefit in that it prevents you from having to write code that creates SqlParameter arrays, but it's woefully inefficient if you do it for every stored procedure call. The SqlHelperParameterCache is a way to provide the convenience of parameter discovery while minimizing the cost. When you call one of the methods that does parameter discovery, SqlHelper checks the parameter cache to see if there has already been a call to that particular stored procedure on the supplied connection or connection string. If so, the parameter types are retrieved from the in-memory cache. If not, the SqlHelperParameterCache object discovers the parameters and then caches them. The result is that the first call to a given stored procedure incurs the cost of discovering the parameters, but subsequent calls get them from the cache, almost free. The cost of getting the parameters from the cache is less than creating the parameter array yourself for each call. The initial hit is expensive, but amortized over the application's lifetime, it's not expensive at all.