vendredi 27 mars 2015

What is the performance benefit when using a temporary table to update a regular table?


I'm trying to understand what the performance benefit between two different methods of updating multiple table rows.


For the first method we have an explicit update statement for each line to be altered.


i.e.



update my_table set my_field='A' where id=1;
update my_table set my_field='B' where id=2;
update my_table set my_field='C' where id=3;
update my_table set my_field='D' where id=4;
update my_table set my_field='E' where id=5;


For the second method we use a temporary table.


i.e.



create temporary table my_temp_table (id,val);

insert into my_temp_table value (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');

update my_table t, my_temp_table tt set t.val=tt.val where t.id=tt.id;


I've been told that the second method is more efficient and thus superior. I just don't understand why this is so.


Sure we have 5 queries vs 3 queries but that last query is essentially still doing all those updates, albeit implicitly? Plus it adds in a join too.


So I'm just curious as to why using a temporary table is better? Is it simply 3 is less than 5, and as such, is better?





Aucun commentaire:

Enregistrer un commentaire