Is getting a column by name slower then ordinal?

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!

Sheng
Fri Feb 29 17:13:47 CST 2008

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.
--
Sheng Jiang
Microsoft MVP in VC++
"Smokey Grindel" <nospam@nospam.com> wrote in message
news:O9tlkwxeIHA.5160@TK2MSFTNGP05.phx.gbl...
> 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
Fri Feb 29 16:49:12 CST 2008

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)
____________________________________________________________________________________________
"Smokey Grindel" <nospam@nospam.com> wrote in message
news:O9tlkwxeIHA.5160@TK2MSFTNGP05.phx.gbl...
> 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
Sat Mar 01 14:37:47 CST 2008

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

"William Vaughn" <billvaNoSPAM@betav.com> wrote in message
news:328B9B58-34FD-42E7-8567-858A1D1DFC15@microsoft.com...
> 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)
> ____________________________________________________________________________________________
> "Smokey Grindel" <nospam@nospam.com> wrote in message
> news:O9tlkwxeIHA.5160@TK2MSFTNGP05.phx.gbl...
>> 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
Sat Mar 01 15:06:36 CST 2008

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

"William Vaughn" <billvaNoSPAM@betav.com> wrote in message
news:328B9B58-34FD-42E7-8567-858A1D1DFC15@microsoft.com...
> 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)
> ____________________________________________________________________________________________
> "Smokey Grindel" <nospam@nospam.com> wrote in message
> news:O9tlkwxeIHA.5160@TK2MSFTNGP05.phx.gbl...
>> 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
Wed Mar 05 06:00:44 CST 2008

At least your last option

Cor

"Smokey Grindel" <nospam@nospam.com> schreef in bericht
news:O9tlkwxeIHA.5160@TK2MSFTNGP05.phx.gbl...
> 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!
>