SQL Server Stored Proc takes much longer than the same query made from text in Management Studio

Question

So, here's a little weird ... I have a stored process that takes 40 seconds. I copy the contents of the saved proc to a new request window, change 2 input parameters (which are both dates), so they are declared and set, and start the request. This is basically an instant execution in 1 second. The only difference is that the stored proc accepts 2 dates as parameters.

Has anyone understood what could happen?

(I am running SQL Server 2005)

Answer 1

Recompilation sniffs the parameters, so it does not matter.

This article explains my statement ...

... parameter values ​​are evaluated during compilation or recompilation ...

You need to mask the parameters:

ALTER PROCEDURE [uspFoo] @Date1 datetime, @Date2 datetime AS BEGIN DECLARE @IDate1 datetime, @IDate2 datetime; SELECT @IDate1 = @Date1, @IDate2 = @Date2; -- Stuff here that depends on @IDate1 and @IDate2 END 

Answer 2

As gbn says, this is a problem with sniffing options. An alternative way of suggesting it is to include the following line at the end of your query:

 OPTION (RECOMPILE) 

Answer 3

Run the profiler and write down the query plans for execution. Check what differences - you can customize the request or force a specific plan.

Answer 4

I had a similar problem, and I found that it was because I was setting the parameter as DATE, but the column in which it was being compared in the WHERE clause was in DATETIME, so that SSMS would convert the data type for each one that it was comparing. Changing the parameter data type to DATETIME or masking the parameter as described above resolves the issue.