主页 / dba / 问题 / 126006
Accepted
boot4life
Asked: 2016-01-13 07:40:20 +0800 CST

如何通过 UNPIVOT（循环连接）使用批处理模式？

  • 772

我有以下形式的查询：

SELECT ...
FROM ColumnstoreTable cs
CROSS APPLY (
    SELECT *
    FROM (VALUES
        ('A', cs.DataA)
        , ('B', cs.DataB)
        , ('C', cs.DataC)
    ) x(Col0, Col1)
) someValues

这从 Columnstore 支持的子查询 ( ColumnstoreTable) 中获取每一行并将这些行相乘。这本质上是一个UNPIVOT. 真正的查询比这更大。查询的这一部分提供给其他处理。

这里的问题是这CROSS APPLY是作为一个合理的选择的循环连接实现的。不幸的是，循环连接不支持批处理模式。

这部分查询对性能非常关键，我怀疑以批处理模式运行它可能对性能非常有益。

我怎样才能重写这个查询，这样我就不会退出批处理模式？

我确实尝试使用临时表而不是VALUES，但这并没有改变没有相等连接条件来进行散列连接的事实。

sql-server sql-server-2014

1 个回答

  1. Best Answer

    一种方法可能是为值使用#temp 表，并引入虚拟等值连接列以允许散列连接。例如：

    -- Create a #temp table with a dummy column to match the hash join
-- and the actual column you want
CREATE TABLE #values (dummy INT NOT NULL, Col0 CHAR(1) NOT NULL)
INSERT INTO #values (dummy, Col0)
VALUES (0, 'A'),
        (0, 'B'),
        (0, 'C')
GO

-- A similar query, but with a dummy equijoin condition to allow for a hash join
SELECT v.Col0, 
    CASE v.Col0 
        WHEN 'A' THEN cs.DataA 
        WHEN 'B' THEN cs.DataB 
        WHEN 'C' THEN cs.DataC
        END AS Col1
FROM ColumnstoreTable cs
JOIN #values v
    -- Join your dummy column to any numeric column on the columnstore,
    -- multiplying that column by 0 to ensure a match to all #values
    ON v.dummy = cs.DataA * 0

    性能和查询计划

    这种方法产生如下查询计划，哈希匹配以批处理模式执行：

    在此处输入图像描述

    如果我用语句替换SELECT语句SUM以CASE避免必须将所有这些行流式传输到控制台，然后在我周围的一个真正的 100MM 行列存储表上运行查询，我会看到相当好的性能来生成必需的 300MM行：

    CPU time = 33803 ms, elapsed time = 4363 ms.

    实际计划显示了散列连接的良好并行化。

    在此处输入图像描述

    当所有行都具有相同值时，有关哈希连接并行化的注意事项

    此查询的性能在很大程度上取决于联接的探测端的每个线程都可以访问完整的哈希表（与哈希分区版本相反，假设只有一个不同的值，哈希分区版本会将所有行映射到单个线程列dummy）。

    幸运的是，在这种情况下这是真的（正如我们可以从探测端缺少Parallelism运算符所看到的那样）并且应该可靠地是真的，因为批处理模式构建了一个跨线程共享的哈希表。因此，每个线程都可以从中获取它们的行Columnstore Index Scan并将它们与该共享哈希表相匹配。在 SQL Server 2012 中，此功能的可预测性要低得多，因为溢出会导致运算符以行模式重新启动，这既失去了批处理模式的优势，又需要Repartition Streams连接的探测端的运算符，这在这种情况下会导致线程倾斜. 允许溢出保持批处理模式是 SQL Server 2014 的一项重大改进。

    据我所知，行模式没有这种共享哈希表的能力。然而，在某些情况下，通常在构建端估计少于 100 行，SQL Server 将为每个线程创建一个单独的哈希表副本（可通过Distribute Streams进入哈希连接的前导来识别）。这可能非常强大，但不如批处理模式可靠，因为它取决于您的基数估计，并且 SQL Server 正在尝试评估为每个线程构建哈希表的完整副本的收益与成本。

    UNION ALL：一个更简单的选择

    Paul White 指出，另一个可能更简单的选项是使用UNION ALL每个值组合行。假设您很容易动态构建此 SQL，这可能是您最好的选择。例如：

    SELECT 'A' AS Col0, c.DataA AS Col1
FROM ColumnstoreTable c
UNION ALL
SELECT 'B' AS Col0, c.DataB AS Col1
FROM ColumnstoreTable c
UNION ALL
SELECT 'C' AS Col0, c.DataC AS Col1
FROM ColumnstoreTable c

    这也会产生一个能够利用批处理模式并提供比原始答案更好的性能的计划。（尽管在这两种情况下，性能都足够快，以至于任何快速选择数据或将数据写入表都成为瓶颈。）该UNION ALL方法还避免了玩乘以 0 之类的游戏。有时最好简单地思考！

    CPU time = 8673 ms, elapsed time = 4270 ms.

    在此处输入图像描述

    • 12

相关问题