Create Table # T1
(
Id int identity (1, 1)
Not null,
BH int
)
Insert into # T1
Select 1, 492417420
Select *
From # T1
Drop table # T1
-- Update a piece of data, which actually affects 0 rows
-- 4 seconds
Update test. [sgoa_data]. [DBO]. [NSB]
Set flag = 1 from
Test. [sgoa_data]. [DBO]. [NSB] As a join (select B. bh from test. [sgoa_data]. [DBO]. [NSB] as B,
# T1 as C
Where B. bH = C. bh) E
On a. bH = E. bh
-- 25 seconds
Update test. [sgoa_data]. [DBO]. [NSB]
Set flag = 1 from
Test. [sgoa_data]. [DBO]. [NSB] As,
# T1 as B
Where a. bH = B. bh
Select. * From test. [sgoa_data]. [DBO]. [NSB] As a join (select B. * From test. [sgoa_data]. [DBO]. [NSB] as B,
# T1 as C
Where B. bH = C. bh) E
On a. bH = E. bh
Troubleshooting
-- 0 seconds
Update a
set flag = 1
from link_39.sgoa_data.dbo.nsb as a
join (select distinct
B. BH
from link_39.sgoa_data.dbo.nsb as B,
# T1 as C
where B. BH = C. BH
) e on. BH = E. BH