如何高效向SQL的Artikel表插入1000万条产品数据？

原语句的性能瓶颈 #

• 单条插入+GO 10000000循环：每次插入都要单独触发事务日志写入、锁机制，1000万次的累积开销极大
• 每次插入都执行SELECT TOP 1 Hersteller FROM Artikel ORDER BY NEWID()：全表扫描+随机排序，随着表数据量增大，该查询耗时会持续上升
• 单值生成随机数据的方式效率低下，无法利用批量处理的优势

优化方案 #

1. 批量插入+数字生成表批量造数 #

利用CTE生成连续数字，一次性生成批量数据，大幅减少插入次数。示例中每次插入10000条，循环1000次即可完成1000万条插入，比单条插入效率提升数倍：

-- 用系统视图生成10000行的数字序列
WITH Numbers AS (
    SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Num
    FROM sys.all_columns c1
    CROSS JOIN sys.all_columns c2
)
INSERT INTO Artikel (Hersteller, Artikelnummer, Artikelnamen, Artikelbeschreibung, Preis)
SELECT
    (SELECT TOP 1 Hersteller FROM Artikel ORDER BY NEWID()),
    FLOOR(RAND(CHECKSUM(NEWID(), Num)) * (100000000000 - 101 + 1)) + 101,
    REPLACE(NEWID(), '-', ''),
    REPLACE(NEWID(), '-', ''),
    ROUND(RAND(CHECKSUM(NEWID(), Num)) * 9999, 2)
FROM Numbers;
GO 1000

2. 优化Hersteller的随机获取逻辑 #

如果原表中已有Hersteller数据，先将其存入临时表并添加自增ID，通过随机数关联获取，避免每次全表扫描：

-- 提前缓存Hersteller列表到临时表
SELECT Hersteller, ROW_NUMBER() OVER (ORDER BY Hersteller) AS ID
INTO #TempHersteller
FROM Artikel
GROUP BY Hersteller;

DECLARE @MaxHerstellerID INT = (SELECT MAX(ID) FROM #TempHersteller);

-- 批量插入时通过随机ID关联获取Hersteller
WITH Numbers AS (
    SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Num
    FROM sys.all_columns c1
    CROSS JOIN sys.all_columns c2
)
INSERT INTO Artikel (Hersteller, Artikelnummer, Artikelnamen, Artikelbeschreibung, Preis)
SELECT
    th.Hersteller,
    FLOOR(RAND(CHECKSUM(NEWID(), n.Num)) * (100000000000 - 101 + 1)) + 101,
    REPLACE(NEWID(), '-', ''),
    REPLACE(NEWID(), '-', ''),
    ROUND(RAND(CHECKSUM(NEWID(), n.Num)) * 9999, 2)
FROM Numbers n
CROSS APPLY (SELECT Hersteller FROM #TempHersteller WHERE ID = FLOOR(RAND(CHECKSUM(NEWID(), n.Num)) * @MaxHerstellerID) + 1) th;
GO 1000

DROP TABLE #TempHersteller;

3. 临时关闭非必要约束与索引 #

插入大量数据前，临时禁用非聚集索引、外键约束，减少插入时的索引维护开销，完成后再重建/启用：

-- 禁用非聚集索引
ALTER INDEX ALL ON Artikel DISABLE;

-- 禁用外键约束（若存在）
ALTER TABLE Artikel NOCHECK CONSTRAINT ALL;

-- 执行批量插入操作...

-- 重建索引
ALTER INDEX ALL ON Artikel REBUILD;

-- 启用外键约束
ALTER TABLE Artikel CHECK CONSTRAINT ALL;

4. 调整事务日志模式 #

若数据库为完整恢复模式，临时切换为简单恢复模式可减少日志写入压力，插入完成后恢复原模式：

ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE;

-- 执行插入操作...

ALTER DATABASE YourDatabaseName SET RECOVERY FULL;

注意事项 #

• 批量插入的批次大小可根据服务器性能调整，比如每次插入50000条，避免占用过多内存
• 若Artikelnummer有唯一约束，需避免随机冲突，可改用自增列+前缀，或基于NEWID()生成唯一数字
• 先测试小批量数据验证逻辑，再执行全量插入

内容的提问来源于stack exchange，提问作者Mike