Referencing datarow/datareader values by property name vs index - How big a hit?

[W.G. Rowland]

I've read several times that there is a performance hit when referencing
column values in ADO.Net by property name vs index number. Could someone
clue me in to how serious a hit this is, and maybe point me towards an
explanation as to the extra steps involved in referencing by name that are
causing the slowdown?

i.e.

variable=datareader(3)
is faster than
variable=datareader("LastName")

I hate giving up readability in my code (my code documentation skills are
bad enough already), and I've also got a paranoia of the order of columns
somehow getting shifted around. (I know when I bind a table to a datagrid,
the column order doesn't always match the order in the table..)

Any comments?

Thanks,
W.G. Rowland

 

[William Ryan]

The answer really depends on how many columns you reference and then, how
many rows you are referencing etc. If you are using small queries, I doubt
you'll ever notice the difference. On large queries, particularly if you
have programmers that still think the flatter a table the better and if you
are using the web or compact framework where you want to squeeze every bit
of performance you can...it can be kinda ugly.

---However, you can have it both ways.

Before you reference your Columns in the reader, you can do something like
this

int LastName = myDataReader.GetOrdinal("LastName");

You can name your int values according to the Literal of the column name.
Then, when you reference your reader

while dr.Read(){
myComboBox.Items.Add(dr.GetString(LastName));

}


Now, by naming the Int Values clear names, you can have the best of both
worlds b/c the column resolution is done only once, not each pass of the
loop. Bill Vaughn also has a really cool idea in his Best Practices book
where he uses an Enumeration for each of the column names. Then, he can
reference them by name and still have the index referenced.

If you check google for DataReader.GetOrdinal, there are plenty of examples.

HTH,

Bill

 

[William \(Bill\) Vaughn]

Thanks for the plug. The differences between ordinal and string (name)
reference is significant (an order of magnitude) in ticks as I describe in
my books. Strongly typed referencing is fastest but requires more
time/trouble/code at design time. Using enumerations is a good way to get
back the performance and still have a human-readable body of code. Sure, if
you don't bind that often who cares... it does not make that much
difference. When you're working with a web page that gets invoked
100/second, every little bit adds up.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________