I have 2 update queries relatively simple but acting on around 10 million rows hence the performance and temp db is severely impacted.
--- query 1
Update dbo. test
set lasttime = ( select max(time) from dbo.test)
--- query 2
set col A = case when status = 'open' and date < DateAdd(d,-1,LastModifiedMaxDateTime) then DATEDIFF(d, date, LastModifiedMaxDateTime) END
set col B = case when status = 'open' and date < DateAdd(d,-1,LastModifiedMaxDateTime) then 'Y' else 'N' END,
set col C = case when status = 'open' then datediff(d,date,maxdate) end
These queries are getting executed through SSIS execute SQL task and taking 45 min to execute.
The table has a clustered index
Any pointers towards the improvement of performance will help.
- This topic was modified 1 day, 21 hours ago by kirti.