faster cf updates

[jerch]

Playground to eval speed differences between certain update tricks.

UPDATE FROM gives a rather big speedup in postgres and sqlite >= 3.33 (testcase is to update 10000 records of SelfRef):

# sqlite with bulk_update
>>> timer(lambda : update_dependent(SelfRef.objects.all()))
18.874271631240845
# sqlite with UPDATE FROM
>>> timer(lambda : update_dependent(SelfRef.objects.all()))
3.453566312789917

# postgres with bulk_update
>>> timer(lambda : update_dependent(SelfRef.objects.all()))
17.17230486869812
# postgres with UPDATE FROM
>>> timer(lambda : update_dependent(SelfRef.objects.all()))
1.7931149005889893

Edit: With mariadb >= 10.3 UPDATE FROM can be simulated without creating a temp table like this:

UPDATE exampleapp_selfref
INNER JOIN (SELECT * FROM (VALUES (0, 1), (1,'yay!')) AS foo) AS data
ON exampleapp_selfref.id = data.0
SET exampleapp_selfref.name = data.1;

Update - runtime for mysql:

# with bulk_update
>>> timer(lambda : update_dependent(SelfRef.objects.all()))
15.962127923965454
# with UPDATE FROM workaround
>>> timer(lambda : update_dependent(SelfRef.objects.all()))
2.10331130027771

[jerch]

Wow, this optimized update query scales much better with a bigger batchsize than bulk_update. Numbers for COMPUTEDFIELDS_BATCHSIZE = 1000 on 10000 records:

# sqlite - big benefit
>>> timer(lambda : update_dependent(SelfRef.objects.all()))
0.6897451877593994
# postgres - small benefit
>>> timer(lambda : update_dependent(SelfRef.objects.all()))
1.6358757019042969
# mariadb - big benefit
>>> timer(lambda : update_dependent(SelfRef.objects.all()))
0.7499890327453613

Edit: postgres actually performs better with a single big statement than with using execute_values. Here the corrected numbers with the same query construction logic as done for the others for batchsize 1000:

# postgres - now a big benefit too
>>> timer(lambda : update_dependent(SelfRef.objects.all()))
0.6264104843139648

(Phew - postgres is ahead again :smile_cat:)

[jerch]

To summarize things for our particular perf test case:

• UPDATE FROM VALUES gives a speedup of ~25x compared to bulk_update, leading to times of <1s for 10000 records of SelfRef. Compared to the old loop-saving, this is ~100 times faster.
• Querying and cf calculation is at 0.15 - 0.20 s for all db backends, thus updating the values in the db still dominates runtime. But querying the values for calculation is rather cheap on SelfRef (all model local fields), which will change significantly with more complicated cf dependencies.

UPDATE FROM VALUES seems to be a neat trick to get multiple columns and rows updated very fast in one go without the need to resort to a temp table in between. There is still a chance, that for very big updates (>100k records at once) a temp table might perform better, but the complexity of this seem not worth it.

Currently the main issue with this optimization is the support in db backends:

• sqlite must be >= 3.33
• mariadb & mysql have seen many changes around VALUES() recently, which is really cumbersome to test and maintain. Maybe a django manangement command can help to check the db compatibility.

[coveralls]

Pull Request Test Coverage Report for Build 1751598454

• 132 of 150 (88.0%) changed or added relevant lines in 2 files are covered.
• No unchanged relevant lines lost coverage.
• Overall coverage decreased (-1.2%) to 95.002%

---

Changes Missing Coverage	Covered Lines	Changed/Added Lines	%
computedfields/fast_update.py	118	136	86.76%
<!--	Total:	132	150	88.0%	-->

Totals
Change from base Build 1715609233:	-1.2%
Covered Lines:	1338
Relevant Lines:	1389

---

💛 - Coveralls

[jerch]

Left to do:

• no clue yet how to test sqlite 3.33+ in gh actions (would need a python build against newer sqlite version than in ubuntu:latest)
• docs section about fast_update