SQL -使用常量值和参数是否存在性能差异？

Question

在性能方面，如果在where子句中有许多使用(不同的)常量值运行的查询，而不是在顶部有声明参数的查询，而参数值却在变化，那么性能会有什么不同吗？

where子句中具有常量值的示例查询：

select
*
from [table]
where [guid_field] = '00000000-0000-0000-000000000000' --value changes

提议(改进？)具有声明参数的查询：

declare @var uniqueidentifier = '00000000-0000-0000-000000000000' --value changes

select
*
from [table]
where [guid_field] = @var

有什么不同吗？我正在查看类似于上述两个查询的执行计划，没有任何不同之处。但是，我似乎还记得，如果在SQL语句中使用常量值，那么SQL server将不会重用相同的查询执行计划，或者导致性能下降的问题--但这是真的吗？

Answer 1

在这里区分参数和变量是很重要的。参数传递给过程和函数，变量被声明。

寻址变量，这是问题中的SQL所具有的，在编译一个临时批处理时，Server自己编译每个语句。因此，当使用变量编译查询时，它不会回过头来检查任何赋值，因此它将编译一个为未知变量优化的执行计划。在第一次运行时，该执行计划将被添加到计划缓存中，然后以后的执行可以并将对所有变量值重用此缓存。

当您传递一个常量时，查询将根据该特定值进行编译，因此可以创建一个更优化的计划，但需要增加重新编译的成本。

因此，要具体回答你的问题：

但是，我似乎还记得，如果在SQL语句中使用常量值，那么SQL server将不会重用相同的查询执行计划，或者导致性能下降的问题--但这是真的吗？

是的，确实不能对不同的常量值重用相同的计划，但这并不一定会导致更差的性能。可以为该特定常量使用更合适的计划(例如，选择书签查找而不是索引扫描稀疏数据)，这种查询计划更改可能超过重新编译的成本。与SQL性能问题几乎总是一样。答案是，它取决于。

对于参数，默认行为是根据第一次执行过程或函数时参数使用的时间来编译执行计划。

我以前曾用例子详细地回答过类似的问题，这些问题涵盖了上述许多方面，因此，我将不重复其中的各个方面，而只是将这些问题联系起来：

• Does assigning stored procedure input parameters to local variables help optimize the query?
• Ensure cold cache when running query
• Why is SQL Server using index scan instead of index seek when WHERE clause contains parameterized values

Answer 2

你的问题涉及的事情太多了，一切都与统计有关。

SQL甚至为Adhoc查询编译执行计划，并将它们存储在计划缓存中，以便重用(如果它们被认为是安全的)。

select * into test from sys.objects

select schema_id,count(*) from test
group by schema_id


--schema_id 1 has 15
--4 has 44 rows

第一次询问:我们每次都尝试不同的文本，所以如果sql认为它可以看到safe..You，则保存计划，第二个查询估计值与文本4相同，因为SQL将计划保存为4。

--lets clear cache first--not for prod
dbcc freeproccache

select * from test
where schema_id=4

输出：

​

​

select * from test where 
 schema_id=1

输出：

​

​

第二个问题:

作为param传递局部变量，让我们使用相同的值4。

--lets pass 4 which we know has 44 rows,estimates are 44 whem we used literals
declare @id int
set @id=4
select * from test

如下图所示，使用局部变量估计的粗略的29.5行与统计数据有关。

输出：

​

​

因此，总之，统计信息在选择查询计划(嵌套循环或进行扫描或查找)方面至关重要，从示例中可以看出，从计划缓存膨胀的角度来看，每个method.further的估计值是如何不同的。

您可能还想知道，如果我传递了许多临时查询，因为SQL为相同的查询生成了一个新的计划，即使空间有变化，那么会发生什么呢?下面的链接将帮助您。

进一步阅读：

http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/

http://sqlperformance.com/2012/11/t-sql-queries/ten-common-threats-to-execution-plan-quality

Answer 3

首先，注意局部变量与参数不相同。

假设列被索引或具有统计信息，Server将使用统计信息直方图根据所提供的常量值收集符合条件的行数估计值。如果查询很简单，也会自动参数化和缓存(不管值如何都会产生相同的计划)，这样后续的执行就可以避免查询编译成本。

参数化查询还使用带有最初提供的参数值的stats直方图生成计划。该计划被缓存，并用于后续的执行，不管它是否微不足道。

对于局部变量，Server使用总体统计基数来生成计划，因为实际值在编译时是未知的。这个计划对于一些值可能是好的，但是对于其他的值来说，当查询不是很简单的时候，则不是最优的。