Opened 4 months ago

Last modified 2 months ago

#29771 assigned Cleanup/optimization

Support database-specific syntax for bulk_update

Reported by: Tom Forbes Owned by: Tom Forbes
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Tom Forbes, Brillgen Developers, Srinivas Reddy Thatiparthy 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 (5)

comment:1 Changed 4 months ago by Simon Charette

Triage Stage: UnreviewedAccepted

comment:2 Changed 4 months ago by Tom Forbes

Owner: changed from nobody to Tom Forbes
Status: newassigned

comment:3 Changed 4 months ago by Tom Forbes

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 Changed 4 months ago by Brillgen Developers

Cc: Brillgen Developers added

comment:5 Changed 2 months ago by Srinivas Reddy Thatiparthy

Cc: Srinivas Reddy Thatiparthy added
Note: See TracTickets for help on using tickets.
Back to Top