SqlCommand.ExecuteReader takes too much time

Hi,

I have a table containing about 14,5 million records and I want to
iterate over the complete data. If I execute a "select * from
mytable", the call to ExecuteReader takes some hours to return. In T-
SQL I can open a cursor and start reading immediately. As far as I
understand, ExecuteReader/SqlDataReader should behave the same way -
but it seems like I'm wrong. Any hint?

regards,
Achim

Mary
Wed Apr 16 09:23:22 CDT 2008

What are you doing with 14 million rows? There are better ways to
export data from SQL Server. If you really want to use a reader, then
supply the nolock query hint to speed things up. See
http://blogs.msdn.com/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx
for more information.

--Mary

On Wed, 16 Apr 2008 01:04:21 -0700 (PDT), Achim Domma
<domma@procoders.net> wrote:

>Hi,
>
>I have a table containing about 14,5 million records and I want to
>iterate over the complete data. If I execute a "select * from
>mytable", the call to ExecuteReader takes some hours to return. In T-
>SQL I can open a cursor and start reading immediately. As far as I
>understand, ExecuteReader/SqlDataReader should behave the same way -
>but it seems like I'm wrong. Any hint?
>
>regards,
>Achim

Cor
Wed Apr 16 12:38:58 CDT 2008

Achim,

Are you starting your "T-SQL", whatever you mean with that as the
DataReader uses as well T-SQL, from the same machine as your DataReader?

Cor

"Achim Domma" <domma@procoders.net> schreef in bericht
news:4965387f-2188-48ee-ba42-eda9368b4af5@y21g2000hsf.googlegroups.com...
> Hi,
>
> I have a table containing about 14,5 million records and I want to
> iterate over the complete data. If I execute a "select * from
> mytable", the call to ExecuteReader takes some hours to return. In T-
> SQL I can open a cursor and start reading immediately. As far as I
> understand, ExecuteReader/SqlDataReader should behave the same way -
> but it seems like I'm wrong. Any hint?
>
> regards,
> Achim

William
Wed Apr 16 21:12:21 CDT 2008

I'm with Mary. ADO.NET is NOT designed to provide a bulk data interface. I
have yet to see very many legitimate reasons to move that many rows to the
client. Processing that requires touching all the rows should be done on the
server if at all possible. Remember that if REALLY necessary (when TSQL is
not suitable) you can execute CLR procedures in VB.NET or C#. SELECT * is
also discouraged as it brings columns that you might not need. TSQL is no
different but consider that the operation is NOT returning any more than a
small cache of rows that is refreshed as you move through the rowset.

--
__________________________________________________________________________
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)
____________________________________________________________________________________________
"Achim Domma" <domma@procoders.net> wrote in message
news:4965387f-2188-48ee-ba42-eda9368b4af5@y21g2000hsf.googlegroups.com...
> Hi,
>
> I have a table containing about 14,5 million records and I want to
> iterate over the complete data. If I execute a "select * from
> mytable", the call to ExecuteReader takes some hours to return. In T-
> SQL I can open a cursor and start reading immediately. As far as I
> understand, ExecuteReader/SqlDataReader should behave the same way -
> but it seems like I'm wrong. Any hint?
>
> regards,
> Achim

Cor
Wed Apr 16 22:32:06 CDT 2008

> I'm with Mary.

That Summit brings everybody together.

:-)

Cor

> __________________________________________________________________________
> 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)
> ____________________________________________________________________________________________
> "Achim Domma" <domma@procoders.net> wrote in message
> news:4965387f-2188-48ee-ba42-eda9368b4af5@y21g2000hsf.googlegroups.com...
>> Hi,
>>
>> I have a table containing about 14,5 million records and I want to
>> iterate over the complete data. If I execute a "select * from
>> mytable", the call to ExecuteReader takes some hours to return. In T-
>> SQL I can open a cursor and start reading immediately. As far as I
>> understand, ExecuteReader/SqlDataReader should behave the same way -
>> but it seems like I'm wrong. Any hint?
>>
>> regards,
>> Achim
>

Achim
Wed Apr 23 07:08:23 CDT 2008

On 17 Apr., 05:32, "Cor Ligthert[MVP]" <notmyfirstn...@planet.nl>
wrote:

> That Summit brings everybody together.

Hi,

thanks to you all for your feedback. I'll think about redesigning my
process.

regards,
Achim