RE: Differences in execution times for a stored procedure

From: Cowboy (Gregory A. Beamer) - MVP (NoSpamMgbworld_at_comcast.netNoSpamM)
Date: 10/04/04 

Date: Mon, 4 Oct 2004 12:09:02 -0700

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!
***************************
"Vincent Keller" wrote:
> 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 ?
> 
> 
>

Relevant Pages

  • Differences in execution times for a stored procedure
    ... I am trying to execute a time-consuming stored procedure in my application. ... When I run the stored procedure through Query Analyzer, ... Are there some known performance differences in executing a query (stored ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADO, SQLServer2000 and errors
    ... "Luigi" wrote in message ... > If I execute the stored procedure in Query Analyzer what happens, ...
    (microsoft.public.data.ado)
  • xp_sendmail
    ... I'm running sql 2000 and when I execute xp_sendmail with the parameters in ... query analyzer I get the following error: ... Could not find stored procedure 'xp_sendmail'. ...
    (microsoft.public.sqlserver.tools)
  • export stored procedure to excel
    ... creates a crosstab and I can see the results of it when I ... execute it in query analyzer like so: ...
    (microsoft.public.sqlserver.programming)
  • Re: performace issue with a stored procedure
    ... Did you look in the query execution plan in Query Analyzer to see what part ... use OleDB if you want performance. ... > I have run the stored procedure in query analyser on the ...
    (microsoft.public.sqlserver.programming)
Loading