#23646: Add QuerySet.bulk_update() to to efficiently update many models
-------------------------------------+-------------------------------------
Reporter: Brillgen Developers | Owner: Tom
| Forbes
Type: New feature | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tom Forbes):
Thank you for this! I've added a ticket to support this:
https://code.djangoproject.com/ticket/29771#ticket
Replying to [comment:15 Simon Charette]:
> > It requires a custom join on VALUES which I'm really not sure how to
implement. If you have any idea I'd love a point in the right direction.
>
> Hmm I'll have to dig into it a bit more as well but I was hoping it
would be possible without introducing a new type of JOIN by having
`SQLUpdateCompiler` do the heavy lifting.
>
> I suppose having a look at how `QuerySet.extra(tables, where)` is
implemented could guide us towards implementing it at the `UpdateQuery`
level though some form of JOIN.
>
> From my local tests it seems like we could use `UNION ALL` to replace
`VALUES` on MySQL at least.
>
> {{{#!sql
> UPDATE table, (
> SELECT 1 id, 2 first, 3 second
> UNION ALL SELECT 2, 3, 4
> UNION ALL ...
> ) AS table_update
> SET table.first = table_update.first, table.second = table_update.second
> WHERE table.id IN (...) AND table.id = table_update.id
> }}}
>
> I haven't tested performance on SQLite and MySQL but the query seem to
perform significantly faster (3-5x) on PostgreSQL using `VALUES()` instead
of `CASE(WHEN)` when updating two columns on 1k rows of a table with 10k
rows. The performance difference was getting larger as more columns were
updated (I tried 1 to 5).
--
Ticket URL: <https://code.djangoproject.com/ticket/23646#comment:17>
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/066.8b06204d46316ea26828e96d46d673ab%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
