查询运行速度很快，但在存储过程中运行速度较慢

Question

我正在使用SQL2005分析器进行一些测试。

我有一个存储过程，它只运行一个SQL查询。

当我运行存储过程时，它需要很长时间并执行800,000次磁盘读取。

当我单独运行与存储过程相同的查询时，它进行了14,000次磁盘读取。

我发现，如果我使用OPTION(recompile)运行相同的查询，它需要800,000次磁盘读取。

由此，我做出了(可能是错误的)假设，即存储过程每次都会重新编译，这就是导致问题的原因。

有谁能解释一下这个问题吗？

我已将ARITHABORT设置为ON。(这解决了堆栈溢出的类似问题，但没有解决我的问题)

下面是整个存储过程：

CREATE PROCEDURE [dbo].[GET_IF_SETTLEMENT_ADJUSTMENT_REQUIRED]
 @Contract_ID int,
 @dt_From smalldatetime,
 @dt_To smalldatetime,
 @Last_Run_Date datetime
AS
BEGIN
 DECLARE @rv int


 SELECT @rv = (CASE WHEN EXISTS
 (
  select * from 
  view_contract_version_last_volume_update
  inner join contract_version
  on contract_version.contract_version_id = view_contract_version_last_volume_update.contract_version_id
  where contract_version.contract_id=@Contract_ID
  and volume_date >= @dt_From
  and volume_date < @dt_To
  and last_write_date > @Last_Run_Date
 )
 THEN 1 else 0 end)

 -- Note that we are RETURNING a value rather than SELECTING it.
 -- This means we can invoke this function from other stored procedures
 return @rv
END

下面是我运行的一个脚本，它演示了这个问题：

DECLARE 
 @Contract_ID INT,
 @dt_From smalldatetime,
 @dt_To smalldatetime,
 @Last_Run_Date datetime,
    @rv int


SET @Contract_ID=38
SET @dt_From='2010-09-01'
SET @dt_To='2010-10-01'
SET @Last_Run_Date='2010-10-08 10:59:59:070'


-- This takes over fifteen seconds
exec GET_IF_SETTLEMENT_ADJUSTMENT_REQUIRED @Contract_ID=@Contract_ID,@dt_From=@dt_From,@dt_To=@dt_To,@Last_Run_Date=@Last_Run_Date

-- This takes less than one second!
SELECT @rv = (CASE WHEN EXISTS
(
 select * from 
 view_contract_version_last_volume_update
 inner join contract_version
 on contract_version.contract_version_id = view_contract_version_last_volume_update.contract_version_id
 where contract_version.contract_id=@Contract_ID
 and volume_date >= @dt_From
 and volume_date < @dt_To
 and last_write_date > @Last_Run_Date
)
THEN 1 else 0 end)


-- With recompile option. Takes 15 seconds again!
SELECT @rv = (CASE WHEN EXISTS
(
 select * from 
 view_contract_version_last_volume_update
 inner join contract_version
 on contract_version.contract_version_id = view_contract_version_last_volume_update.contract_version_id
 where contract_version.contract_id=@Contract_ID
 and volume_date >= @dt_From
 and volume_date < @dt_To
 and last_write_date > @Last_Run_Date
)
THEN 1 else 0 end) OPTION(recompile)

Answer 1

好的，我们以前也遇到过类似的问题。

我们修复此问题的方法是在SP中设置本地参数，以便

DECLARE @LOCAL_Contract_ID int, 
        @LOCAL_dt_From smalldatetime, 
        @LOCAL_dt_To smalldatetime, 
        @LOCAL_Last_Run_Date datetime

SELECT  @LOCAL_Contract_ID = @Contract_ID, 
        @LOCAL_dt_From = @dt_From, 
        @LOCAL_dt_To = @dt_To, 
        @LOCAL_Last_Run_Date = @Last_Run_Date

然后，我们使用SP内部的本地参数，而不是传入的参数。

这通常会为我们解决这个问题。

我们认为这是由于参数嗅探，但没有任何证据，抱歉...X-)

编辑：

看看Different Approaches to Correct SQL Server Parameter Sniffing，可以找到一些有见地的例子、解释和修复。

Answer 2

正如其他人所提到的，这可能是一个“参数嗅探”问题。尝试包括这一行：

OPTION (RECOMPILE)

在SQL查询的末尾。

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

Answer 3

我猜这是由parameter sniffing.引起的