为什么参数化查询产生的查询计划比非参数化查询慢得多 [英] Why does a parameterized query produces vastly slower query plan vs non-parameterized query
问题描述
在 SQL Server 2005 数据库中，我正在处理此查询:
In a SQL Server 2005 database I'm working on this query:
选择*
来自 foo
加入 bar.x = foo.x
加入 baz 上的 baz.y = foo.y
其中 foo.x = 1000
select *
from foo
join bar on bar.x = foo.x
join baz on baz.y = foo.y
where foo.x = 1000
与以下参数化版本相比，具有截然不同且速度更快的查询计划.
has a vastly different and faster query plan than the following parameterized version.
声明@p0 int
设置@p0 = 1000
选择 *
来自 foo
加入 bar.x = foo.x
加入 baz 上的 baz.y = foo.y
其中 foo.x = @p0
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
在我的特殊情况下，带有文字的版本在亚秒时间内运行.参数化版本需要 2-3 秒.鉴于它们是相同的查询，我希望它们是相同的.
In my particular case the version with the literal runs in sub-second time. The parameterized version takes 2-3 seconds. I expected them to be identical given that they're the same query.
为什么他们得到不同的查询计划?
Why are they getting different query plans?
有没有什么办法可以让参数化版本和文字版本有一样的性能?
Is there any way to make the parameterized version have the same performance as the literal version?
这是查询计划.我的真实查询与我上面给出的通用查询有很大不同，但是产生这些计划的两个查询之间的唯一区别是参数.为什么用参数替换文字会导致如此截然不同的计划?
Here are the query plans. My real query is rather different than the generic one I gave above, however the ONLY difference between the two queries that produced these plans is the parameter. Why would replacing a literal with a parameter result in such vastly different plans?
推荐答案
看起来查询计划器已经在文字查询中做出了决定，该查询基于它已有的信息.它会有统计数据，它可以根据您的特定文字中给出的数据分布进行有效查询.
It appears that the query planner has made a decision in the literal query which is based upon information that it already has. It would have statistics which it can query efficiently based on the spread of data given in your specific literal.
参数化查询选择了它认为对表中所有数据最公平的查询，您会注意到其中包含许多嵌套循环(性能 = 差).
The parameterized query has chosen the query that it believes is fairest for all the data in your table, which you'll notice is many nested loops (performance = bad).
也许您可以尝试在您的数据库上运行数据库优化工具，看看某些索引是否可以帮助您?
Perhaps you might try and run the database optimization tools on your database to see if some indexes could help you here?
特别是在您的查询中，试试这个:
Specifically in your query, try this:
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))
但如果不确定此查询中包含的数据不会更改并且您对该计划的查询始终会更有效，我会谨慎执行此操作.
But I would be wary of doing this without being certain that the data contained in this query won't change and that your query on this plan will ALWAYS be more efficient.
这篇关于为什么参数化查询产生的查询计划比非参数化查询慢得多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
