在 SQL Server 中执行大量数据操作的最佳方法是什么？

Question

我们需要在数据库中执行以下操作：

有一个表 A，其列 B_ID 是表 B 的外键。表 A 中有许多行具有相同的 B_ID 值，我们想要修复这是通过克隆 B 中的相应行并将行从 A 重定向到它们来实现的。

所有这些都相对简单，我们已经创建了一个脚本，通过迭代游标并调用存储过程来克隆表 B 中的行来解决这个问题。现在的问题是 A 和 B 表都很大，而且还有一个表 A 中的大量组指向 B 中的同一行。

我们最终得到的是（执行几分钟后）事务日志被填满并崩溃。我们甚至尝试将工作分成合理大小的批次并逐一运行它们，但这最终也会填满日志。

除了以某种方式清理日志之外，是否有某种方法可以更快地处理 SQL Server 中的数据批量插入/更新，并且根本不会破坏日志？

Answer 1

这是批量执行此操作的另一种方法（无光标）。 @KM 看起来应该可以工作，但对我来说它看起来有点慢/可怕，涉及大量锁定和扫描；如果您将工作集限制为仅新行，那么它应该非常快。

以下是测试数据的设置脚本：

CREATE TABLE Colors
(
    ColorID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    ColorName varchar(50) NOT NULL
)

CREATE TABLE Markers
(
    MarkerID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    MarkerName varchar(50) NOT NULL,
    ColorID int NOT NULL,
    CONSTRAINT FK_Markers_Colors FOREIGN KEY (ColorID)
        REFERENCES Colors (ColorID)
)

INSERT Colors (ColorName) VALUES ('Red')
INSERT Colors (ColorName) VALUES ('Green')
INSERT Colors (ColorName) VALUES ('Blue')

INSERT Markers (MarkerName, ColorID) VALUES ('Test1', 1)
INSERT Markers (MarkerName, ColorID) VALUES ('Test2', 1)
INSERT Markers (MarkerName, ColorID) VALUES ('Test3', 1)
INSERT Markers (MarkerName, ColorID) VALUES ('Test4', 2)
INSERT Markers (MarkerName, ColorID) VALUES ('Test5', 2)
INSERT Markers (MarkerName, ColorID) VALUES ('Test6', 3)
INSERT Markers (MarkerName, ColorID) VALUES ('Test7', 3)

因此，我们有一个 1:Many，我们希望将其设为 1:1。为此，首先对更新列表进行排队（我们将在一些其他组唯一列上对此进行索引，以加快稍后的合并速度）：

CREATE TABLE #NewColors
(
    MarkerID int NOT NULL,
    ColorName varchar(50) NOT NULL,
    Seq int NOT NULL,
    CONSTRAINT PK_#NewColors PRIMARY KEY (MarkerID)
)

CREATE INDEX IX_#NewColors
ON #NewColors (ColorName, Seq);

WITH Refs AS
(
    SELECT
        MarkerID,
        ColorID,
    ROW_NUMBER() OVER (PARTITION BY ColorID ORDER BY (SELECT 1)) AS Seq
    FROM Markers
)
INSERT #NewColors (MarkerID, ColorName, Seq)
SELECT r.MarkerID, c.ColorName, r.Seq - 1
FROM Refs r
INNER JOIN Colors c
    ON c.ColorID = r.ColorID
WHERE r.Seq > 1

结果将为每个需要更新的标记提供一行获得新颜色。然后插入新颜色并捕获完整输出：

DECLARE @InsertedColors TABLE
(
    ColorID int NOT NULL PRIMARY KEY,
    ColorName varchar(50) NOT NULL
)

INSERT Colors (ColorName)
OUTPUT inserted.ColorID, inserted.ColorName
INTO @InsertedColors
    SELECT ColorName
    FROM #NewColors nc;

最后合并它（这是临时表上的额外索引派上用场的地方）：

WITH InsertedColorSeq AS
(
    SELECT
        ColorID, ColorName,
        ROW_NUMBER() OVER (PARTITION BY ColorName ORDER BY ColorID) AS Seq
    FROM @InsertedColors
),
Updates AS
(
    SELECT nc.MarkerID, ic.ColorID AS NewColorID
    FROM #NewColors nc
    INNER JOIN InsertedColorSeq ic
    ON ic.ColorName = nc.ColorName
    AND ic.Seq = nc.Seq
)
MERGE Markers m
USING Updates u
    ON m.MarkerID = u.MarkerID
WHEN MATCHED THEN
    UPDATE SET m.ColorID = u.NewColorID;

DROP TABLE #NewColors

这应该非常高效，因为它只需要查询生产表一次。其他一切都将针对临时表中相对较小的数据进行操作。

测试结果：

SELECT m.MarkerID, m.MarkerName, c.ColorID, c.ColorName
FROM Markers m
INNER JOIN Colors c
    ON c.ColorID = m.ColorID

这是我们的输出：

MarkerID     MarkerName   ColorID   ColorName
1            Test1        1         Red
2            Test2        6         Red
3            Test3        7         Red
4            Test4        2         Green
5            Test5        5         Green
6            Test6        3         Blue
7            Test7        4         Blue

这应该是您想要的，对吧？没有光标，没有严重的丑陋。如果它占用了太多内存或 tempdb 空间，那么您可以用索引物理暂存表替换临时表/表变量。即使有几百万行，也不可能填满事务日志并崩溃。

Answer 2

如果您可以使操作脱机，则可以更改数据库的恢复模式，进行更改，然后将恢复模式改回来。

总的来说，事务日志是为了保护您、允许回滚等，并且当您出于跟踪目的进行删除等操作时，它会变得更大。

注意：使用这种方法时一定要先有一个非常好的备份......

Answer 3

我无法想象你为什么要这样做。当前的一对多关系有什么问题？您现在不是需要更大的表来执行所有工作吗？

但是，鉴于您想要执行此操作，首先您要进行事务日志备份，频率如何？如果频率低于每十五分钟一次，则进行更改。当您备份日志时，日志会被截断，如果您不备份日志，那么日志会不断增长，直到空间耗尽。另外，您为日志指定的增长百分比也可能太小。增加它，它也可能对你有帮助。

您可以尝试在 SSIS 中完成这项工作，但我不知道这是否真的有助于解决日志记录问题。不过，这将有助于提高完成任务的性能。

Answer 4

我不确定这将如何在很多行上工作，但尝试一下：

DECLARE @TableA table (RowID int, B_ID int)
INSERT INTO @TableA VALUES (1,1)
INSERT INTO @TableA VALUES (2,1) --need to copy
INSERT INTO @TableA VALUES (3,2)
INSERT INTO @TableA VALUES (4,2) --need to copy
INSERT INTO @TableA VALUES (5,2) --need to copy
INSERT INTO @TableA VALUES (6,1) --need to copy
INSERT INTO @TableA VALUES (7,3)
INSERT INTO @TableA VALUES (8,3) --need to copy
DECLARE @TableB table (B_ID int, BValues varchar(10))
INSERT INTO @TableB VALUES (1,'one')
INSERT INTO @TableB VALUES (2,'two')
INSERT INTO @TableB VALUES (3,'three')

DECLARE @Max_B_ID int
SELECT @Max_B_ID=MAX(B_ID) FROM @TableB

--if you are using IDENTITY, turn them off here
INSERT INTO @TableB 
        (B_ID, BValues)
        --possibly capture the data to eliminate duplication??
        --OUTPUT INSERTED.tableID, INSERTED.datavalue
        --INTO @y 
    SELECT
        dt.NewRowID, dt.BValues
        FROM (SELECT 
                  RowID, a.B_ID
                      ,@Max_B_ID+ROW_NUMBER() OVER(order by a.B_ID) AS NewRowID,b.BValues
                  FROM (SELECT
                            RowID, B_ID
                            FROM (SELECT 
                                      RowID, a.B_ID, ROW_NUMBER() OVER(PARTITION by a.B_ID order by a.B_ID) AS RowNumber
                                      FROM @TableA a
                                 ) dt
                            WHERE dt.RowNumber>1
                       )a
                      INNER JOIN @TableB  b ON a.B_ID=b.B_ID
             ) dt


UPDATE aa
    SET B_ID=NewRowID
    FROM @TableA   aa
        INNER JOIN (SELECT
                        dt.NewRowID, dt.BValues,dt.RowID
                        FROM (SELECT 
                                  RowID, a.B_ID
                                      ,@Max_B_ID+ROW_NUMBER() OVER(order by a.B_ID) AS NewRowID,b.BValues
                                  FROM (SELECT
                                            RowID, B_ID
                                            FROM (SELECT 
                                                      RowID, a.B_ID, ROW_NUMBER() OVER(PARTITION by a.B_ID order by a.B_ID) AS RowNumber
                                                      FROM @TableA a
                                                 ) dt
                                            WHERE dt.RowNumber>1
                                       )a
                                      INNER JOIN @TableB  b ON a.B_ID=b.B_ID
                             ) dt
                   ) dt2 ON aa.RowID=dt2.RowID

SELECT * FROM @TableA
SELECT * FROM @TableB

输出：

RowID       B_ID
----------- -------
1           1
2           4
3           2
4           6
5           7
6           5
7           3
8           8

(8 row(s) affected)

B_ID        BValues
----------- -------
1           one
2           two
3           three
4           one
5           one
6           two
7           two
8           three

(8 row(s) affected)

Answer 5

如果您要从多对一（多 A 到 1 B）关系转变为一对一（1 A 到 1 B）关系，那么在我看来，最简单的途径是在 A 中创建字段来支持此关系然后对 A 进行简单更新，将 B 中的值复制到其中。

通过这种方式，您可以完全摆脱 B，并且可以在一个更新查询中执行更改。像这样的东西：

update tableA SET
  col1 = B.col1,
  col2 = B.col2
from tableA A
inner join tableB on (B.ID = A.B_ID)

Answer 6

这就是我所做的：

创建一个查询，它返回两个表（A，B）中的数据完全相同
需要位于最终表 (C) 中并将其放入 ExtractData.sql 文件中：

select
    A.id,
    A.xxx,
    A.yyy,
    B.*
from
   A

   JOIN B
     on B.id = A.id

然后在 cmd 窗口中，执行以下命令将数据提取到文件中：

sqlcmd.exe -S [Server] -U [user] -P [pass] -d [dbname] -i DataExtract.sql -s "|" -h -1 -W -o ExtractData.dat

为了避免填充日志，请尝试将数据库恢复模式设置为插入之前很简单：

ALTER DATABASE [database name] SET RECOVERY SIMPLE

然后执行 TRUNCATE TABLE C （如果您需要清除旧数据 - 它不会像删除那样添加到日志中）。

然后在cmd窗口中执行此命令将数据批量加载到表C中：

bcp.exe dbname.dbo.C in ExtractData.dat -S [Server] -U [user] -P [pass] -t "|" -e ExtractData.err -r \n -c

错误记录将显示在ExtractData.err文件中，因此如果需要调整
表 C 的架构您可以调整/截断/重新加载提取的数据，这样您就不会
每次都需要运行查询。

完成后将恢复模式设置回 FULL：

ALTER DATABASE [database name] SET RECOVERY FULL