查询运行快,但在存储过程中运行慢 [英] Query runs fast, but runs slow in stored procedure
问题描述
我正在使用 SQL 2005 分析器进行一些测试.
I am doing some tests using the SQL 2005 profiler.
我有一个存储过程,它只运行一个 SQL 查询.
I have a stored procedure which simply runs one SQL query.
当我运行存储过程时,它需要很长时间并执行 800,000 次磁盘读取.
When I run the stored procedure, it takes a long time and performs 800,000 disk reads.
当我在存储过程之外运行相同的查询时,它会读取 14,000 次磁盘.
When I run the same query separate to the stored procedure, it does 14,000 disk reads.
我发现,如果我使用 OPTION(重新编译)运行相同的查询,则需要 800,000 次磁盘读取.
I found that if I run the same query with OPTION(recompile), it takes 800,000 disk reads.
由此,我做出(可能是错误的)假设,即存储过程每次都在重新编译,这就是问题所在.
From this, I make the (possibly erroneous) assumption that the stored procedure is recompiling each time, and that's causing the problem.
有人可以对此有所了解吗?
Can anyone shed some light onto this?
我已将 ARITHABORT 设为 ON.(这在stackoverflow上解决了类似的问题,但没有解决我的)
I have set ARITHABORT ON. (This solved a similar problem on stackoverflow, but didn't solve mine)
这里是整个存储过程:
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
这是我运行的演示问题的脚本:
Here's a script I run that demonstrates the problem:
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)
推荐答案
好的,我们之前也遇到过类似的问题.
OK, we have had similar issues like this before.
我们解决这个问题的方法是在 SP 内部设置局部参数,这样
The way we fixed this, was by making local parameters inside the SP, such that
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 内部的本地参数而不是传入的参数.
We then use the local parameters inside the SP rather than the parameters that was passed in.
这通常为我们解决了问题.
This typically fixed the issue for Us.
我们认为这是由于参数嗅探引起的,但没有任何证据,抱歉...X-)
We believe this to be due to parameter sniffing, but do not have any proof, sorry... X-)
看看不同纠正 SQL Server 参数嗅探的方法,了解一些有见地的示例、解释和修复.
Have a look at Different Approaches to Correct SQL Server Parameter Sniffing for some insightful examples, explanations and fixes.
这篇关于查询运行快,但在存储过程中运行慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!