CODEWITHSUNDEEP
sqlsql-serverperformancet-sqlsql-server-2012
Deep Kalra
Deep Kalra

Reputation: 1428

How TSQL Merge works? Performance against UPDATE and INSERT

Can someone help me understand how TSQL merge actually works? and help me understand when the UPDATE/INSERT will be faster and when MERGE will be faster?

I know its a very vague question and not related to one particular case I am working on. Hope this is allowed on StackOverflow.

If you can give me relevant links that will work as well. Thanks.

Thanks for pointing me to: SQL Server : MERGE performance but I feel this is more related to the indexes then merge and hence my question is not a duplicate of this.

Upvotes: 3

Views: 2476

Answers (1)

usr
usr

Reputation: 171216

The merge implementations does a full outer join on source and target. Depending on what side-effecting clauses you specified this can be reduced to easier joins such as left or inner joins.

From the join result there are compute scalar operators that compute what action is supposed to happen and what values are going to be used. This result is streamed into an operator that does the writes.

This is very simplified. The difference to normal DML is almost zero if you only specify one side-effecting clause. This shows that merge does not have an inherent performance disadvantage.

In fact it has an advantage in the sense that it needs to do pass over the data only once. Often, merge is faster than multiple statements doing the same thing.

  • The optimizer can see all DML at once
  • One pass over the data instead of one per statement
  • All index writes are sorted by index key. It's better to do this once instead of multiple times
  • Per-statement overhead only once

It can use a little more CPU if you use merge in a way that does not benefit from any of these points.

Performance really depends on the schema, the shape of the merge and on the data. I can construct you cases where merge is slightly slower and cases where it is significantly faster.

Upvotes: 1

Related Questions