What query is faster: one update with multiple sets or multiple updates with one set?

user_2442041 0
Viewed 174

Suppose you have TestTable with columns: field1, field2, ... fieldn.

What query executes faster:

UPDATE TestTable set field1 = n1;



UPDATE TestTable set field2 = n2;

...

UPDATE TestTable set fieldn = nn;

or 

UPDATE TestTable set 

field1 = n1,

field2 = n2,

....

fieldn = nn;
postgresql oracle mysql sql
2 Answers

Good question. Try thinking of it this way.

Every set operation takes negligible time
for every UPDATE = n checks

UPDATE n1 = n
UPDATE n2 = n
UPDATE n3 = n

So essentially, for n number of updates, you are checking n rows n times, so you are looking at n^2 total checks. However, if you utilize a SINGLE UPDATE function, you are only checking the n rows ONE time.

Therefore, the second option is significantly better.

user_48765610

SQL is set-based. Any time you can work on a set of data in a single operation, you're usually better off.

user_3412510
Related