SQL Server存储过程比直接查询慢很多

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

我有一张超过100MM的记录表。该表具有聚簇索引和非聚簇索引。

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.

我可以在表上使用T-SQL运行基本计数，运行需要1秒。当我在存储过程中放入相同的精确计数查询时，它需要12秒才能运行。

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.

我确实尝试在没有参数的情况下运行它，SP运行速度更快（1秒）。所以我假设它与参数有关。

4 个解决方案

#1

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

尝试将SP更改为使用传入的变量的本地副本。

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.

之前我发现由于参数Snffing，SP可以运行得慢很多，但是一旦你使用了变量副本，性能就会恢复。

What is Parameter Sniffing ?

什么是参数嗅探？

SQL Server : Parameter Sniffing

SQL Server：参数嗅探

#2

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

如前所述，这可能是一个参数嗅探问题。尝试包括以下内容：

OPTION (RECOMPILE)

at the end of your SQL query.

在SQL查询结束时。

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

这里有一篇文章解释了什么参数嗅探：http：//blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

#3

You could always try to execute it as dynamic sql:

您总是可以尝试将其作为动态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

#4

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.

第一次运行存储过程时，SQL Server必须编译存储过程，这可能需要一些时间。 @Astander提到参数嗅探 - 这是一个有效的点，可能会扭曲你的结果。

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).
您可以强制锁定级别，例如在表名之后使用WITH（NOLOCK），这可以解决问题（但请注意，通过这样做可能会得到不准确的结果）。
You may need to update the statistics on the table or defragment the indexes
您可能需要更新表上的统计信息或对索引进行碎片整理

#1