SQL存储过程父子记录插入WHILE循环改写为无循环批量插入方案

核心优化思路 #

• 用集合操作替代逐行WHILE循环，避免逐行插入的IO、上下文切换开销
• 用OUTPUT子句捕获批量插入父记录时自动生成的主键ID，省去逐行取SCOPE_IDENTITY()的开销
• 字符串拆分逻辑直接嵌入子记录插入语句，移除不必要的临时表读写

优化后完整代码 #

-- 声明表变量存储批量插入父表后返回的父ID与对应子ID字符串的关联关系
DECLARE @ParentInsertResult TABLE (
    ParentId INT,
    ChildIds VARCHAR(MAX)
)

-- 第一步：批量插入所有父记录，同时捕获生成的父ID和对应子ID字符串
INSERT INTO WorkflowInstanceRole (ParentType, GrandparentId)
OUTPUT inserted.Id, source.ChildIds INTO @ParentInsertResult(ParentId, ChildIds)
SELECT 
    ParentType, 
    WorkflowInstanceId AS GrandparentId
FROM @PenguinParentChildUpdate
-- 可根据需要加过滤条件，比如排除不需要插入的父记录

-- 第二步：批量拆分每个父记录的子ID列表，直接插入子表
INSERT INTO dbo.PenguinParentChild (grandparentId, childid)
SELECT 
    pir.ParentId,
    CONVERT(INT, ss.value) AS childid
FROM @ParentInsertResult pir
CROSS APPLY STRING_SPLIT(pir.ChildIds, CHAR(44)) ss
-- 自动过滤NULL或空字符串的子ID列表，不需要额外IF判断
WHERE pir.ChildIds IS NOT NULL 
  AND LEN(LTRIM(RTRIM(pir.ChildIds))) > 0
  AND ISNUMERIC(ss.value) = 1 -- 可选：增加非数字子ID的过滤逻辑避免报错

优化效果说明 #

1. 完全移除循环逻辑，所有操作均为批量集合操作，性能可提升数十倍，原有每秒13次请求的瓶颈可突破至每秒数百次甚至更高，具体提升幅度随单次处理的父记录数量增加而更明显
2. 修复原有循环逻辑的隐含错误：原有代码循环内未按计数器取对应行的@PenguinParentChildUpdate记录，实际每次循环都会覆盖变量为表的最后一行数据，存在逻辑错误
3. 省去临时表#TempParentChildUpdateTable的读写开销，整体逻辑更简洁易维护

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