SQL Multiple Updates vs single Update performance

Question

Assume I have 10,000 rows that need to be updated. What would be faster

UPDATE DB.Servers SET Live = 1 where name = 'server1';
UPDATE DB.Servers SET Live = 1 where name = 'server2';
...
UPDATE DB.Servers SET Live = 1 where name = 'server100000';

OR

UPDATE DB.Servers SET Live = 1 where name in ('server1', 'server2'...'server10000');

I would assume the second option is faster, but I'm not sure. What worries me is that I don't know if there is a length limit for an SQL stm. What would be recommended in this type of situation?

Thank you

Answer 1

The single UPDATE is faster.

I have tested with MySQL 5.1.73

CREATE TABLE test_random (
  val char(40) NOT NULL default '',
  num int NOT NULL default '0',
  KEY val (val)
) TYPE=MyISAM;

INSERT INTO test_random (val, num) VALUES
('MXZJBXUGNFOZMMQMYZEMLKZZKTCIGEU',889),
('ZTEBMDHOJGYBYEOPZIIPPJQQOKXMTKU',351),
... [200K records total inserted] ...
('ADLDYZQHDEZMYBHORKGJYMIOVUETQCM',786);

Then here is random-update-single.sql:

UPDATE test_random SET num=1 WHERE val IN (
'PXTUKCZMRFZDTWUPULAPENPNQCSPFQJ',
'GDIMLSCDRSNCMUNUZLQIDFZSEELNZLR',
... [100K records] ...
'ADLDYZQHDEZMYBHORKGJYMIOVUETQCM');

And here is random-update-multiple.sql:

UPDATE test_random SET num=2 WHERE val='PXTUKCZMRFZDTWUPULAPENPNQCSPFQJ';
UPDATE test_random SET num=2 WHERE val='GDIMLSCDRSNCMUNUZLQIDFZSEELNZLR';
... [100K records] ...
UPDATE test_random SET num=2 WHERE val='ADLDYZQHDEZMYBHORKGJYMIOVUETQCM';

Here is the result:

> time mysql -uroot test < random-update-single.sql
0.075u 0.009s 0:01.78 3.9%      0+0k 0+0io 0pf+0w
> time mysql -uroot test < random-update-single.sql
0.074u 0.009s 0:01.76 3.9%      0+0k 0+0io 0pf+0w
> time mysql -uroot test < random-update-single.sql
0.069u 0.013s 0:01.57 4.4%      0+0k 0+0io 0pf+0w

---

> time mysql -uroot test < random-update-multiple.sql
1.746u 1.515s 0:11.14 29.1%     0+0k 0+0io 0pf+0w
> time mysql -uroot test < random-update-multiple.sql
2.183u 2.150s 0:14.83 29.1%     0+0k 0+0io 0pf+0w
> time mysql -uroot test < random-update-multiple.sql
1.961u 1.949s 0:13.96 27.9%     0+0k 0+0io 0pf+0w

That is, multiple UPDATE turned out to be 5-6 times slower than single UPDATE.

Answer 2

SQL is supposed to be a declarative language; it does not expect from the user to say "how" to get the result, only "what" the desired result is. So in principal I would use the in() construct, as this is the most concise (from a logical viewpoint) way to ask for the results, and let the DBMS (any DBMS!) decide what's best.