Using MERGE with care in SQL Server 2008

One of the more interesting new T-SQL keywords in SQL Server 2008 is the MERGE statement. It can be very useful for writing queries that allow you to UPDATE, INSERT, or DELETE as appropriate in one statement. Since NewsGator Online is running completely on SQL Server 2008, I have been eagerly looking for places where I can rewrite stored procedures that use the classic UPSERT pattern to use MERGE.

The problem is that your performance may vary depending on whether the row in the target table that you are concerned with already exists or not. If the row exists (so you are doing an UPDATE), MERGE can be much more expensive than an “UPSERT”, while if the row does not exist, MERGE can be less expensive than an “UPSERT”.

This is different than what I saw during the CTP6 timeframe, where update performance was the same between MERGE and “UPSERT”, and insert performance was much better with MERGE than with “UPSERT”. 

There was a bug relating to MERGE that was fixed in SQL Server 2008 RTM CU1, that may have affected performance, but that is just a guess on my part. My results below are using SQL Server 2008 RTM CU3 (Build 1787).

At any rate, I have two test SPs that update\insert into a 23 million row table. The results are shown below:

PRINT 'MERGE'
EXEC dbo.MergePostCategory 5, 'volt', 'keyword', 0.60
PRINT ''
PRINT 'UPSERT'
EXEC dbo.UpsertPostCategory 5, 'volt', 'keyword', 0.60

-- Case 1 Row already exists in target table ****************************
--MERGE when row exists is 67% of batch cost
--Table 'PostCategory'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
--UPSERT when row exists is 33% of batch cost
--Table 'PostCategory'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- Case 2 Row does not exist in target table  ***************************
--MERGE when row does not exist is 47% of batch cost
--Table 'PostCategory'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
--UPSERT when row does not exist is 53% of batch cost
--Table 'PostCategory'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'PostCategory'. Scan count 0, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

My take away from this one test is that if you are relatively sure that you will be inserting more than updating, you should use MERGE instead of using an “UPSERT”, while if you will be updating more often, you should stick with “UPSERT”. Of course your mileage may vary, so you should always test both ways with your data and environment.

Technorati Tags: ,

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

One Response to Using MERGE with care in SQL Server 2008

  1. Ryan says:

    Have you tried this with more data instead of just one row? Say to upsert 100\’s of thousands or several million rows into your table with 23 milllion rows? I have yet to see MERGE perform as well as an "upsert." But, maybe that is my enviornment….?

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