1

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?

EDIT 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.

  • Why are you fixed on avoiding MERGE? It might well be the fastest option (or not, but impossible to say). – usr Nov 27 '15 at 14:52
  • The plan looks fine and totally reasonable. If this takes 20s as you say there is either blocking or random IO going on. If this was without blocking and in-memory I'd estimate one second per execution. I think expecting 30m rows written per second is a bit much to expect! – usr Nov 28 '15 at 12:01
  • Run the proc single-threadedly in a loop. Then, in a separate session repeatedly run sp_whoisactive and look at the wait types. What waits come up most often? Is it IO or locks? This is a simple way to profile the query. – usr Nov 28 '15 at 12:02
  • I will try your running the proc single-threadedly in a loop. However it wasn't 20s for 30m rows but only 30k rows. – Ryan Nov 28 '15 at 16:20
  • Here's what I got: CXPACKET, IO_COMPLETION and PAGEIOLATCH_SH – Ryan Nov 29 '15 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.