Am trying to optimize a procedure. There are 3 different update queries present in the procedure.
update #ResultSet
set MajorSector = case
when charindex(' ', Sector) > 2 then rtrim(ltrim(substring(Sector, 0, charindex(' ', Sector))))
else ltrim(rtrim(sector))
end
update #ResultSet
set MajorSector = substring(MajorSector, 5, len(MajorSector)-4)
where left(MajorSector,4) in ('(00)','(01)','(02)','(03)','(04)','(05)','(06)','(07)','(08)','(09)')
update #ResultSet
set MajorSector = substring(MajorSector, 4, len(MajorSector)-3)
where left(MajorSector,3) in ('(A)','(B)','(C)','(D)','(E)','(F)','(G)','(H)','(I)','(J)','(K)','(L)','(M)','(N)','(O)','(P)','(Q)','(R)','(S)','(T)','(U)','(V)','(W)','(X)','(Y)','(Z)')
To complete all three update queries it takes less than 10 seconds.
Execution plan for all three update queries.
https://www.brentozar.com/pastetheplan/?id=r11BLfq7b
What I planned is to change the three different update queries into one single update query, so that the I/O can be reduced.
;WITH ResultSet
AS (SELECT CASE
WHEN LEFT(temp_MajorSector, 4) IN ( '(00)', '(01)', '(02)', '(03)', '(04)', '(05)', '(06)', '(07)', '(08)', '(09)' )
THEN Substring(temp_MajorSector, 5, Len(temp_MajorSector) - 4)
WHEN LEFT(temp_MajorSector, 3) IN ( '(A)', '(B)', '(C)', '(D)','(E)', '(F)', '(G)', '(H)','(I)', '(J)', '(K)', '(L)','(M)', '(N)', '(O)', '(P)','(Q)', '(R)', '(S)', '(T)','(U)', '(V)', '(W)', '(X)','(Y)', '(Z)' )
THEN Substring(temp_MajorSector, 4, Len(temp_MajorSector) - 3)
ELSE temp_MajorSector
END AS temp_MajorSector,
MajorSector
FROM (SELECT temp_MajorSector = CASE
WHEN Charindex(' ', Sector) > 2 THEN Rtrim(Ltrim(Substring(Sector, 0, Charindex(' ', Sector))))
ELSE Ltrim(Rtrim(sector))
END,
MajorSector
FROM #ResultSet)a)
UPDATE ResultSet
SET MajorSector = temp_MajorSector
But this takes around 1 minute to complete. I checked the execution plan, it is identical as first update query.
Execution plan for above query:
https://www.brentozar.com/pastetheplan/?id=SJvttz9QW
Can somebody explain why it is slow?
Dummy data for testing:
If object_id('tempdb..#ResultSet') is not null
drop table #ResultSet
;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
SELECT CONVERT(varchar(255), NEWID()) as Sector,cast('' as varchar(1000)) as MajorSector
into #ResultSet
FROM Tally
where n <= 242906 -- my original table record count
ORDER BY n;
Note : Since this is not my original data the timings I have mentioned above could be little different. Still the single update query is much slower than the first three.
I tried executing the queries more than 10 times to make sure external factors should not affect the performance. All 10 times first three updates ran much faster than the last single update.