I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown below. The tricky part is I need to insert rows when they're not matched in the target table but also need to insert records when there are updated and have shares left over.
INSERT INTO dbo.#SubscriptionPeriodTemp (UserId, Shares, ProcessedShares, StartDate, EndDate) SELECT UserId, Shares, ProcessedShares, StartDate, EndDate FROM ( MERGE INTO dbo.SubscriptionPeriod AS sp USING #ResponderOrderItem AS ob ON (ob.UserId = sp.UserId AND ob.ContentId = sp.ContentId AND sp.Status = @SubscriptionPeriodStatusOpen AND sp.EndDate > @StartDate AND sp.StartDate < @EndDate) WHEN MATCHED THEN UPDATE SET sp.Status = CASE WHEN @IsRespondingOrderOfBuyType = 1 THEN @SubscriptionPeriodStatusPurchased ELSE @SubscriptionPeriodStatusSold END, sp.ProcessedDate = SYSUTCDATETIME() WHEN NOT MATCHED BY TARGET THEN INSERT (UserId, ContentId, Status, Shares, StartDate, EndDate, CreateDate) VALUES (UserId, @ContentId, @SubscriptionPeriodStatusOpen, ob.ProcessedShares, @StartDate, @EndDate, SYSUTCDATETIME()) OUTPUT $action, inserted.UserId, inserted.Shares, ob.ProcessedShares, inserted.StartDate, inserted.EndDate ) AS Changes (Action, UserId, Shares, ProcessedShares, StartDate, EndDate) WHERE Action = 'UPDATE' AND Shares - ProcessedShares > 0
Is there a more efficient option?
I should have been a bit more specific. I'd like to increase the performance of a specific stored procedure in my database and the execution plan when I run the proc shows 90% of the work is being done with this section of the code. with ~30,000 records in the
dbo.SubscriptionPeriod table it takes ~20 seconds. This procedure is meant to be a high throughput proc being called 1000s of times per second. I think I have a long way to go :)
Click here for the execution plan.
Click here for the database script (70MB file). Please see line 102,786 in the stored procedure named
spAddOrderItem for the area of concern
Click here for the script to run the procedure with the exact data I'm using for testing.