SQL Server stored procedure a lot slower than straight query

Question

I have a table with over 100MM records in it. The table has a clustered index and a nonclustered index.

I can run a basic count using T-SQL on the table and it takes 1 second to run. When I put the same exact count query inside of a stored procedure it then takes 12 seconds to run.

I have looked at the execution plan for both the standard query and the stored procedure and they both are using the nonclustered index.

I am not sure why the stored procedure is so slow compared to the standard query.

I have read some stuff about reindexing in a situation like this but I am not sure why I need to do that. Also, it takes a few hours to reindex so I want to make sure that will work.

Any help on this would be great.

Thanks

UPDATE

Here is the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE quickCount 

@sYID INT,
@eYID INT

AS
BEGIN

SET NOCOUNT ON;


    SELECT COUNT(leadID)
    FROM dbo.leads
    WHERE yearID >= @sYID
    AND yearID <= @eYID

END
GO

and here is the standard query:

SELECT COUNT(leadID)
FROM leads
WHERE yearID >= 0
AND yearID <= 99

I did try to run it with no parameters and the SP runs way faster (1 second). So I am assuming that it has something to do with the parameters.

Answer 1

Try changing your SP to using local copies of the variables passed in.

Something like

ALTER PROCEDURE quickCount  

@sYID INT, 
@eYID INT 

AS 
BEGIN 

SET NOCOUNT ON; 
    DECLARE @Local_sYID INT, 
            @Local_eYID INT 
    SELECT  @Local_sYID = @sYID INT, 
            @Local_eYID = @eYID INT

    SELECT COUNT(leadID) 
    FROM dbo.leads 
    WHERE yearID >= @Local_sYID 
    AND yearID <= @Local_eYID 

END 

I have found before that due to Parameter Snffing, a SP can run a lot slower, but the performance returns once you use copies of the variables.

What is Parameter Sniffing ?

SQL Server : Parameter Sniffing