主页 / dba / 问题 / 103157
Accepted
whitebeast
Asked: 2015-06-04 05:53:04 +0800 CST

在@table 变量和#temporary 表上插入期间的不同执行计划

  • 772

我遇到了奇怪的情况：将〜800 000行插入表变量比插入（使用相同的选择）临时表快得多。INSERT 在存储过程下。SELECT 具有来自存储过程的参数。

下面的示例代码：

create procedure p1
    @Param1 int
AS
    declare @t table (id int)

    insert into @t
        select id  
        from dbo.SomeTable 
        where SomeField = @Param1

如果我将 @t 更改为 #t - 程序执行时间的差异将超过 2.5。

作为 DBA，我一直认为应该在临时表中处理这么大的行数……但现在，我不这么认为 :)

无论如何，我只是想了解发生了什么以及为什么相同的 SELECT 在 INSERT 期间有不同的执行计划。

同样的情况（如我所想）在这里：INSERT performance difference between temporary tables and table variable。但我有相反的情况 - 插入 # 比 @ 慢

那么，你有什么想法吗？

  1. 在这种情况下什么更好：使用@或#？
  2. 为什么我会得到不同的执行计划？

执行计划在这里：https ://drive.google.com/file/d/0B1iqgEJ5a7vfcjAwMmtJaVFrNWM/view?usp=sharing

先感谢您！

PS Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)

PPS 我在这里找到了关于临时表和表变量的非常好的文章：SQL Server 中的临时表和表变量有什么区别？. 但是，不幸的是，它对我没有帮助。

sql-server performance

1 个回答

  1. Best Answer

    我注意到的第一件事是每个查询的查询计划编译时间超过 3 秒。哇，这是一个非常复杂的查询！

    由于潜在执行计划的解决方案空间非常大（它随着查询中涉及的对象数量呈指数增长），SQL Server 在出现时只能探索一小部分潜在的查询计划为这些查询制定计划。请记住，SQL Server 的工作不是创建可能的最佳查询计划，而是创建一个足够好的查询计划并尽快完成。

    我经常发现，查询制定方式的微小变化，即使它们不影响查询的逻辑，也会对查询计划产生重大影响。有趣的是，随着查询变得越来越复杂，这种可能性越来越大。发生这种情况的一个可能原因是对查询的调整可能会导致 SQL Server 使用不同的初始计划开始基于成本的优化。随着基于成本的优化的进行，这个不同的起点可能会产生对潜在查询计划空间的不同探索——有点像不同的随机种子会影响随机数的生成。请注意，您提供的查询计划明显不同（比较下面计划形状的图像！）并且 SQL Server 实际上确实估计 @table 变量计划稍微便宜一些。

    至于为什么表变量与临时表会对基于成本的优化产生如此大的影响，我将冒险进行一个仅部分受过教育的猜测：插入表变量会强制执行串行计划（请参阅NonParallelPlanReason出现CouldNotGenerateValidParallelPlan在表变量计划，但不是临时表计划），这可能会影响查询优化器生成初始计划或在计划优化的某个阶段所采用的代码路径。

    如果可能的话，我会尝试的下一步是简化查询，以便使用更少的表和/或将查询拆分为多个查询（使用中间 #temp 表），以便每个查询更简单并提供更好的统计信息。如果这不可行，您还可以尝试更多 hacky 选项，例如使用查询提示（例如，在临时表查询上强制 MAXDOP 1，并查看计划是否更像表变量查询）。

    带有#temp 表的查询计划： 在此处输入图像描述

    带有@table 变量的查询计划： 在此处输入图像描述

    最后，如果您有兴趣更深入地了解查询优化器的工作原理，我发现Paul White 的博客是一个很好的资源！

    • 1

相关问题