Wednesday, October 18, 2006


Pass Parameters as Objects to MS Data Access Application Blocks

Intuition seems too state that it's better to call the SqlHelper overloads that take SqlParameter[] for the parameters to a stored procedure or query.

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.

Good tip... but where's the sample code? :)
I'd love to see a quick and dirty perf comparison after the caching has been performed to see what the difference is, out of curiosity.
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?