Pros and Cons of using the MERGE statement versus EXCEPT in identifying changed dimensional data

Question

Pros and Cons of using the MERGE statement versus EXCEPT in identifying changed dimensional data

Phil Church

SSC-Addicted

Points: 463
More actions
June 20, 2011 at 5:58 am

#252210

After looking over the MERGE statement it appears that there is line of code in the WHERE clause for every column in a table as seen in the example below:
This same code could be done with a simple EXCEPT statement between the 2 tables with the result set being inserted into a #temp table for updating existing and inserting new records. What is the benefit of using the MERGE statement or is it simply a different way of accomplishing the same result?
MERGE dbo.Client_SCD1 AS DST
USING CarSales.dbo.Client AS SRC
ON (SRC.ID = DST.BusinessKey)
WHEN NOT MATCHED THEN
INSERT (BusinessKey, ClientName, Country, Town, County, Address1, Address2, ClientType, ClientSize)
VALUES (SRC.ID, SRC.ClientName, SRC.Country, SRC.Town, SRC.County, Address1, Address2, ClientType, ClientSize)
WHEN MATCHED
AND (
ISNULL(DST.ClientName,'') <> ISNULL(SRC.ClientName,'')
OR ISNULL(DST.Country,'') <> ISNULL(SRC.Country,'')
OR ISNULL(DST.Town,'') <> ISNULL(SRC.Town,'')
OR ISNULL(DST.Address1,'') <> ISNULL(SRC.Address1,'')
OR ISNULL(DST.Address2,'') <> ISNULL(SRC.Address2,'')
OR ISNULL(DST.ClientType,'') <> ISNULL(SRC.ClientType,'')
OR ISNULL(DST.ClientSize,'') <> ISNULL(SRC.ClientSize,'')
)
THEN UPDATE
SET
DST.ClientName = SRC.ClientName
,DST.Country = SRC.Country
,DST.Town = SRC.Town
,DST.Address1 = SRC.Address1
,DST.Address2 = SRC.Address2
,DST.ClientType = SRC.ClientType
,DST.ClientSize = SRC.ClientSize
;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

The MERGE statement evaluates the predicates in ON clause and applies all assignments in the WHEN MATCHED...THEN clause. What are you trying to prevent by adding the additional checks...a trigger from firing? The I/O of writing to the row when there truly are no updates to do?

This code...

AND (

ISNULL(DST.ClientName,'') <> ISNULL(SRC.ClientName,'')

OR ISNULL(DST.Country,'') <> ISNULL(SRC.Country,'')

OR ISNULL(DST.Town,'') <> ISNULL(SRC.Town,'')

OR ISNULL(DST.Address1,'') <> ISNULL(SRC.Address1,'')

OR ISNULL(DST.Address2,'') <> ISNULL(SRC.Address2,'')

OR ISNULL(DST.ClientType,'') <> ISNULL(SRC.ClientType,'')

OR ISNULL(DST.ClientSize,'') <> ISNULL(SRC.ClientSize,'')

)

...will add a lot of overhead to the command and unless you have specific concerns about updating the row with the same data that was already there then I would omit it.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Sorry, almost missed the point...

Using EXCEPT you'll be incurring a lot of the same comparison overhead by comparing every column in both sets. Have you run both methods side by side? What have your findings been in terms of performance when comparing the two methods?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Functions like that on your columns is going to lead to scans. Period. You can't get around it. I would not recommend that approach. As you add data to the system, this query will get slower and slower and no indexes can possibly help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning