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
I am struggling to follow some of the logic in the loop, but I think the premise is
- Insert to WorkFlowInstanceRole
- 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
OUTPUT clause on a merge will allow you to capture both the newly inserted identity value, and the non-inserted
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).