PerformanceHighlight of Weekly #002

Faster inventory valuation at a past date

Stock moves are now batched across all products instead of per product, which cuts the number of queries for at-date valuation.

Versions20.0
ModulesWMS Accounting stock_account
AppInventory & Manufacturing

Pull requests and commits

BranchPull requestCommit
20.0odoo/odoo#290561 ↗872f3824b3 ↗

Commit message

[PERF] stock_account: Perf improvements for at date inventory valuation

*Batch per all moves, not per product*

**Problem:**
In #250526, moves are batched in order to prevent memory error in databases
with many stock.move. However, the batching is done on the moves per product,
meaning batches can be very small relative to the limit, and this causes
unecessary queries compared to batching per all moves to process.

**Solution:**
Iterate through all moves rather than per product, and use/save the per product
results directly in the relevant dicts.

---

*Batch and prefetch for initial product std_price*

**Problem:**
When initializing products' standard price before replaying valuation, the
first stock.move is read. This happens before any prefetching or batching
occurs, so a query is made per move and contributes to performance issues.

**Solution:**
Batch and prefetch the products' first moves, then initialize the standard
price.

---

*Use cached is_in and is_out values*

**Problem:**
In `_get_valued_qty()`, `_is_in()` and `_is_out()` are called for each move,
but these methods are already called for these moves and cached as `is_in` and
`is_out`.

**Solution:**
Replace the method calls with the cached fields. If the move is not done, we
fallback to the methods as the cached fields will be false for not done moves.

---

**Perf Tables:**
Record: product.template, each with AVCO automated valuation and a done in move

Today:
|Record count|Time before|Queries before|Time after|Queries after|
|------------|-----------|--------------|----------|-------------|
|1k          |1.08s      |264           |697ms     |235          |
|5k          |2.74s      |864           |2.23s     |864          |
|10k         |4.83s      |1375          |4.56s     |1870         |

At Date:
|Record count|Time before|Queries before|Time after|Queries after|
|------------|-----------|--------------|----------|-------------|
|1k          |3.10s      |6109          |925ms     |386          |
|5k          |14.68s     |30299         |3.50s     |1564         |
|10k         |26.98s     |59173         |7.16s     |3590         |

opw-6134244

My stack

Pick the versions and apps you run. Weekly digests, this week's table and All changes then show only what concerns you. Saved in this browser.

Versions
Apps
Modules