What is the performance of "Merge" clause in sql server 2008?

Question

Merge can performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Is anyone familiar with the performance to use "Merge" versus the traditional logic to check existence and decide the update or insert then?

Thanks!

Answer 1

MERGE is generally faster because there are less DML operations, and it's also the recommended approach from the documentation. With the "traditional" way, you're processing the tables twice - once to check for existence and once to execute your DML. With MERGE, everything is encapsulated with one operation - hence one set of locks issued, one set of logging, etc. etc.

However, it is pretty subjective on what your queries are actually doing. You should probably take a look at Optimizing MERGE Statement Performance on MSDN.

Answer 2

Technet has some information in Optimizing MERGE Statement Performance. It basically says that performance is better than doing individual checks, because only one pass over the data is required. However indexes etc. are of course still important.