Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
performance on update using subquery versus correlated update
--Sql Server 2014 Express Edition --Batches are separated by 'go' select @@version as 'sql server version'; -- Functional Task: -- align all classes to value of class "A" -- Challenge -- find most efficient code! -- In example the correlated update points to the same table, where source values reside. -- This is not the point of a "correlated update" -- Title -- performance on update using subquery versus correlated update -- demo table create table correlatedUpdateShow(cus_id bigint, cus_class char(1), cus_value varchar(10)); -- demo values insert into correlatedUpdateShow values (1,'A', 'Text1'), (1,'B', 'abc'), (1,'C', 'xyz'), (2,'A', 'Text2'), (2,'B', NULL), (2,'C', NULL); -- check the data select * from correlatedUpdateShow order by 1,2,3; go -- Variation I -- often used, but perhaps not the best solution -- update using subquery for new value /* -- uncomment to run this variation UPDATE t0 SET t0.cus_value = (SELECT t1.cus_value -- source of the new value determined within each record update FROM correlatedUpdateShow t1 -- so each time reaching next record, new value has to be evaluated WHERE t1.cus_class = 'A' -- which obviously means, repeat the subquery for every record in table AND t1.cus_id = t0.cus_id) -- meeting the where condition FROM correlatedUpdateShow t0 WHERE t0.cus_class <> 'A'; select * from correlatedUpdateShow; */ -- Variation II -- correlated update should be much faster -- the bigger the amount of data to update, the easier you'll notice the difference UPDATE t0 SET t0.cus_value = t1.cus_value FROM correlatedUpdateShow t0 JOIN (SELECT t1.cus_value, t1.cus_id -- source of the new value prepared in one batch and joined in whole FROM correlatedUpdateShow t1 -- so t1.cus_value comes already prepared along with each row to update WHERE t1.cus_class = 'A') t1 -- this way the update is done with only one update run and one(!) select run ON t0.cus_id = t1.cus_id WHERE t0.cus_class <> 'A'; -- BTW: Why mention this where clause? Without the condition even original value gets updated with "A" -- BTW 2: If You know there will be a reasonable amount of values with the new value, -- add another condition to avoid updating same to this same value -- result select * from correlatedUpdateShow order by 1,2,3; go -- clean up drop table correlatedUpdateShow; -- one day, I will try to understand the meaning of this "go" thing .. go -- Just to get a little more specific reagarding performance impact: -- Talking about example above with no indexes at all, it get's worse and worse, -- the more records are affected. In fact, it will get slower in a non linear manner -- have a look to the corresponding postgres example to see some numbers on performance with larger data amounts: -- https://rextester.com/SFP98118
run
|
edit
|
history
|
help
0
Except Insert missing
FIGURA5.4
Common Table Expression
Create Tables_2
Branch
SqlServer1282018
Pivot and Unpivot
Sql flight 1
t
tr3