Adding update SQL queries

Question

I have a script that updates itself every week. I've got a warning from my hosting that I've been overloading the server with the script. The problem, I've gathered is that I use too many UPDATE queries (one for each of my 8000+ users).

It's bad coding, I know. So now I need to lump all the data into one SQL query and update it all at once. I hope that is what will fix my problem.

A quick question. If I add purely add UPDATE queries separated by a semicolon like this:

UPDATE table SET something=3 WHERE id=8; UPDATE table SET something=6 WHERE id=9;

And then update the database with one large SQL code as opposed to querying the database for each update, it will be faster right?

Is this the best way to "bunch" together UPDATE statements? Would this significantly reduce server load?

Answer 1

A curiosity of SQL is that the following integer expression (1 -abs(sign(A - B))) = 1 if A == B and 0 otherwise. For convenience lets call this expression _eq(A,B). So

update table set something = 3*_eq(id,8) + 6* _eq(id,9) where id in (8,9);

will do what you want with a single update statement.

Answer 2

Make a delimited file with your values and use your equivalent of MySQL's LOAD DATA INFILE. This will be significantly faster than an UPDATE.

LOAD DATA INFILE '/path/to/myfile'
REPLACE INTO TABLE thetable(field1,field2, field3)
//optional field and line delimiters
;

Answer 3

Your best bet is to batch these statements by your "something" field:

UPDATE table SET something=3 WHERE id IN (2,4,6,8)
UPDATE table SET something=4 WHERE id IN (1,3,5,7)

Of course, knowing nothing about your requirements, there is likely a better solution out there...

Answer 4

It will improve IO since there is only one round trip, but the database "effort" will be the same.