Update statement with Subquery tuning

Question

SQL Server 2017 Enterprise

We have many update statements like below which run very frequently. We are looking for possible rewrite options for better performance. Please advise any better recommended way to rewrite this.

--This part is to only setup test data in temp table,
select top 1000 ColA, ColB, ColC
into #Temptst
from BaseTable

------------

declare @DT datetime = '2021-01-01'

Update #Temptst set ColE = ColE * 0.99
from SeekTable a with(nolock), #Temptst b
where a.ColA = b.ColA
and a.ColB = b.ColB
and a.ColC = b.ColC
and a.ColD = (select max(c.ColD) from SeekTable c with (NOlock)
where c.ColA= a.ColA and c.ColB = a.ColB and
c.ColC = a.ColC and c.ColD <= @DT)

drop table #TempCostkee

Answer 1

Hi @pokiri ，

Welcome to the microsoft TSQL Q&A forum!

In terms of efficiency, temporary tables are a good first choice.You can try to use temporary tables instead of subqueries.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Check an alternative statement:

update t
set ColE = ColE * 0.99
from #Temptst t
where exists (select * from SeekTable where colA = t.colA and colB = t.colB and colC = t.colC and colD <= @DT)