THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

SqlDataReader performance tips

I just posted a few SqlDataReader performance tips in response to a newsgroup post; I think they're some pretty good tips, so I'll repeat them here.

These tips were gleaned from using both Lutz Roeder's Reflector and Compuware's DevPartner Profiler Community Edition.

Both of these packages are free, excellent additions to the toolbox of anyone who wants to write high-performance .NET code. The Reflector will show you what all of those library routines are really doing under the covers (for instance, did you know that the DataView's FindRows() method does a linear search?) And the Profiler will show you how long each line of code takes to execute -- accurately, down the microsecond. Very useful for figuring out where your bottlenecks are!

Anyway, on to the tips... Pretty basic stuff, but good to keep in mind:

A) Use an indexer with the ordinal position:

Instead of:

 

while (reader.read())
object x = reader.GetValue("ColX");

or

 

while (reader.read())
object x = reader["ColX"];

do:

 

int ColX = reader.GetOrdinal("ColX");
while (reader.read())
object x = reader[ColX];

Under the covers, the string-based indexer and GetValue both call GetOrdinal -- so if you're looping through a large rowset GetOrdinal might be called every single time. Instead, only call it once.

B) Avoid the Get (e.g. GetInt32) methods at all costs, and use static casts instead of converts:

Bad:

int x = reader.GetInt32(ColX);

Better:

 

int x = Convert.ToInt32(reader[ColX]);

Best:

 

int x = (int)reader[ColX];

For maximal performance return the SQL Server equivalent of whatever datatype you'll be casting to (so that you can avoid the Convert methods.) And avoid NULLs so that you don't have to check for DBNull on the client.

C) Finally, just like in classic ADO, when using a SqlDataReader, "open late and close early" is the way to go. Open your connection at the last possible moment and close it as soon as you're done reading the data -- this will maximize connection pool availability.


Published Wednesday, July 12, 2006 10:42 PM by Adam Machanic
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

RP said:

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.

August 31, 2008 2:44 PM
 

David said:

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.

October 27, 2008 10:16 AM
 

David said:

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

October 27, 2008 3:44 PM
 

AP said:

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.

September 18, 2009 10:46 AM
 

Ashish Gupta said:

GetXXX() methods are definitely faster than the cast.

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

June 28, 2010 7:49 AM
 

Adam Machanic said:

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.

June 28, 2010 9:53 AM
 

John Glasgow said:

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%.

September 18, 2012 4:58 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement