SqlDataReader performance tips

re: SqlDataReader performance tips

RP — Sun, 31 Aug 2008 18:44:25 GMT

Re: (B) above.

With the latest ADO.NET libraries (as of .NET 3.5), the GetXXX methods for value types (GetInt32/GetDateTime etc) are approximately 10 - 15% faster compared to the cast approach (i'm guessing because the box/unbox overhead of converting to an object and back is avoided).

The performance is roughly the same for something like GetString vs (string) reader[0] - either of which create an object on the heap anyway.

re: SqlDataReader performance tips

David — Mon, 27 Oct 2008 14:16:19 GMT

Is what RP said true?

Cause I really don't see how this could be since your sending the data from the SqlDataReader to a value type?

I wouldn't be surpised SqlDataReader would be holding objects/references in the background so I wonder how boxing/unboxing can be avoided by using hte GetXXX methods.

Thanks.

re: SqlDataReader performance tips

David — Mon, 27 Oct 2008 19:44:05 GMT

Nevermind me, apparently good pratices since ADO.NET 2.0...

re: SqlDataReader performance tips

AP — Fri, 18 Sep 2009 14:46:54 GMT

I can confirm what David is saying. We did some tests and using the GetXXX methods resulted in being 10% faster than using the reader[i] indexer method.

re: SqlDataReader performance tips

Ashish Gupta — Mon, 28 Jun 2010 11:49:49 GMT

GetXXX() methods are definitely faster than the cast.

http://support.microsoft.com/kb/310348

re: SqlDataReader performance tips

Adam Machanic — Mon, 28 Jun 2010 13:53:27 GMT

Yup, I agree that in current versions of ADO.NET the getters are a much better choice. When I originally wrote this post -- sometime in 2004 -- the casts were faster in my tests.

The other two tips are still 100% valid--use of the ordinal indexer and paying attention to releasing connections as quickly as possible are important keys for maximizing performance.

re: SqlDataReader performance tips

John Glasgow — Tue, 18 Sep 2012 20:58:13 GMT

Awesome post! I saw GetOrdinal for years but never made the connection that it was called every time. Boosted the performance of a query I was working on by about 25%.