Differences in execution times for a stored procedure

[Vincent Keller]

I am trying to execute a time-consuming stored procedure in my application.
When I run the stored procedure through Query Analyzer, it takes some time
but returns results in about 15-20 secs. However, when I run the same
procedure by using ExecuteScalar/ExecuteNonQuery it seems to take a lot more
time ( ~ 2-3 mins).

I have ensured that I am not keeping the connection open for a long time as
well as used Indexes where required but that hasn't helped a lot.

Are there some known performance differences in executing a query (stored
procedure) directly through Query Analyzer and through ADO.NET ?

Any recommendations for such a scenario to speed up query performance ?

 

[Guest]

SQLClient or OleDb? If OleDb, you automatically lose some perf. Are you
explicitly typing all of the parameters or using simple notation:

SqlParameter param = new SqlParameter("@name", MyValue);

If you are using the above, you add some overhead.

Next, have you run a profile on the SQL, using SQL Profiler, to see where
the actual bog down is?

What kinds of T-SQL are you running? When called from "out of process"
certain types of queries can stick a bit.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[William \(Bill\) Vaughn]

When a SP is executed for the first time, SQL Server compiles a query plan
and writes it to memory. This plan is based on the parameters you supply
(the first time). The next time you execute the SP, the same query plan is
used--regardless of how well the parameters "match" the existing plan. If
the SP is complex, it could be that the plan does not match the current
statistics or parameters passed in after the initial execution. Of course,
if the SQL Server needs the memory it will flush the cache and the process
is repeated (a new plan is compiled on next execution). Other factors also
cause the plan to be recompiled and you can request a recompile on each
execution--which sometimes results in better performance.

I'll be discussing this in my workshop on the 15th in Chicago.
hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________