Poor performance of ADO.NET SQLDataReader

[Nick]

We try to migrate our ASP appliations to ASP.NET and
expereance some strange performance drops when replacing
ADO ODBC recordset queries by ADO.NET DataReader Objects.
Especialy reading a single row from a large table (>
4.000.000 data rows) by selecting the row using the
primary key. It is 50 to 100 times faster in ADO ODBC than
in managed ADO.NET (Same Query statement, same SQL
Server...). It seems to be a BUG in the implementation of
the SQLDataAdapter in .net.

Any hints and work arounds / (or BUG fixes) apreciated.

 

[William Ryan]

Nicolas:

Can you post the code snippet. Unless it was a typo,
DataAdapters and DataReaders are two different beasts
that aren't used for the same operation...you can fire a
datareader with only a command and connection object.

To this end, I've probably used them on well over 2000+
different queries and I've never seen them be slow, even
on some pretty large queries.

However, if you post the code, it might be eaiser to
identify.

Cheers,

Biii

 

[William \(Bill\) Vaughn]

Nick,
I would turn on the Profiler to see what's getting sent (and compare it
to what ADO classic is sending), but I really don't see anything wrong. It
might (only might) help to execute this as a SP, but I expect that this
simple query plan would be cached. Since you say the size of the table makes
a difference, I would suspect a server index issue. That is, have you
updated the statistics on the table? If you're running side-by-side with the
ADO classic query this should not be a factor.

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