8 ms·
> 6. UPDATE multiple rows with a CASE expression Oh, man, as a seasoned DBA/Data Analyst, don't do this unless you have a really good reason to. This is prema
by da_chicken 9y ago
> 6. UPDATE multiple rows with a CASE expression
Oh, man, as a seasoned DBA/Data Analyst, don't do this unless you have a really good reason to. This is premature optimization of the kind you want to avoid.
Yes, it's really neat to update everything in a single statement, and in some situations it can perform significantly better, but CASE expressions in an UPDATE statement quickly get complicated to map out in your head. It gets extremely difficult extremely quickly to tell where you have an error, and it's very, very easy to make a very costly mistake.
If you really need an atomic change you can just do this:
BEGIN;
UPDATE reward_members SET member_status = 'gold_group' WHERE member_status = 'gold';
UPDATE reward_members SET member_status = 'bronze_group' WHERE member_status = 'bronze';
UPDATE reward_members SET member_status = 'platinum_group' WHERE member_status = 'platinum';
UPDATE reward_members SET member_status = 'silver_group' WHERE member_status = 'silver';
COMMIT;
In general, however, make your queries difficult for the server and easy for you, because you make a ton more mistakes than the server ever will. Let the query planner and optimizer do the work. If performance becomes a problem on this query, you can fix it later when you can focus on just that one issue and understand the specific problem much better.
> You can imagine how many round trips this would take to the server if multiple individual UPDATE statements had been run.
If you're not returning data and you reuse the connection like you're supposed to, "round trips" cost is essentially nothing. What's expensive here is that the database server has to scan the index or row data on member_status. However, if the table is not billions of rows, it can probably fit that index (or even the row data for small tables) in memory and will cache hit on everything.
However, the list of single UPDATE statement can perform much better than a monolithic statement. If you're only updating a portion of the table, or if the number of rows that you'll actually be updating is comparatively small, then the list of single UPDATE statements can perform much better. It all depends on exactly what you're doing with the table.
- sk5t 9y agoYup! The simple UPDATEs could improve the chances of the query planner working optimally--think of cases where a single, complex, setwise update might avoid using some really helpful partial indexes, etc.
- billrobertson42 9y agoAnd would also improve the chances that somebody looking at the sql in 5 or 10 years would quickly understand it as well.
- malisper 9y agoHead's up. Depending on the circumstances, multiple updates are vulnerable to a race condition known as "read skew". If a user's status changes while the multiple update transaction is executing, their status can wind up in a bad state. Here's a demonstration: > CREATE TABLE reward_members (id bigint, member_status text); > INSERT INTO reward_members (values (0, 'silver')); > BEGIN; > UPDATE reward_members SET member_status = 'gold_group' WHERE member_status = 'gold'; > UPDATE reward_members SET member_status = 'bronze_group' WHERE member_status = 'bronze'; -- Concurrently: > UPDATE reward_members SET member_status = 'gold' WHERE id = 0 AND member_status = 'silver'; > UPDATE reward_members SET member_status = 'platinum_group' WHERE member_status = 'platinum'; > UPDATE reward_members SET member_status = 'silver_group' WHERE member_status = 'silver'; > COMMIT; > SELECT * FROM reward_members; id | member_status ----+--------------- 0 | gold If the transactions executed atomically (in the instantaneous sense, not the ACID sense), the member_status of the user should be either silver_group or gold_group depending on the order the transactions executed in. Due to the race condition, the user winds up with the member_status of gold instead. If you want to avoid this you either have to use the case statement version or use a stricter isolation level. Either repeatable read or serializable would prevent this. I've written about read skew, other related race conditions, and how to prevent them in a series of blog posts you can find here[0][1][2]. On a related note, efficiently batching disjoint updates is an interesting problem. One of my coworkers wrote about how he solved this exact problem in Redshift[3]. He found the most efficient way was to create a staging table with what old values corresponded to what new values. Then you can join the staging table with the table you are updating. [0] http://malisper.me/postgres-transactions-arent-fully-isolated/ http://malisper.me/postgres-transactions-arent-fully-isolate... [1] http://malisper.me/postgres-row-level-locks/ http://malisper.me/postgres-row-level-locks/ [2] http://malisper.me/postgres-transaction-isolation-levels/ http://malisper.me/postgres-transaction-isolation-levels/ [3] https://heapanalytics.com/blog/engineering/redshift-pitfalls-avoid https://heapanalytics.com/blog/engineering/redshift-pitfalls...
- leetrout 9y agoThank you very much for taking the time to explain that and including all the links. I've had issues in the past with atomic transactions in Django still appearing to have race conditions and I think this explains why. Really appreciate it.
- dylanz 9y ago“Make your queries difficult for the server and easy for you”... that’s a great quote. We’re a bit more fragile!
- deleted 9y ago[deleted]
- orf 9y agoThis comment is really interesting to me as I'm working on getting essentially this functionality added to Django[1]. The case statement is slightly different, more like this (usually with a couple of hundred ID's): UPDATE TABLE place SET rating = ( CASE WHEN id = 1 THEN 25 CASE WHEN id = 2 THEN 94 CASE WHEN id = 3 THEN 16 ) WHERE id IN (1, 2, 3) Members of the Django community, myself included, have seem huge performance benefits using this technique. I had an update loop go from taking 90 seconds to 3. As a seasoned DBA you see any huge downsides with this technique? I agree that if you're updating the whole table using a CASE statement is not a great idea but if you want to update 1000 rows with differing values and you have to issue 1000 individual queries then this can take some time (including whatever overhead Django has). 1. https://github.com/django/django/pull/9606 https://github.com/django/django/pull/9606
- pritambaral 9y ago> (usually with a couple of hundred ID's) > I had an update loop go from taking 90 seconds to 3. How is the performance with prepared statements? It seems like repeatedly executing the same statement with different values is what prepared statements are made for.
- Can_Not 9y agoThe ORM likely does use prepared statements, it's that most ORMs by default makes 100 round-trip IO operations (one for each update). The code would need to be rewritten without the ORM to send all the updates as one database call. The query isn't expensive to the database, but to the application itself just the IO of the database call adds up quickly.
- orf 9y agoUnfortunately Django does not use prepared statements. There are some big challenges to work around before support can be added. In any case I'd be very, very surprised if a significant portion of the 87 seconds using a single statement saved was saved also saved by using a prepared statement.