Is getting a column by name slower then ordinal?

[Smokey Grindel]

If I am doing a very large loop in a data reader say 20,000 rows being read
in... now I need to read data so I am doing the
reader.Get[Type](reader.GetOrdinal("[columnname]"))... which seems like it'd
be slow doing that x number of times per row... is it better to use the
ordinal by itself? does looking up an ordinal have any major performance
impact? should I look them up once and cache them in variables? what do you
guys thinks? thanks!

 

[William Vaughn]

Yea, a LOT slower (by name). Ah, my questions are:

1) Why aren't you using the new DataTable.Load method?
2) Why are you bringing 20,000 rows to the client? Can't you do the work on
the server?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Sheng Jiang[MVP]]

yes, ordinal is facter. You don't have to find the ordinal by name every
single time.
I suggest you look for some code generator tools for data access, such as
typed dataset, codesmith or nhibernate.

 

[Smokey Grindel]

20,000 is just an example number for benchmarking... why I am not using a
datatable.load because I need to process records as they come in for speed..
when we did the same problem with data tables our performance was reduced by
a significant amount (over 10% more time) and memory usage was an issue...
when we do data readers at least we can get rid of the data as its taken
care of what we have to on a row by row basis...

Yea, a LOT slower (by name). Ah, my questions are:

1) Why aren't you using the new DataTable.Load method?
2) Why are you bringing 20,000 rows to the client? Can't you do the work
on the server?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

If I am doing a very large loop in a data reader say 20,000 rows being
read in... now I need to read data so I am doing the
reader.Get[Type](reader.GetOrdinal("[columnname]"))... which seems like
it'd be slow doing that x number of times per row... is it better to use
the ordinal by itself? does looking up an ordinal have any major
performance impact? should I look them up once and cache them in
variables? what do you guys thinks? thanks!

 

[Smokey Grindel]

I guess I should add to my answer a little more also... we are doing
datatable.load on some smaller tables that don't take up much memory and
need to be databound... we do the reader with large data sets that we need
to perform things on that can not be done on the server or we are working in
T-SQL CLR proc's to work with which we process them row by row... we have
some logic that doesnt like to work well with sets and needs to be processed
sequentually and we want to stay far away from cursors so readers seemed the
best solution

Yea, a LOT slower (by name). Ah, my questions are:

1) Why aren't you using the new DataTable.Load method?
2) Why are you bringing 20,000 rows to the client? Can't you do the work
on the server?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

If I am doing a very large loop in a data reader say 20,000 rows being
read in... now I need to read data so I am doing the
reader.Get[Type](reader.GetOrdinal("[columnname]"))... which seems like
it'd be slow doing that x number of times per row... is it better to use
the ordinal by itself? does looking up an ordinal have any major
performance impact? should I look them up once and cache them in
variables? what do you guys thinks? thanks!

 

[Cor Ligthert [MVP]]

At least your last option

Cor