ADO vs Query Analyzer Performance -- It's not parameter sniffing
- From: "Mike Jansen" <mjansen_nntp@xxxxxxxx>
- Date: Wed, 19 Apr 2006 08:54:32 -0400
I'm running into an issue that I've seen in production and also reproduced
in a test environment:
1. Call to stored procedure from application via ADO takes a long time
2. Immediately calling the same stored procedure WITH SAME PARAMETERS from
Query Analyzer returns the results almost immediately.
I have tried multiple ways of narrowing this down and the only differences I
see are ADO/SQLOLEDB vs. Query Analyzer. Using SQL Profiler I captured
SP:Stmt Starting and SP:Stmt Completed and watched the individual queries
run (the stored proc returns multiple recordsets). When called from ADO
only the query lines that returned results had their performance slowed
down, the other statements were "normal". I was informed that the Duration
data column in profiler is the time from when the request is received until
the last row is retrieved, so interaction between the client (ADO/SQLOLEDB)
and the server can affect duration.
I have ruled out "parameter sniffing" since in testing and in production I
have captured a call to the proc in profiler, and copied and pasted the
exact call into Query Analyzer and executed it immediately and the Query
Analyzer call returned very quickly (40 seconds from app, 2 seconds in Query
Analyzer). I've even from the development environment re-run the IDENTICAL
query from the app over and over (same parameters) and it still runs slow so
its not a matter of caching on the server. (By the way, the slowness in the
app is completely narrowed down to the Recordset.Open call so its not other
app code).
The wierd thing is that this issue seems to come and go and does seem to be
affected by stuff outside of the client.
I don't expect a direct answer to my problem (I haven't provided enough
specific probably anyways), but what I do need to understand is how
ADO/SQLOLEDB interaction with SQL Server is different than Query Analyzer.
What kinds of things would be different on both the client side
(ADO/SQLOLEDB vs Query Analyzer) and the server side (how either of these
two causes SQL Server to function differently. Also what is the difference
between RPC calls and "batch" calls as far as performance goes? Can the RPC
subsystem (which I'm assuming is the OS subsystem and not something specific
to SQL server) have bottlenecks as well (I think I've eliminated this as the
cause in this specific example though) ?
If anyone can give insight into that, it would be greatly appreciated!
Thanks,
Mike
.
- Prev by Date: Re: Run-time error -214722164 (80040154) Class Not Registered
- Next by Date: RE: oledb problem in VS2005
- Previous by thread: About Disconnected Record Sets
- Next by thread: RE: oledb problem in VS2005
- Index(es):
Relevant Pages
|
|
