performance on update using subquery versus correlated update , Sql Server

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;

-- 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;

-- 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

λ

                                      .NET NoSQL database for rapid development