Opened 6 years ago

Last modified 13 months ago

#29771 assigned Cleanup/optimization

Support database-specific syntax for bulk_update

Reported by: Tom Forbes Owned by: Akash Kumar Sen
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Tom Forbes, Brillgen Developers, Srinivas Reddy Thatiparthy, Adam Johnson, Shai Berger, Akash Kumar Sen, John Speno Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

As Simon points out here (https://code.djangoproject.com/ticket/23646#comment:15) specialized syntax can massively speed up bulk_update().

Postgres supports using UPDATE ... FROM (​https://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql) and we can possibly emulate this with MySQL and UNION ...'s.

Change History (11)

comment:1 by Simon Charette, 6 years ago

Triage Stage: Unreviewed → Accepted

comment:2 by Tom Forbes, 6 years ago

Owner: changed from nobody to Tom Forbes
Status: new → assigned

comment:3 by Tom Forbes, 6 years ago

Cc: Tom Forbes added

I think the groundwork to this is quite difficult to lay. The current SQLUpdateCompiler is not suitable for this, I think.

Also I believe we can mimic this on SQLLite:

WITH test(id) AS (SELECT 1 UNION ALL SELECT 2)
UPDATE demo
SET name=(select id from test where id=demo.id)
WHERE id IN (select id from test)

Whether or not this is faster is something I'm not sure of. I believe the MySQL syntax will be faster, and there is no SQL standard way of doing this (I believe?), so we are left with a number of pretty different workarounds. I'm not sure how to structure this nicely.

I can maybe hack Postgres support into the SQLUpdateCompiler, but it's really not nice. We have to create all the aliases ourselves, manage the different column names, etc etc.

comment:4 by Brillgen Developers, 6 years ago

Cc: Brillgen Developers added

comment:5 by Srinivas Reddy Thatiparthy, 6 years ago

Cc: Srinivas Reddy Thatiparthy added

comment:6 by Adam Johnson, 5 years ago

Cc: Adam Johnson added

comment:7 by Shai Berger, 5 years ago

Cc: Shai Berger added

comment:8 by Mariusz Felisiak, 21 months ago

Owner: Tom Forbes removed
Status: assigned → new

comment:9 by Akash Kumar Sen, 15 months ago

Cc: Akash Kumar Sen added

comment:10 by Akash Kumar Sen, 14 months ago

Owner: set to Akash Kumar Sen
Status: new → assigned

comment:11 by John Speno, 13 months ago

Cc: John Speno added
Note: See TracTickets for help on using tickets.
Back to Top