主页 / dba / 问题 / 230722
Accepted
Joe Obbish
Asked: 2019-02-26 17:27:45 +0800 CST

为什么临时表比急切线轴更有效地解决万圣节问题？

  • 772

考虑以下查询，该查询仅在源表中尚未在目标表中时才从源表中插入行：

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

一种可能的计划形状包括合并连接和急切线轴。急切的线轴操作员在场以解决万圣节问题：

第一个计划

在我的机器上，上面的代码在大约 6900 毫秒内执行。用于创建表格的复制代码包含在问题的底部。如果我对性能不满意，我可能会尝试将要插入的行加载到临时表中，而不是依赖于急切的假脱机。这是一种可能的实现：

DROP TABLE IF EXISTS #CONSULTANT_RECOMMENDED_TEMP_TABLE;
CREATE TABLE #CONSULTANT_RECOMMENDED_TEMP_TABLE (
    ID BIGINT,
    PRIMARY KEY (ID)
);

INSERT INTO #CONSULTANT_RECOMMENDED_TEMP_TABLE WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT new_rows.ID
FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
OPTION (MAXDOP 1);

新代码在大约 4400 毫秒内执行。我可以获得实际计划并使用 Actual Time Statistics™ 来检查在操作员级别上花费的时间。请注意，要求实际计划会为这些查询增加大量开销，因此总数将与先前的结果不匹配。

╔═════════════╦═════════════╦══════════════╗
║  operator   ║ first query ║ second query ║
╠═════════════╬═════════════╬══════════════╣
║ big scan    ║ 1771        ║ 1744         ║
║ little scan ║ 163         ║ 166          ║
║ sort        ║ 531         ║ 530          ║
║ merge join  ║ 709         ║ 669          ║
║ spool       ║ 3202        ║ N/A          ║
║ temp insert ║ N/A         ║ 422          ║
║ temp scan   ║ N/A         ║ 187          ║
║ insert      ║ 3122        ║ 1545         ║
╚═════════════╩═════════════╩══════════════╝

与使用临时表的计划相比，带有急切假脱机的查询计划似乎在插入和假脱机运算符上花费了更多的时间。

为什么使用临时表的计划更有效率？无论如何，急切的线轴不是主要只是一个内部临时表吗？我相信我正在寻找专注于内部的答案。我能够看到调用堆栈有何不同，但无法弄清楚大局。

如果有人想知道，我在 SQL Server 2017 CU 11 上。这是填充上述查询中使用的表的代码：

DROP TABLE IF EXISTS dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR;

CREATE TABLE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR (
ID BIGINT NOT NULL,
PRIMARY KEY (ID)
);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (MAXDOP 1);


DROP TABLE IF EXISTS dbo.A_HEAP_OF_MOSTLY_NEW_ROWS;

CREATE TABLE dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (
ID BIGINT NOT NULL
);

INSERT INTO dbo.A_HEAP_OF_MOSTLY_NEW_ROWS WITH (TABLOCK)
SELECT TOP (1900000) 19999999 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
sql-server sql-server-2017

2 个回答

  1. Best Answer

    这就是我所说的手动万圣节保护。

    您可以在我的文章Optimizing Update Queries中找到它与更新语句一起使用的示例。必须小心保留相同的语义，例如，在执行单独的查询时锁定目标表以防止所有并发修改，如果这与您的场景相关。

    为什么使用临时表的计划更有效率？无论如何，急切的线轴不是主要只是一个内部临时表吗？

    假脱机具有临时表的一些特征，但两者并不完全等价。特别是，spool 本质上是对 b-tree 结构的逐行无序插入。它确实受益于锁定和日志记录优化，但不支持批量加载优化。

    因此，通常可以通过以一种自然的方式拆分查询来获得更好的性能：将新行批量加载到临时表或变量中，然后从临时对象执行优化插入（没有显式的万圣节保护）。

    进行这种分离还可以让您更自由地分别调整原始语句的读取和写入部分。

    作为旁注，考虑如何使用行版本解决万圣节问题是很有趣的。也许未来版本的 SQL Server 将在适当的情况下提供该功能。

    正如 Michael Kutz 在评论中提到的那样，您还可以探索利用空洞填充优化来避免显式 HP 的可能性。为演示实现此目的的一种方法是ID在A_HEAP_OF_MOSTLY_NEW_ROWS.

    CREATE UNIQUE INDEX i ON dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (ID);

    有了这个保证，优化器就可以使用空洞填充和行集共享：

    MERGE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (SERIALIZABLE) AS HICETY
USING dbo.A_HEAP_OF_MOSTLY_NEW_ROWS AS AHOMNR
    ON AHOMNR.ID = HICETY.ID
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID) VALUES (AHOMNR.ID);

    合并计划

    虽然很有趣，但在许多情况下，您仍然可以通过使用精心实施的手动万圣节保护来获得更好的性能。

    • 14

  2. 为了稍微扩展 Paul 的答案，假脱机和临时表方法之间经过时间的部分差异似乎归结为缺乏对DML Request Sort假脱机计划中的选项的支持。使用未记录的跟踪标志 8795，临时表方法的经过时间从 4400 毫秒跳到 5600 毫秒。

    INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT new_rows.ID
FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
OPTION (MAXDOP 1, QUERYTRACEON 8795);

    请注意，这并不完全等同于假脱机计划执行的插入。此查询将更多数据写入事务日志。

    通过一些技巧可以反过来看到相同的效果。可以鼓励 SQL Server 使用排序而不是假脱机来进行万圣节保护。一种实现：

    INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT TOP (987654321) 
maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
ORDER BY maybe_new_rows.ID, maybe_new_rows.ID + 1
OPTION (MAXDOP 1, QUERYTRACEON 7470, MERGE JOIN);

    现在，该计划有一个 TOP N 排序运算符来代替线轴。sort 是一个阻塞运算符，因此不再需要 spool：

    在此处输入图像描述

    更重要的是，我们现在支持该DML Request Sort选项。再次查看实际时间统计信息，插入运算符现在只需要 1623 毫秒。整个计划执行大约需要 5400 毫秒，而无需请求实际计划。

    正如 Hugo解释的那样，Eager Spool 操作符确实保留了顺序。这可以通过计划最容易地看出TOP PERCENT。不幸的是，带有假脱机的原始查询不能更好地利用假脱机中数据的排序特性。

    • 5

相关问题