CODEWITHSUNDEEP
sqlsql-server-2008t-sql
David S.
David S.

Reputation: 6105

Which is faster/better: UPDATE WHERE IN or MERGE?

I have a table, TBL1, with only GUIDs.

I have another table, TBL2, in which the primary key is GUID and it also has some other columns. I want to update one of the columns in TBL2 table based on whether the GUID is in TBL1.

Which of the following queries is faster and/or more reliable to use for that?

MERGE INTO [db].[dbo].[TBL1] AS target
    USING [db].[dbo].[TBL2] as source
        ON target.GUID = source.GUID
    WHEN MATCHED THEN
        UPDATE SET
            StatusColumn = 0;

or

UPDATE [db].[dbo].[TBL1]
    SET StatusColumn=0
    WHERE GUID IN (SELECT GUID FROM [db].[dbo].[TBL2])

or maybe something else?

Upvotes: 2

Views: 1702

Answers (2)

usr
usr

Reputation: 171216

The answer to this question can only come from the execution plan. From the plan you posted (http://i.imgur.com/6vB2t.png) we can see the following:

  • IN is producing a left semi join. This is a little more efficient. There is also an optimizer weakness which causes the optimizer not to generate a semi join from an explicit join even if it could.
  • Merge is sorting rows. This is because you might get duplicates from your join! If that was impossible merge would be just as fast.
  • I guess the explicit join version is exactly as fast as merge.

Diagnosing this without a plan is just guessing. Look at the plan and/or measure. The measuring provides the answer, but the plan provides the understanding of the answer.

Upvotes: 3

Eric Petroelje
Eric Petroelje

Reputation: 60518

I think the fastest way would probably be a third option using a join:

UPDATE t1 SET StatusColumn=0
FROM db.dbo.TBL1 t1
INNER JOIN db.dbo.TBL2 t2 ON t1.guid = t2.guid

Upvotes: 1

Related Questions