id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35399	"Reduce the ""Case-When"" sequence for a bulk_update when the values for a certain field are the same."	Willem Van Onsem	nobody	"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."	Cleanup/optimization	closed	Database layer (models, ORM)	5.0	Normal	duplicate	db, bulk_update, case, when		Unreviewed	1	0	0	0	0	0