Is it faster to run 10,000 queries each updating a single row, or 100 queries each updating 100 rows?
mysql, performance, postgresql, sql-update
Solution
Neither. In PostgreSQL I would instead:
WITH x AS (
SELECT unnest('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40
,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60
,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80
,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100
}'::int[]) AS id
)
UPDATE mytable t
SET myflag = 1
FROM x
WHERE t.id = x.id;
I put so many IDs in my example to give a visual clue that 10000 IDs is a lot. The two ideas presented in the question would either:
have to parse the list and put together 10000 statements and send them to the server, which may very well take longer than the UPDATEs themselves.
have to search in a list (array) of 10000 items for each individual `id` in `mytable` for a matching id. Standard indexes can't be used. This will be very slow. Performance degrades with the size of `mytable`.
An index on `mytable.id` is all the presented alternative needs to outperform both variants by an order of magnitude.
The CTE parses the array once (subquery works, too - MySQL has no CTEs) - and `unnest()` is rather fast with that. Doing it all in one statement beats 10000 statements by an order of magnitude. Add another order of magnitude if those statements are run in individual transactions. Add another one if you should use individual sessions.
Rare exceptions apply for databases with locking issues under heavy write load. Just benchmark as has been advised. `EXPLAIN ANALYZE` is your friend in PostgreSQL.
If the operation grows huge, and most of the table is updated and / or you are running low on disk space or RAM, it may still be a good idea to split the operation into several logical chunks - just not too many, find the sweet spot. Mostly to let HOT updates recycle table bloat from previous `UPDATE` runs. Consider this related question.
Problem
Let's say I have a list of primary keys, for each row one value needs updating. Is it better to run: ``` -- run 10,000 of these queries UPDATE mytable SET myflag = 1 WHERE id = [each_id] ``` Or combine updates into batch queries like this: ``` -- run 100 of these queries, where the IN () list contains about 100 elements UPDATE mytable SET myflag = 1 WHERE id IN (3, 4, 5, 9, 99, ... 7887 ) ``` How about 100 queries with 100 IN () items?