主页 / dba / 问题 / 230651
Accepted
Paul White
Asked: 2019-02-26 02:22:55 +0800 CST

单行 INSERT...SELECT 比单独的 SELECT 慢得多

  • 772

给定以下具有 400 行编号从 1 到 400 的堆表：

DROP TABLE IF EXISTS dbo.N;
GO
SELECT 
    SV.number
INTO dbo.N 
FROM master.dbo.spt_values AS SV
WHERE 
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 400;

以及以下设置：

SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

以下SELECT语句在大约6 秒内完成（demo、plan）：

DECLARE @n integer = 400;

SELECT
    c = COUNT_BIG(*) 
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE 
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

注意：@OPTIMIZE FOR该条款只是为了生成一个合理大小的重现，以捕获实际问题的基本细节，包括可能由于各种原因而出现的基数错误估计。

当单行输出写入表时，需要19 秒（演示，计划）：

DECLARE @T table (c bigint NOT NULL);

DECLARE @n integer = 400;

INSERT @T
    (c)
SELECT
    c = COUNT_BIG(*) 
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE 
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

除了插入一行之外，执行计划看起来完全相同。

所有额外的时间似乎都被 CPU 使用率消耗掉了。

为什么INSERT声明这么慢？

sql-server performance

1 个回答

  1. Best Answer

    SQL Server 选择使用行级锁扫描循环连接内侧的堆表。完整扫描通常会选择页级锁定，但表的大小和谓词的组合意味着存储引擎会选择行锁定，因为这似乎是成本最低的策略。

    故意引入的基数错误估计OPTIMIZE FOR意味着对堆的扫描次数比优化器预期的要多得多，并且它不会像通常那样引入假脱机。

    这种因素的组合意味着性能对运行时所需的锁数量非常敏感。

    该SELECT语句受益于允许在没有读取未提交数据的危险且没有行外数据时跳过行级共享锁（仅采用意向共享页级锁）的优化。

    该INSERT...SELECT语句没有从这种优化中获益，因此在第二种情况下每秒获取和释放数百万个 RID 锁，以及意向共享页面级锁。

    大量的锁定活动会占用额外的 CPU 和运行时间。

    最自然的解决方法是确保优化器（和存储引擎）获得合适的基数估计，以便他们做出正确的选择。

    如果这在实际用例中不切实际，则可以将INSERTandSELECT语句分开，并将结果SELECT保存在变量中。这将使SELECT语句受益于锁跳过优化。

    更改隔离级别也可以起作用，方法是不使用共享锁，或者确保快速进行锁升级。

    SELECT作为最后一个兴趣点，通过使用未记录的跟踪标志 8691 强制使用假脱机，可以使查询运行得比优化情况更快。

    • 21

相关问题