#35399: Reduce the "Case-When" sequence for a bulk_update when the values for a
certain field are the same.
               Reporter:  Willem     |          Owner:  nobody
  Van Onsem                          |
                   Type:  New        |         Status:  new
  feature                            |
              Component:  Database   |        Version:  5.0
  layer (models, ORM)                |       Keywords:  db, bulk_update,
               Severity:  Normal     |  case, when
           Triage Stage:             |      Has patch:  1
  Unreviewed                         |
    Needs documentation:  0          |    Needs tests:  0
Patch needs improvement:  0          |  Easy pickings:  0
                  UI/UX:  0          |
 Django's `.bulk_update(..)` seems to work with sequences of `Case-When`
 *per* field and *per* record to update. In other words, if we have three
 items we want to update with `pk` being 1, 4 and 5, and we have a field
 `yn` and the records for pk `1` and `5` have value `y`, whereas the one
 for `pk=4` it is `n`, then this will make a query that looks like:

 UPDATE my_table
 SET yn=CASE WHEN id=1 THEN 'y' WHEN id=4 THEN 'n' WHEN id=5 THEN 'y' END
 WHERE id IN (1,4,5)

 It thus builds a long sequence of `Case`-`When`s, which is not efficient.

 There are for most databases solutions with a temporary table that is then
 upserted into the main table. The Django ORM could probably use existent
 tools for this where we first make a temporary model, create it with the
 migration model, insert in bulk, then upsert with a query, and finally
 remove the temporary table. But a problem with this is, we might not have
 privileges to create an extra table in that database.

 But a low-hanging optimization is to first *group* the values together
 that we can group together. This can be done with a `defaultdict` that
 maps the value to a list of primary keys for that value. In case the value
 is not hashable, we can fallback on the original case-when logic. But for
 values like strings, this would reduce the query to:

 UPDATE my_table
 SET yn=CASE WHEN id IN (1, 5) THEN 'y' WHEN id=4 THEN 'n' END
 WHERE id IN (1,4,5)

 That being said, I think bulk_updates should still be done in a more
 efficient manner, perhaps moving it to the engines, since a lot of
 databases allow the creation of temporary tables to make upserts a lot
 more efficient.
Ticket URL: <https://code.djangoproject.com/ticket/35399>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 

Reply via email to