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.

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

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

"Nick" <(E-Mail Removed)> wrote in message
news:042801c36c88$c5fc1550$(E-Mail Removed)...
> OK, the code is very straight forward:
>
> Dim strSID As String = "REQWTEQoe34243"
> Dim strConnectionStrig As String = "...."
> Dim myCN As SqlConnection = New SqlConnection
> (strConnectionString)
> Dim myCMD As SqlCommand = myCN.CreateCommand()
> Dim myDR As SqlDataReader
>
> myCMD.CommandText = "Select * from SESSIONS where
> SID = @SID"
> myCMD.Parameters.Add("@SID", strSID)
> myCN.Open()
>
> 'This is the Statement taking so long:
> myDR = myCMD.ExecuteReader
>
> While myDR.Read()
>
> ' Process the Data
>
> End While
> myDR.Close()
> myCN.Close()
>
> The Table Sessions is a SQL Server Table with a Primary
> KEY on SID and contains 6 Columns (Integer and Char) and
> has over 4 Million entries. Executing the Select Statement
> in ADO and ASP runs in milliseconds (8 Way SQL 2000
> Cluster Server). In ASP.NET using the code uses more than
> 1.5 seconds to execute myCmd.ExecuteReader statement.
>
> I tried the same on a smaller table (40.000 Entries) and I
> do not experiance a difference between ADO and ADO.NET,
> but the larger the table becomes, the higher is the
> difference in execution time of the same statement.
>
> I also tried to do a
> .ExecuteReader(CommandBehavior.SequentialAccess)
> and
> .ExecuteReader(CommandBehavior.SingleRow)
>
> and also tried to use a dataset instead or the datareader,
> but there is no significant performance difference, since
> it is a single row what comes back.
> It seems to me, that the DataReader does some additional
> stuff in the background that consumes a lot of time on the
> SQL Server (some Table Locks or Cursors or so?) instead of
> just executing the SQL Statement and returning the data.
>
> So I am looking for a low level API to the SQL Server
> without undemanded Wizzard work in the background, that
> just does what I say - "select and return the data".
>
> Thank you for your assistance.
>
> Kind regards
>
> Nick