How to optimize your next SQL Server update request?

Question

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

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 the dbo.TargetTable table: it contains about 100 columns and 8 non-clustered indexes. None of the indices consists of Id1, Id2, Id3 and includes TargetColumn.

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

• All indexes are disabled.
• All 8 Indexes Included
• All indexes are disabled, except for special ones, to speed up the update:

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

I get the following timings:

• 7 minutes
• 5 minutes
• 53 seconds

10 , . SQL- , .

, ?

Answer 1

, ...

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      

...

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)

Answer 2

, .

select count(*) as cnt,A.ID1,A.ID2,A.ID3 into #Temp from TargetTable as A 
group by A.ID1,A.ID2,A.ID3
having Count(*) = 1


UPDATE
dbo.TargetTable A
SET
TargetColumn = 0
From #Temp B
WHERE
TargetColumn = 1
and A.Id1 = B.Id1
    AND A.Id2 = B.Id2
    AND A.Id3 = B.Id3

Answer 3

CTE :

create table #a (id1 int, id2 int, id3 int, targetcolumn int);
create table #b (id1 int, id2 int, id3 int);

with rowstoupdate as (
 select #a.targetcolumn, count(*) over(partition by #a.id1, #a.id2, #a.id3) totalnum
 from #a inner join #b
 on #a.id1 = #b.id1
 and #a.id2 = #b.id2
 and #a.id3 = #b.id3
)
update rowstoupdate
set targetcolumn = 0
where targetcolumn = 1
and totalnum = 1;