Performance of the SQL MERGE vs. INSERT/UPDATE

As I wrote while exploring A Hazard of Using the SQL Merge Statement, I love MERGE because I think it is the greatest thing to happen to SQL querying since sliced bread. Clearly people have been doing the same thing that MERGE does using INSERT and/or UPDATE statements for many years before MERGE became available. But naturally, curiosity got the best of me regarding the performance of this wonderful query tool. Today we’ll try to explore some of the ways MERGE can be used and compare performance against equivalent INSERT and/or UPDATE statements.

MERGE is designed to apply both UPDATE and INSERTs into a target table from a source table. The statement can do both at once, or simply do INSERTs or only UPDATEs. One might even get the impression that INSERT and UPDATE are no longer needed. Why not always use MERGE?

MERGE can also do DELETEs from the target table, but for today we won’t explore that case.

Sample Data and Some Basic Examples

To illustrate our case, let’s set up some very simplistic source and target tables, and populate them with some data that we can demonstrate with.

Sorry if you know all this stuff and I’ve bored you, but we needed to get these basics out of the way.

Exploring the Performance of MERGE

To effectively test the performance of our alternatives, we’ll need to set up a test harness with a non-trivial number of rows in our #Source and #Target tables. You can open the Test Harness 1.sql file in the resources section of this article and follow along. The basic set-up data is as follows.

Both of these INSERT 500,000 rows and UPDATE 500,000 rows, the latter enclosed in a TRANSACTION. We omit error handling and possible ROLLBACK of the transaction for simplicity. The results that we obtained running the test harness in SQL Profiler (5 runs) are as follows:

These indicate that MERGE took about 28% more CPU and 29% more elapsed time than the equivalent INSERT/UPDATE. Not surprising considering all the complexity that MERGE must handle, but possibly forgivable for the additional safety, convenience and simplicity it represents (no need for a TRANSACTION with error handling).

Using MERGE as a Substitute for INSERT

To test the performance of MERGE as a substitute for INSERT, the same test harness that set up the #Target table can be used, but we’ll change the set up for the #Source table as follows.

WITH Tally (n) AS
(
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT INTO #Source
SELECT CASE WHEN n <= 500000 THEN 2*n-1 ELSE 2000000+n END
,1+ABS(CHECKSUM(NEWID()))%1000
FROM Tally;

A quick check of the row counts generated from Test Harness #2.sql confirms that both MERGE and INSERT insert exactly 1,000,000 rows. The results from a Profiler trace using these queries give us this:

Our MERGE was just about 8% more costly in CPU and 6% more in elapsed time. The advantage probably goes to the UPDATE for this case.

Conclusions

While the MERGE statement is a wonderfully useful query syntax that does offer some safety features over the traditional approach of UPDATE then INSERT, it does come with a slight performance penalty. Certainly the performance characteristics may vary depending on your special circumstances, so this article should only be treated as a guideline. While I will probably continue to use it simply for the convenience it offers in the INSERT/UPDATE case, it is unlikely I’d consider using it as a replacement either for INSERT or for UPDATE.

All performance tests in this article were run on a Lenovo Laptop with an Intel Core i-5 CPU @2.60 GHz and 4GB of RAM, running Windows 7 (64 bit) OS, SQL Server 2012 (64 bit).

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.