CODEWITHSUNDEEP
sql-server
user3145
user3145

Reputation: 111

Executing stored procedure takes too long than executing TSQL

I have a stored procedure and when I want to execute it using exec proc_name it takes 1 min

If I copy the code from stored procedure, declare params as variables and then execute the code it takes 10 sec.

What's wrong ?

I am missing something here ?

I am asking this because I use ADO.NET and I get a timeout error when I want to execute that stored procedure using ExecuteNonQuery.

Thank you

Upvotes: 5

Views: 27728

Answers (5)

Vithu
Vithu

Reputation: 151

Option 1 : execute SP in Alter State and try again with parameters.

Option 2 : EXEC sp_updatestats

Option 3 : Failing with option 1, add "option(recompile)" at the end of your query.

Eg : Select Id from Table1 Order BY Id desc option(recompile)

If this runs faster, slow down was due to executions plans made by SQL.

Upvotes: 0

Peter Wishart
Peter Wishart

Reputation: 12270

Its caused by suboptimal plans being used. You mention that the s.p. has parameters, I've had similar issues due to 'parameter sniffing'.

The quickest check to see if this is the issue is just to, inside the SP, copy the input parameters into local variables then use only the local variables.

This stops e.g. optimisation for certain paramater values at the expense of others.

I've had this before in an s.p. which had int parameters where certain parameter values changed the control flow (as well as how queries would be executed) a bit.

Upvotes: 18

bjnr
bjnr

Reputation: 3437

As a general idea, query plans are cached differently when we talk about adhoc statements vs stored procedures. So the execution time could be different as chosen query plan could be different.

As suggestions, I think at:

1/ Invalidate the query plan associated with that stored procedure:

sp_recompile <procname>

2/ Delete all query plans from cache (the hard-way, non recommended in PROD unless you understand very well the consequences): :

DBCC FREEPROCCACHE

3/ Update statistics for involved tables.

4/ Have look at actual execution plan for both cases and isolate where is the performance bottleneck. Post some code and we'll provide you more details about.

Upvotes: 0

YankTHEcode
YankTHEcode

Reputation: 652

try the executing proc with Execution plan icon switched on. It will tell you exactly which part takes time and you/we can probably take over (suggestions) from there. Thanks

Upvotes: 0

Jakub Konecki
Jakub Konecki

Reputation: 46008

Start Sql Profiler and compare those two executions: is the extra 50 mins spent on the server? Are the queries really the same?

You can than copy the actual query text and run it manually and check execution plan.

Upvotes: 2

Related Questions