代码之家 › 专栏 › 技术社区 › Tomas Vana

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

bulkinsert tsql sql-server-2005 database sql-server

Tomas Vana · 技术社区 · 15 年前

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

有一个表A的B列ID是表B的外键。表A中有许多行具有相同的B列ID值,我们希望通过克隆B中相应的行并将行从A重定向到它们来解决此问题。

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

我们最终得到的结果是(在执行了几分钟之后)正在填充事务日志并崩溃。我们甚至尝试将工作分成合理大小的批,并逐个运行,但这最终也会填满日志。

除了以某种方式清理日志之外,还有什么方法可以处理SQL Server中数据的大容量插入/更新,这会更快,而且根本不会破坏日志吗?

6 回复 | 直到 15 年前

Aaronaught 15 年前

这是在批处理中执行此操作的另一种方法(不使用光标)。@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:1,我们想把它变成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空间,那么可以用索引物理临时表替换temp表/表变量。即使有几百万行,也不可能填满事务日志并崩溃。

Mitchel Sellers 15 年前

如果可以使操作脱机,则可以更改数据库的恢复模型,进行更改,然后再更改恢复模型。

总的来说,尽管事务日志是为了保护您、允许回滚等,但为了跟踪目的,它会随着删除等操作而变大。

注意:使用这种方法,首先确保有一个良好的备份….

HLGEM 15 年前

我想不出你为什么要这样做。当前的一对多关系有什么问题?你现在不是要有更大的桌子来完成你所有的工作吗?

但是,考虑到您要这样做,首先是要备份事务日志,频率如何?如果频率低于每十五分钟一次,那么就改变它。备份日志时,日志会被截断。如果不备份日志,则日志会增大,直到耗尽空间。另外,您为日志指定的增长百分比可能太小。再增加一点,它也会帮助你。

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

KM. 15 年前

我不确定这在许多行中是如何工作的,但请尝试一下:

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)

ChrisLively 15 年前

如果要将多对一(多A对一B)关系转换为一对一(一A对一B),那么在我看来,最简单的方法是在A中创建字段以支持此操作,然后对A进行简单更新,将值从B复制到A中。

这样,您就完全摆脱了B,并且可以在一个更新查询中执行更改。类似:

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

Dominic Rodger 15 年前

这就是我要做的:

创建一个返回两个表(A、B)中数据的查询需要在最终表(c)中,并将其放入extractdata.sql文件中:

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

   JOIN B
     on B.id = A.id

然后在命令窗口中,执行此命令以将数据提取到文件中:

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 (如果需要清除旧数据-它不会像删除一样添加到日志中)。

然后,在命令窗口中,执行此命令将数据大容量加载到表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的模式可以调整/截断/重新加载提取的数据,这样就不会每次都需要运行查询。

完成后,将恢复模式设置回满:

ALTER DATABASE [database name] SET RECOVERY FULL