主页 / dba / 问题 / 158374
Accepted
Jason
Asked: 2016-12-17 06:54:09 +0800 CST

使用 Join 和 Window 函数获取领先和滞后值的性能比较

  • 772

我有一个有 20M 行的表，每行有 3 列：time、id和value. 对于每个idand time，都有一个valuefor 状态。我想知道某个特定的领先和滞后time值id。

我使用了两种方法来实现这一点。一种方法是使用连接，另一种方法是使用窗口函数超前/滞后和聚集time索引id。

我通过执行时间比较了这两种方法的性能。join 方法耗时 16.3 秒，window function 方法耗时 20 秒，不包括创建索引的时间。这让我感到惊讶，因为窗口功能似乎是先进的，而连接方法是蛮力的。

下面是这两种方法的代码：

创建索引

create clustered index id_time
 on tab1 (id,time)

加入方式

select a1.id,a1.time
   a1.value as value, 
   b1.value as value_lag,
   c1.value as value_lead
into tab2
from tab1 a1
left join tab1 b1
on a1.id = b1.id
and a1.time-1= b1.time
left join tab1 c1
on a1.id = c1.id
and a1.time+1 = c1.time

使用以下命令生成的 IO 统计信息SET STATISTICS TIME, IO ON：

连接方法的统计信息

这是join方法的执行计划

窗函数法

select id, time, value, 
   lag(value,1) over(partition by id order by id,time) as value_lag,
   lead(value,1) over(partition by id order by id,time) as value_lead
into tab2
from tab1

（仅订购可time节省 0.5 秒。）

这是Window函数方法的执行计划

IO统计

[窗口函数方法4的统计]

我检查了数据，sample_orig_month_1999似乎原始数据按idand排序良好time。这是性能差异的原因吗？

看起来join方法比window function方法有更多的逻辑读取，而前者的执行时间实际上更少。是不是因为前者有更好的并行性？

由于代码简洁，我喜欢窗口函数方法，有没有办法加快这个特定问题的速度？

我在 Windows 10 64 位上使用 SQL Server 2016。

sql-server performance

1 个回答

  1. Best Answer

    LEAD与自连接相比，窗口函数的行模式性能相对较低LAG并不是什么新鲜事。例如，Michael Zilberstein 早在 2012 年就在 SQLblog.com 上写过它。在（重复的）Segment、Sequence Project、Window Spool 和 Stream Aggregate 计划运算符中存在相当多的开销：

    计划部分

    在 SQL Server 2016 中，您有一个新选项，即为窗口聚合启用批处理模式。这需要表上的某种列存储索引，即使它是空的。优化器当前需要存在列存储索引才能考虑批处理模式计划。特别是，它启用了效率更高的 Window Aggregate 批处理模式运算符。

    要在您的情况下对此进行测试，请创建一个空的非聚集列存储索引：

     -- Empty CS index
CREATE NONCLUSTERED COLUMNSTORE INDEX dummy 
ON dbo.tab1 (id, [time], [value]) 
WHERE id < 0 AND id > 0;

    查询：

    SELECT
    T1.id,
    T1.[time],
    T1.[value],
    value_lag = 
        LAG(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time]),
    value_lead =
        LEAD(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time])
FROM dbo.tab1 AS T1;

    现在应该给出一个执行计划，例如：

    批处理模式行存储计划

    ...这可能会执行得更快。

    OPTION (MAXDOP 1)将结果存储在新表中时，您可能需要使用提示或其他提示来获得相同的计划形状。该计划的并行版本需要批处理模式排序（或可能两个），这可能会慢一些。这取决于您的硬件。

    有关批处理模式窗口聚合运算符的更多信息，请参阅 Itzik Ben-Gan 的以下文章：

    • 15

相关问题