Why straight SQL query run faster than stored procedure?

Question

I am facing-out a very surprising problem in SQL Server.

The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will execute in ~57 sec.

For example :

DECLARE @ip_RequestId As Int
SET @ip_RequestId = 3287

SELECT 
   Tbl1.SurveyResponseId,   
   Tbl1.ReportLabelID,               
   Tbl1.EntityId AS FeedbackByEntityId,  
   (SELECT Candidate + ' ' + LastName 
    FROM Tbl2 
    WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName,    
   (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 WHERE MultiRaterId = @ip_RequestId), '')) As ReportTitle
FROM Tbl1

When I run the above query in SSMS as a query, it execute in ~1 seconds.

But after creating the stored procedure:

CREATE PROCEDURE [dbo].[spGetResponse]   
   @ip_RequestId As Int 
AS  
BEGIN
    SELECT 
       Tbl1.SurveyResponseId,   
       Tbl1.ReportLabelID,               
       Tbl1.EntityId AS FeedbackByEntityId,  
       (SELECT Candidate + ' ' + LastName 
        FROM Tbl2 
        WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName,    
       (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 
                       WHERE MultiRaterId = @ip_RequestId), '')) As ReportTitle
    FROM Tbl1
END  

When I execute the above stored procedure with same parameter value (i.e @ip_RequestId = 3287) in SSMS window (exec spGetResponse 3287), it executes in ~57 seconds.

After some googling, I found it's because of "parameter sniffing". But I am not really understand the "parameter sniffing".

**Updated: One more thing **

When the Sp run with search query, i will also fast for me. Means if I convert the SP following format, this will also return result in ~1 sec:

CREATE PROCEDURE [dbo].[spGetResponse]   
    @ip_RequestId As Int 
AS  
BEGIN        
     DECLARE @strQuery As VARCHAR(MAX)
     SET @strQuery = '(SELECT Tbl1.SurveyResponseId, Tbl1.ReportLabelID, Tbl1.EntityId AS FeedbackByEntityId, (SELECT Candidate + '' '' + LastName FROM Tbl2 WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName, (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 WHERE MultiRaterId = ' + @ip_RequestId + '), '''')) As ReportTitle FROM Tbl1)'
     EXECUTE (@strQuery)  
END

When I execute the above stored procedure with same parameter value (i.e @ip_RequestId = 3287) in SSMS window (exec spGetResponse 3287), it executes in ~1 seconds.

Is there any solution exist to overcome the "parameter sniffing" problem?

Answer 1

Assign the value of parameter to the local variable in the procedure then used that variable.

Answer 2

You can use the hint WITH (RECOMPILE) while creating / executing your stored procedure.

CREATE PROCEDURE sp_GetRespond
WITH RECOMPILE

OR

EXECUTE sp_GetRespond WITH RECOMPILE

Every times, the sp is run SQL Engine re-compiles it and generate the most optimal execution plan.

Answer 3

I had the similar issue. Added the OPTIMIZE FOR UNKNOWN at the end of the SP to resolve the problem.