Adam Machanic has posted a month ago an article about SqlDataReader performance tips. One about using an indexer with the ordinal position, a second one about avoiding the Get<datatype> methods and a third about opening and closing the reader.

Especially the last one is something we all sometimes forget. Thanks to Adam for these tips! :)

Tip 1: 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];

Tip 2: 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.

Tip 3: "open late and close early"

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.

you can find all the tips also at his blog by following this link:

http://sqljunkies.com/WebLog/amachanic/archive/2005/04/06/10462.aspx