为什么参数化查询会产生比非参数化查询慢得多的查询计划

在SQL Server 2005数据库中,我正在处理此查询:

select*
from foo
join bar on bar.x = foo.x
join baz on baz.y = foo.y
where foo.x = 1000

与以下参数化版本相比,它具有非常不同且更快的查询计划.

声明@p0 int
set @ p0 = 1000
select*
from foo
join bar on bar.x = foo.x
join baz on baz.y = foo.y
where foo.x = @ p0

在我的特定情况下,具有文字的版本在亚秒时间运行.参数化版本需要2-3秒.我认为它们是相同的,因为它们是相同的查询.

为什么他们会得到不同的查询计划？

有没有办法让参数化版本具有与文字版本相同的性能？

这是查询计划.我的真实查询与上面给出的通用查询有很大不同,但产生这些计划的两个查询之间的唯一区别是参数.为什么用一个参数替换一个文字会产生如此大不相同的计划？

文字查询计划

参数化查询计划

Spence.. 8

看起来查询规划器已在文字查询中做出决定,该查询基于它已有的信息.它将具有可以根据特定文字中给出的数据传播有效查询的统计数据.

参数化查询选择了它认为对表中所有数据最公平的查询,您会注意到许多嵌套循环(性能=坏).

也许您可以尝试在数据库上运行数据库优化工具,看看某些索引是否可以帮助您？

特别是在您的查询中,试试这个:

declare @p0 int
set @p0 = 1000
select *
from foo
join bar on bar.x = foo.x
join baz on baz.y = foo.y
where foo.x = @p0
OPTION ( OPTIMIZE FOR (@p0 = 1000))

但是我会谨慎地做到这一点,而不确定此查询中包含的数据不会改变,并且您对此计划的查询总是更有效率.

看起来查询规划器已在文字查询中做出决定,该查询基于它已有的信息.它将具有可以根据特定文字中给出的数据传播有效查询的统计数据.

参数化查询选择了它认为对表中所有数据最公平的查询,您会注意到许多嵌套循环(性能=坏).

也许您可以尝试在数据库上运行数据库优化工具,看看某些索引是否可以帮助您？

特别是在您的查询中,试试这个:

declare @p0 int
set @p0 = 1000
select *
from foo
join bar on bar.x = foo.x
join baz on baz.y = foo.y
where foo.x = @p0
OPTION ( OPTIMIZE FOR (@p0 = 1000))

但是我会谨慎地做到这一点,而不确定此查询中包含的数据不会改变,并且您对此计划的查询总是更有效率.

我认为你正在与" 参数嗅探 "发生冲突.基本上,这意味着SQL Server会尝试使用尽可能多的信息来计算查询的最佳执行计划.对于第一个查询,您有一个在编译时已知的常量值,因此引擎可以直接优化该值的查询计划.

在第二个中,你在编译时使用了一个变量掩码,这个掩码是引擎的值(你认为它应该能够解决它,但实际上我有一个简单的常量表达式的类似问题! ),导致表现不佳.

您可以尝试解决此问题的一种方法是将查询包装在直接获取参数的存储过程中,然后将其应用于查询 - 如下所示:

CREATE PROCEDURE test
  @p0 int
AS
BEGIN
  select *
  from foo
  join bar on bar.x = foo.x
  join baz on baz.y = foo.y
  where foo.x = @p0
END

这应该允许优化器准确地"嗅探"您使用的参数并为您生成最佳查询计划.