refactoring sql while loop to regular inserts

I am inserting parent records and child records at the same time in a stored procedure.

Rather than have outside code make nested calls to create each parent and then each child of that parent (which is even slower than my current approach), I am giving the sql a comma separated list of child types that I put into a temp table (#TempParentChildUpdateTable) which is associated with a parent record in a table value parameter (@PenguinParentChildUpdate).

Then I loop through both, insert the parent and then insert all related children.

The problem is this while loop is not performing very well at 13 requests per second.

How do I make this faster? How do I take out the while loop?

Is there a way to do this with non-looping inserts? If so, can the string parsing happen inside the inner insert?

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

I am struggling to follow some of the logic in the loop, but I think the premise is

1. Insert to WorkFlowInstanceRole
2. Capture the inserted records then insert further children based on this.

Since step 2 requires data that is not in WorkFlowInstanceRole you need to use MERGE to add the new rows, rather than a standard insert. What this allows you to do is capture the ChildIds from the source table, even though you aren’t inserting them. Something like this should do it (I’ve had to guess at some data types):

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;

The key part is the MERGE* really, since the condition is 1=0 then this will always insert. Unlike INSERT the OUTPUT clause on a merge will allow you to capture both the newly inserted identity value, and the non-inserted ChildIds column.

This is output into a temporary table, which you can then use, along with CROSS APPLY STRING_SPLIT() to insert the child records.

There may be some data errors, and logic may not be 100% perfect, but this should hopefully be a bit step in the right direction.

---

^{*MERGE has a number of known bugs, and I’d generally advise to steer clear, but I am not aware of any alternative that would allow you to capture the newly inserted identity value, and the non-inserted ChildIds column, and as far as I am aware none of these bugs affect this operation (Anecdotally, in that I have never encountered an issue using this method).}

---