我的任务是将行从源表导入到目标表,同时在途中对列进行一些映射。这些行由 GUID 标识,并且只应导入不存在的行。作业需要进行批处理以实现中断和恢复,并避免日志过度增长。这些表位于同一台服务器上的不同数据库中。可能有几千到几百万条记录。
我设法想出的最好的就是这个。
INSERT INTO DST_DB.dbo.dst_table (MyGUID, Col1, Col2, ...)
SELECT TheirGUID, ColA, ColB, ...
FROM SRC_DB.dbo.src_table AS SRC1
WHERE SRC1.TheirGUID IN (
SELECT TOP 10000 TheirGUID
FROM SRC_DB.dbo.src_table AS SRC0
WHERE SRC0.TheirGUID NOT IN (
SELECT MyGUID FROM DST_DB.dbo.dst_table
)
ORDER BY SRC0.CreationTime
)
说明
TOP 负责批处理。
两个表都聚集在 CreationTime 上,因此 ORDER BY 只是一种保险。
内层select是为了避免ColA, ColB, ...
在TOP生效后才去src_table取数据,其实帮助很大。我也尝试过基于左连接的版本,但这似乎对查询计划和性能影响不大。
问题是当 dst_table 填满时性能会下降很多。它以大约 5000 行/秒的速度开始,并在接近尾声时减慢到 500 行。据我所知,这主要是由于最里面的“leftAntiSemiJoin”涉及越来越多的行。
挑战在于找到一种方法来避免NOT IN (SELECT..
重复这样做,同时仍然获得批处理的好处。如果我可以在开始时将所有NOT IN
GUID 选择到 a 中#TempTable
,则无需为每个批次更新它们 -除了实际的批处理。
我知道我可以使用游标循环,但这会使它成为一个逐行操作,我预计它本质上会慢得多。我直觉上想做的是从我的 中批量“使用”GUID #TempTable
,同时构建我的 INSERT <- SELECT。
有什么办法可以使这项工作吗?
更新
我已经在下面发布了我实际实施的解决方案作为答案。
似乎只循环插入一系列日期可能会更好。
例如:
只需跟踪开始和结束,并在每次运行时将它们递增固定数量。
如果您真的打算按固定行数对其进行批处理,您仍然可以使用类似的逻辑,跟踪最高值。这种技术称为Keyset Pagination。
注意
CreationTime
必须是唯一的。如果它不是唯一的,那么你需要这样的东西如果您将查询重写为
它会做你需要的吗?我想 TheirGUID 在两个表上都有索引。
@charlieface在这里提供了想法,但这是我实施的解决方案。这可能仍然可以改进,但第一印象是它要快得多。(我不能再次运行完整的 500GB 作业只是为了测试)。重要的是,它不会随着目标表填满而减慢速度!
使用提到的技术(在 Python 中,如果你想知道的话)准备一个开始/结束日期列表,然后像这样遍历这些日期。(也处理角落案件)
我有点不确定某些排序和索引是否可能是多余的,但这已经是一个很大的改进,代价是增加了一些额外的复杂性和在前期设置上花费的额外几秒钟。
另一个可能影响插入性能的因素是 dst_table 上的索引。对于 GUID 索引,FILLFACTOR 为 100 将导致大量错误的页面拆分。如果 dst_table 已经有很多行,并且您要向它们添加行,我会首先让聚簇 GUID 索引上的 FILLFACTOR 大约为 70,如果碎片超过 1%,我会每天执行重建索引的工作。如果你要添加到一个空表,我会把它变成一个堆,并且在 GUID 上只有一个非聚集索引;加载完成后可以添加您真正想要的索引。