adam machanic now blogs at http://sqlblog.com

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<datatype> (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.