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 ....