Stored procedure takes longer to run than query.

Issue:

Hi I have a stored procedure and when i run it by using declare and set parameter it gives me result with in 30 seconds but if I use (exec sp_procedurename parametervalue1,parametervalue2) statement then it takes 5 minutes to execute.

Resolution:

If you’re running the same exact SQL as the stored procedure and the times are that different, your stored procedure is probably relying on metadata that is out of date. Try updating statistics or recompiling the stored procedure.

EXEC sp_procedurename ‘1-Nov-2011’, ‘15-Nov-2011’ WITH RECOMPILE

-- Update all statistics on a table
USE AdventureWorks2012;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail;  
GO  

-- Update the statistics for an index
USE AdventureWorks2012;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;  
GO