Post reply

Performance benifits of MERGE ?

  • January 24, 2008 at 11:28 am

    #183249

    Hi there,

    Is there a performance benifit to using a MERGE statement to update/insert/delete data vs a similar programatic way?

    A sample MERGE

    MERGE Order_Archive AS OA

    USING

    (SELECT order_ID, -- The initial query to find records

    load_date = MIN(CONVERT(VARCHAR(8), GETDATE(), 112)),

    order_total = SUM(order_amount),

    order_count = COUNT(*)

    FROM order_details

    WHERE order_year <= 2008

    GROUP BY order_ID)

    AS archive_cte (order_ID, load_date, order_total, order_count) ON (oa.CustomerID = archive_cte.CustomerID

    AND oa.SalesDate = archive_cte.LoadDate)

    WHEN NOT MATCHED THEN -- The INSERT statement used when no match is found

    INSERT (order_ID, order_date, order_amount, order_count, create_date, update_date)

    VALUES( archive_cte.order_ID, archive_cte.load_date, archive_cte.order_total,

    archive_cte.order_count, GETDATE(), GETDATE())

    WHEN MATCHED THEN -- The UPDATE statement used when a match is found

    UPDATE SET oa.order_amount = oa.order_amount + archive_cte.order_amount,

    oa.TotalSalesCount = oa.order_count + archive_cte.order_count,

    oa.update_date = GETDATE();

    You could just as easily do two IF statements. I don't currently have 2008 installed so I can't really test it out myself.

    Thanks,

    Chris

  • January 24, 2008 at 10:47 pm

    #771589

    I haven't made it to trying 2008 yet, but I would guess that there may be some kind of benefit from reduced locking (depending on the isolation level and the desired actions). I would be very interested in knowing how a merge statement handles the different locks should anyone have a good link.

  • February 8, 2008 at 1:57 pm

    #776606

    matt stockham (1/24/2008)

    I haven't made it to trying 2008 yet, but I would guess that there may be some kind of benefit from reduced locking (depending on the isolation level and the desired actions). I would be very interested in knowing how a merge statement handles the different locks should anyone have a good link.

    http://weblogs.sqlteam.com/mladenp/archive/2007/08/03/60277.aspx

  • February 8, 2008 at 2:27 pm

    #776618

    Thanks Adam, just what I wanted.

  • August 12, 2008 at 2:20 pm

    #857039

    MERGE provides approximately the same performance as the previous "upsert" solutions. If you need performance and upsert/merge operations, and the majority of the operations are updates then try the TRY...[UPDATE]... CATCH [INSERT] method. You'll be surprised... 🙂

    -- Erik http://blog.rollback.hu

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply