DataReader ordinal-based lookups vs named lookups

Question

Microsoft (and many developers) claim that the SqlDataReader.GetOrdinal method improves the performance of retrieving values from a DataReader versus using named lookups ie. reader["ColumnName"]. The question is what is the true performance difference if dealing with small, paged record sets? Is it worth the extra overhead of finding and referencing ordinal indexes throughout the code?

Answer 1

Microsoft recommends not calling GetOrdinal within a loop.

That would include indirect calls with the string indexer.

You can use GetOrdinal at the top of your loop put the ordinals in an array and have the indexes in the array be const or have an enum for them (no GetOrdinal at all) or use GetOrdinal into individual variables with descriptive names.

Only if your sets are small would I really consider this to be premature optimization.

It's apparently a 3% penalty.

Answer 2

Any difference will be more than outweighed by maintenance overhead.

If you have that much data that it makes a noticeable difference, I'd suggest you have too much data in your client code. Or this is when you consider use ordinals rather than names