How to optimize the following update query on SQL Server?

Question

I need to optimize the following update query on SQL Server 2005/2008:

UPDATE
dbo.TargetTable A
SET
TargetColumn = 0
WHERE
TargetColumn = 1
AND 1 =
(
    SELECT COUNT(*)
    FROM dbo.TargetColumn B
    WHERE
                A.Id1 = B.Id1
                AND A.Id2 = B.Id2
                AND A.Id3 = B.Id3
)

About table dbo.TargetTable: it has about 100 columns and 8 non-clustered indexes. None of the indexes is made of Id1, Id2, Id3 and includes TargetColumn.

I tried to run this update on 3 combinations of indexes (size of table about 200000 records):

1. All indexes disabled
2. All 8 indexes enabled
3. All indexes disabled except the special one what intended to speedify the update:

create index idx0 on dbo.TargetTable (Id1, Id2, Id3) include (TargetValue)

I get the following timings:

1. 7 minutes
2. 5 minutes
3. 53 seconds

But then I tried this query on the table with size about 10 million records none of the cases was able to finish. In each case SQL server gave me strange errors about buffer pool running out of memory.

Are there any other ways to optimize this query apart from using the special index?

Answer 1

I believe following update is equivalent...

UPDATE  dbo.TargetTable
SET     TargetColumn = 0
FROM    dbo.TargetTable A
        INNER JOIN (
          SELECT  A.Id1
                  , A.Id2
                  , A.Id3
          FROM    dbo.TargetTable A
                  INNER JOIN dbo.TargetColumn B ON A.Id1 = B.Id1
                                                   AND A.Id2 = B.Id2 
                                                   AND A.Id3 = B.Id3
          GROUP BY
                  A.Id1
                  , A.Id2
                  , A.Id3
          HAVING  COUNT(*) = 1
        ) B ON B.Id1 = A.Id1
               AND B.Id2 = A.Id2
               AND B.Id3 = A.Id3
WHERE   A.TargetColumn = 1      

...and benefits from following Covering Indexes

CREATE INDEX IX_TARGETTABLE_ID1_ID2_ID3 ON dbo.TargetTable (Id1, Id2, Id3) INCLUDE (TargetColumn)
CREATE INDEX IX_TARGETCOLUMN_ID1_ID2_ID3 ON dbo.TargetColumn (Id1, Id2, Id3)