将SQL while循环重构为常规插入

Question

我在一个存储过程中同时插入父记录和子记录。

而不是让外部代码进行嵌套调用来创建每个父级，然后创建父级的每个子级(这比我目前的方法更慢)，我给sql一个逗号分隔的子类型列表，我放入一个临时表(#TempParentChildUpdateTable)，它与表值参数(@PenguinParentChildUpdate)中的父级记录相关联。

然后循环遍历两者，插入父元素，然后插入所有相关的子元素。

问题是这个while循环在每秒13个请求时执行得不是很好。

我如何使它更快?如何取出while循环?

是否有一种方法可以用非循环插入来做到这一点?如果是这样，字符串解析可以在内部插入中进行吗?

DECLARE @RowCnt int = 0;
DECLARE @CounterId int = 1;
SELECT @RowCnt = COUNT(*) FROM @PenguinParentChildUpdate;
WHILE @CounterId <= @RowCnt
BEGIN
SELECT @GrandparentId = WorkflowInstanceId,
@ParentType = ParentType,
@ChildIds = ChildIds
FROM @PenguinParentChildUpdate
-- insert parent record
INSERT INTO WorkflowInstanceRole (ParentType, GrandparentId)
VALUES(@ParentType, @GrandparentId)
SET @ParentId = SCOPE_IDENTITY()

-- identify children
-- convert comma separated list (e.g. 4,91,12,6) into separate rows
INSERT INTO #TempParentChildUpdateTable (sq.ChildId, sq.ParentId)
select convert(int, value) ChildId, @ParentId RoleId FROM string_split(@ChildIds, CHAR(44))
-- insert children 7787+ =
IF (@ChildIds IS NOT NULL AND LEN(LTRIM(RTRIM(@ChildIds))) > 0)
BEGIN
INSERT INTO dbo.PenguinParentChild
(
grandparentId,
childid,
)
select
@ParentId,
childId,
from
#TempParentChildUpdateTable tsru
where
tsru.ParentId = @ParentId
END
SET @CounterId = @CounterId + 1
END

Answer 1

我正在努力遵循循环中的一些逻辑，但我认为前提是

1. 插入到WorkFlowInstanceRole
2. 捕获插入的记录，然后在此基础上插入更多的子记录。

由于步骤2需要的数据不在WorkFlowInstanceRole中，因此需要使用MERGE来添加新行，而不是标准插入。这允许您从源表中捕获ChildIds，即使您没有插入它们。像这样的东西应该可以做到(我不得不猜测一些数据类型):

DECLARE @Output TABLE 
(   
ParentId INT, 
ParentType INT, 
GrandParentId INT, 
ChildIDs VARCHAR(MAX)
);
MERGE INTO WorkflowInstanceRole AS t
USING @PenguinParentChildUpdate AS s
ON 1 = 0 -- Will never be true so will always insert
WHEN NOT MATCHED THEN 
INSERT (ParentType, GrandparentId)
VALUES (s.ParentType, s.WorkflowInstanceId)
OUTPUT inserted.ParentId, inserted.ParentType, inserted.GrandParentId, s.ChilDIds
INTO @Output (ParentId,ParentType, GrandParentId, ChildIDs);
INSERT INTO dbo.PenguinParentChild (GrandParentId, ChildId)
SELECT  o.ParentId,
CONVERT(int, ss.value) 
FROM    @Output AS o
CROSS APPLY STRING_SPLIT(i.ChileIds, CHAR(44)) AS ss;

关键部分是MERGE*真的，因为条件是1=0，所以这将始终插入。与INSERT不同，合并中的OUTPUT子句将允许您捕获新插入的标识值和未插入的ChildIds列。

这将输出到一个临时表中，然后您可以使用它与CROSS APPLY STRING_SPLIT()一起插入子记录。

可能会有一些数据错误，逻辑可能不是100%完美，但这应该是朝着正确方向迈出的一步。

---

^{*MERGE有许多已知的错误，我通常建议避开，但我不知道有任何替代方法可以让您捕获新插入的标识值和未插入的ChildIds列，据我所知，这些错误都不会影响此操作(有趣的是，我从未遇到过使用此方法的问题)。}

---