Re: Slow Stored Procedure when run via ado, fast from query analyzer


"barooz" <BruceTheNoble@xxxxxxxxx> wrote in message
news:1122663559.051929.160540@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Greetings folks.
>
> I've got a stored procedure reading data from the database that runs in
> about 2 seconds from query analyzer, and takes 50+ seconds run from ADO
> in asp classic. I'm using SQLOLEDB. I'm totally baffled, any ideas
> what I could check?

That is quite a difference. Even inefficient coding in ADO will not account
for as much as that.
Does the SQL Profiler not reveal what is going on?

For ADO, you have set the optional parameters that say this is Stored
Procedure?
If a Recordset - have you checked to see CursorLocation, CursorType,
LockType?
If Server-sided, have you set the CacheSize?

If you have Client-sided RecordSet, no data is seen until all data is read
into memory.
The action happens at the Open() stage and Open() does not return until
_ALL_ records are in memory.

If you have Server-sided RecordSet, data is seen as soon as the Cache is
full.
Open() returns immediately. This may give the illusion of speed in that the
latency is much smaller, but if you timed when you see the last record for
Server vs Client, it should be the same ballpark in magnitude.

> I'm getting the 50+ seconds time from Sql
> Profiler...so I'm assuming that 50+ seconds is the server side
> execution time and isn't including the time to transport the data from
> the sqlserver to the webserver... Any ideas?

Nope. I would raise this in the SQLServer programming newsgroup.

Stephen Howe


.

Relevant Pages

  • Re: DAO to ADO Recordset Options
    ... DAO was really fast when dealing with Access data (faster than ADO) but was ... Recordset. ... For client-sided cursors, there is only Static cursor type regardless as ... determines how often data is fetched from the server. ...
    (microsoft.public.data.ado)
  • Pessimistic locking with approles.
    ... It now uses ADO, server side cursors, application roles, ... say you have an ADO recordset open on a table Employee ... (Pessimistic locking requires a server side cursor.) ...
    (microsoft.public.sqlserver.security)
  • Re: ADO Data Control Concurrency Problem
    ... >> Editing of row in table is done with the help of ADO Data Control. ... Cursor location does not bare on whether the ... Client or Server memory. ... reflected in the open recordset. ...
    (microsoft.public.vb.general.discussion)
  • Really disconnect a recordset?
    ... Is there a way to completely disconnect a recordset so that it has no memory ... it was created via OLE DB for SQL Server? ... ADO is not allowing me to "update" the these fields. ... Set con = New ADODB.Connection ...
    (microsoft.public.vb.database.ado)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)