Executing stored procedure takes too long than executing TSQL

Question

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

Answer 1

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.

Answer 2

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.