SQL Server Stored Proc takes (much) longer to run than the same query executed from text in Management Studio
Recompilation sniffs the parameters, so it can make no difference.
This article explains my statement...
...parameter values are sniffed during compilation or recompilation...
You need to mask the parameters:
ALTER PROCEDURE [uspFoo] @Date1 datetime, @Date2 datetimeASBEGIN DECLARE @IDate1 datetime, @IDate2 datetime; SELECT @IDate1 = @Date1, @IDate2 = @Date2; -- Stuff here that depends on @IDate1 and @IDate2END
As gbn says, this is a parameter sniffing issue. An alternative way to his suggestion is to include the following line at the end of your query:
OPTION (RECOMPILE)
Run the profiler and capture the query plans for the execution. Check to see what the differences are - you may be able to tune the query or force a particular plan.