Performance diff when sp executed from apps and from query analyze

Hi,
Im noticing a performance diff when i execute a proc from the application
the duration shown in the profiler is higher than when i execute the same
proc from query analyze.
Does anyone have any clue on why this difference is occuring. What could be
causing the overhead.
Thanks.
Arzan wrote:
> Hi,
> Im noticing a performance diff when i execute a proc from the
> application the duration shown in the profiler is higher than when i
> execute the same proc from query analyze.
> Does anyone have any clue on why this difference is occuring. What
> could be causing the overhead.
> Thanks.
Duration is sometimes an application issue. The duration for a query is
determined by how long a query takes to execute plus how long the
application takes to fetch all the results in the result set. QA fetches
rows immediately and performs little to no processing on the returning
data, so durations are as fast as they can be. OTOH, an application may
be performing row processing on the result set or not fetching all at
once causing delays in the final duration figure.
You can test this from SQL EM and Profiler.
Run Profiler with the SQL:StmtStarting and SQL:StmtCompleted events.
Find a table with a lot of rows and right-click and select Return All
Rows.
You'll see the SQL:StmtStarting event with the Select * From...
Go back to SQL EM and CTLR + END to move to the end of the result set
Now you see the SQL:StmtCompleted in Profiler.
I'm not saying this is your problem. Can you provide more details about
your app and what's it's doing with the results, if any.
David Gugick
Imceda Software
www.imceda.com