MERGE Performance with February CTP of SQL Server 2008

I have been doing some testing with the February CTP of SQL Server 2008 (aka Katmai), comparing the performance of the new TSQL MERGE command to the classic "Upsert" pattern of trying an UPDATE, checking the @@ROWCOUNT and then doing an INSERT if no rows were updated.

The "Upsert" pattern is very efficient if the row you are trying to update already exists in the table, but not so good if it does not (since it tries to find the row, then does an INSERT). What I have found with my MERGE testing is that MERGE has about the same cost as "Upsert" if the row already exists, but is about half the cost of "Upsert" if the row does not exist (so you have to do the INSERT).

This makes MERGE a win/win situation compared to "Upsert".

 

-- This is an "Upsert" pattern
ALTER PROCEDURE [dbo].[UpsertPostByPostID]
(
    @PostID    bigint,
    @Description varbinary(MAX)
)
AS

    SET NOCOUNT ON;
    
    -- Try Update first
    UPDATE dbo.PostEx
    SET [Description] = @Description
    WHERE PostID = @PostID    

    --Nothing updated, insert instead
    IF @@ROWCOUNT = 0 
        BEGIN 
            INSERT INTO dbo.PostEx
                   (PostID, [Description])
            VALUES (@PostID, @Description)
        END
    
    RETURN

 

 

-- This is a MERGE
ALTER PROCEDURE [dbo].[MergePostByPostID]
(
    @PostID    bigint,
    @Description varbinary(MAX)
)
AS

    SET NOCOUNT ON;
    
    MERGE dbo.PostEx AS cp
    USING (SELECT @PostID AS pPostID, @Description AS pDescription)AS s
    ON cp.PostID = s.pPostID

    WHEN MATCHED
    AND (cp.[Description] <> s.pDescription) THEN
        UPDATE SET [Description] = s.pDescription
    WHEN NOT MATCHED THEN
        INSERT VALUES (pPostID, pDescription);
    
    RETURN

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s