Solution: It takes much longer to run a stored procedure than run the code inside the stored procedure

If you have the problem that a stored procedure takes much longer to execute than the exactly same code direct on the MS SQL Server Management Studio, consider the possibility of side-effects from «Parameter Sniffing».

Example:

CREATE Procedure [dbo].[SlowExcecution]
@MyParam NVARCHAR(50)
AS
 
SELECT * FROM XYZ WHERE A = @MyParam
GO

If this code runs fast without SP, but slow as SP, then add a new local variable and copy the parameter into this variable. Then use this variable instead of the parameter.

CREATE Procedure [dbo].[FastExcecution]
@MyParam NVARCHAR(50)
AS
 
DECLARE @MyLocalVariable NVARCHAR(50)
@MyLocalVariable = @MyParam
 
SELECT * FROM XYZ WHERE A = @MyLocalVariable
GO

For details and background informations take a look at this great blog ….

http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html