CODEWITHSUNDEEP
sql-serverperformancestored-procedures
Sequenzia
Sequenzia

Reputation: 2381

SQL Server stored procedure a lot slower than straight query

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.

Upvotes: 9

Views: 23154

Answers (5)

sean717
sean717

Reputation: 12693

EXEC sp_recompile N'dbo.MyStoredProc';

Did the trick for me. The stored proc was taking 13 seconds for run and the query takes 0.00 seconds. After running the above command, they both take 0.00 seconds :)

Upvotes: 0

ninjaPixel
ninjaPixel

Reputation: 6382

As already mentioned, this could be a parameter sniffing problem. Try including the line:

OPTION (RECOMPILE)

at the end of your SQL query.

There is an article here explaining what parameter sniffing is: http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

Upvotes: 5

ifx
ifx

Reputation: 581

The first time you run the stored procedure, SQL Server will have to compile the stored procedure, which can take some time. @Astander mentioned parameter sniffing - which is a valid point, and can skew your results.

Some other factors to consider are (whilst they shouldn't really explain your symptoms):

  • You could force a locking level, e.g. WITH (NOLOCK) after the table name, which could resolve the issue (but note that you could get inaccurate results by doing that).
  • You may need to update the statistics on the table or defragment the indexes

Upvotes: 0

digscoop
digscoop

Reputation: 371

You could always try to execute it as dynamic sql:

ALTER PROCEDURE quickCount  

@sYID INT, 
@eYID INT 

AS 
BEGIN 
    SET NOCOUNT ON;

    DECLARE @SQL VARCHAR(max)

    SELECT @SQL = '
    SELECT COUNT(leadID) 
    FROM dbo.leads 
    WHERE yearID >= '+CONVERT(VARCHAR(20),@sYID)+'
    AND yearID <=   '+CONVERT(VARCHAR(20),@eYID)

    EXEC (@SQL)
END

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166606

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

Upvotes: 19

Related Questions