sql-server - 关于合并操作的高排序代价

  显示原文与译文双语对照的内容
0 0

我使用 MERGE 特性将数据插入到表中,使用大容量导入表作为源。 ( 如这里描述的 )

这是我的查询:

DECLARE @InsertMapping TABLE (BulkId int, TargetId int);
MERGE dbo.Target T
USING dbo.Source S
ON 0=1 WHEN NOT MATCHED THEN 
 INSERT (Data) VALUES (Data)
OUTPUT S.Id BulkId, inserted.Id INTO @InsertMapping; 

当通过显示实际执行计划来评估性能时,我看到在主键索引上执行了高成本排序。 我不知道,因为主键应该已经经排序了,所以不需要对它的进行额外排序。

Execution Plan

由于这样的排序开销,查询需要几秒钟才能完成。 是否有办法加速插入? 也许一些指数暗示或者附加指数? 这样的插入不应该花费那么长的时间,即使有数千条条目。

时间:原作者:6个回答

0 0

我可以用下面的方法重现这个问题

CREATE TABLE dbo.TargetTable(Id int IDENTITY PRIMARY KEY, Value INT)
CREATE TABLE dbo.BulkTable(Id int IDENTITY PRIMARY KEY, Value INT)
INSERT INTO dbo.BulkTable
SELECT TOP (1000000) 1
FROM sys.all_objects o1, sys.all_objects o2
DECLARE @TargetTableMapping TABLE (BulkId INT,TargetId INT);
MERGE dbo.TargetTable T
USING dbo.BulkTable S
ON 0 = 1
WHEN NOT MATCHED THEN 
 INSERT (Value)
 VALUES (Value)
OUTPUT S.Id AS BulkId,
 inserted.Id AS TargetId
INTO @TargetTableMapping; 

这在聚集索引合并运算符之前提供了一个排序。

Plan

排序位于 Expr1011, Action1010 上,这两个列都是来自以前运算符的计算列输出。

Expr1011 是调用内部和无文档函数 getconditionalidentity 生成 TargetTable 中标识列的id 列的结果。

Action1010 是一个标志,指示插入,更新,删除。 在这种情况下,它总是 4,因为这个 MERGE 语句可以执行的惟一操作是 INSERT

排序中的排序原因是聚集索引合并运算符具有DMLRequestSort属性集。

enter image description here

根据需要插入的行数设置 DMLRequestSort 属性。 Paul White在这里的评论中解释

添加 [DMLRequestSort] 以支持在 2008中最小限度地记录INSERT语句的能力。 最小日志记录的前提条件之一是,行是以聚集键顺序向插入运算符提供的。

在聚集索引键顺序中插入表可以能更有效,因为它减少了随机的IO和碎片。

如果函数 getconditionalidentity 在升序( 似乎是合理的) 中返回生成的标识值,则对排序的输入将以所需顺序为单位。 计划中的排序将在逻辑上是冗余的( 以前有一个类似的问题,它的中有不必要的) 。

可以让表达式变得更加模糊,从而消除排序。

DECLARE @TargetTableMapping TABLE (BulkId INT,TargetId INT);
DECLARE @N BIGINT = 0x7FFFFFFFFFFFFFFF
MERGE dbo.TargetTable T
USING (SELECT TOP(@N) * FROM dbo.BulkTable) S
ON 1=0
WHEN NOT MATCHED THEN 
 INSERT (Value)
 VALUES (Value)
OUTPUT S.Id AS BulkId,
 inserted.Id AS TargetId
INTO @TargetTableMapping; 

这样可以减少估计的行计数,并且计划不再具有排序。 你将需要测试这个插件是否确实能够改善性能。 可能会让事情更糟糕。

原作者:
...