海量SQL数据生成：客户端还是SQL Server端执行更优？

嘿，这个场景我太熟了——之前帮团队生成过8亿级的测试数据，踩过不少ORDER BY NEWID()的坑。咱们一步步拆解问题，找到能把速度提上去的最优方案：

NEWID()会为每一行生成一个唯一GUID，而ORDER BY操作需要对整个枚举表做全表排序——哪怕枚举表只有100条数据，每次生成主表数据都要做一次排序，10亿级的调用量叠加起来，开销直接拉满。而且外键约束的存在，会让每一行插入都触发父表的存在性检查，进一步拖慢速度。

1. 预生成枚举表的随机引用池，避免实时排序 #

最有效的办法是只做一次随机排序，把结果存成临时池，后续主表生成直接从池里取数据，不用每次都调用ORDER BY NEWID()。还能顺便控制枚举值的分布（比如模拟真实业务里的高频/低频值）。

示例代码：

-- 第一步：预生成枚举表的随机ID池（比如生成1000万条，够主表多批次使用）
SELECT TOP 1000000 e.Id INTO #EnumRandomPool
FROM EnumTable e
-- 用交叉连接快速生成重复行，数量按需调整
CROSS JOIN (SELECT TOP 1000000 1 FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2) t
-- 只排序一次，生成随机顺序
ORDER BY NEWID()

-- 第二步：主表生成时，通过行号关联随机池取ID
INSERT INTO MainTable (EnumId, OtherColumn1, OtherColumn2)
SELECT 
  erp.Id AS EnumId,
  mainData.Col1,
  mainData.Col2
FROM (
  -- 这里生成主表的其他数据，用ROW_NUMBER做关联键
  SELECT 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn,
    -- 生成其他字段的逻辑，比如随机字符串、日期等
    LEFT(NEWID(), 10) AS Col1,
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 365, '2020-01-01') AS Col2
  FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2
  -- 控制主表单次生成的行数
  TOP 1000000
) mainData
JOIN (
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn, Id FROM #EnumRandomPool
) erp ON mainData.rn = erp.rn

如果需要模拟真实分布，比如某个枚举值占70%，可以往池里多插几次这个ID：

-- 插入700万次高频ID
INSERT INTO #EnumRandomPool SELECT 1 FROM (SELECT TOP 700000 1 FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2) t
-- 插入300万次其他枚举ID（随机）
INSERT INTO #EnumRandomPool SELECT e.Id FROM EnumTable e WHERE Id !=1 CROSS JOIN (SELECT TOP 300000 1 FROM sys.all_columns ac1) t ORDER BY NEWID()

2. 临时禁用外键与索引，批量导入后再重建 #

外键约束会让每一行插入都触发父表的存在性检查，非聚集索引会让每一行插入都维护索引结构——这俩在10亿级数据面前都是性能杀手。正确的步骤是：

• 禁用目标表的外键约束：

  ALTER TABLE MainTable NOCHECK CONSTRAINT ALL;
  

• 禁用目标表的非聚集索引（主键/聚集索引可以保留，因为批量插入时聚集索引的顺序插入开销更低）：

  ALTER INDEX ALL ON MainTable DISABLE;
  

• 批量导入数据（用INSERT ... SELECT、BCP或者BULK INSERT）
• 重新启用并检查约束，重建索引：

  ALTER TABLE MainTable CHECK CONSTRAINT ALL;
  ALTER INDEX ALL ON MainTable REBUILD;
  

⚠️ 注意：预生成的随机池必须保证所有ID都是枚举表中存在的，否则约束检查会失败！

3. 用随机数代替ORDER BY NEWID()取枚举ID #

如果不想预生成池，也可以用随机数直接映射枚举表的ID范围，避免全表排序。比如枚举表ID是1到100，直接生成1-100的随机整数：

-- 生成1-100的随机整数，代替ORDER BY NEWID()
SELECT FLOOR(RAND(CRYPT_GEN_RANDOM(4)) * 100) + 1 AS RandomEnumId

或者用CHECKSUM(NEWID())生成随机整数排序，比GUID排序快得多：

-- 取随机枚举ID，比ORDER BY NEWID()高效
SELECT TOP 1 Id FROM EnumTable ORDER BY CHECKSUM(NEWID())

4. 分批次生成+并行处理 #

10亿条数据一次性生成肯定扛不住，拆成小批次（比如每次100万条），用多个会话并行插入。同时开启SQL Server的并行查询（设置合适的MAXDOP，比如等于CPU核心数），让INSERT ... SELECT利用多核加速。

另外，插入时加上TABLOCK提示，让SQL Server用最小日志模式，减少日志开销：

INSERT INTO MainTable WITH (TABLOCK) (EnumId, ...)
SELECT ...

5. 用内存优化表存临时数据 #

把预生成的随机池或者中间生成的主表数据放到内存优化表里，内存表的读写速度比磁盘表快几个数量级，适合海量临时数据的处理：

-- 创建内存优化的临时随机池
CREATE TABLE #EnumRandomPool (Id INT NOT NULL) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

• 把数据库切换到简单恢复模式，批量插入时日志生成量会大幅减少，速度更快。
• 避免在生成数据时调用昂贵的函数（比如多次调用NEWID()），尽量批量生成后再处理。
• 先在小数据集上测试策略，调整批次大小、并行数等参数，再放大到10亿级。

这些策略组合起来，我之前把生成时间从原来的72小时压缩到了8小时左右，效果非常明显。你可以根据自己的服务器配置调整细节~

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