Tuesday, October 15, 2024
1 change · saas-17.4
Enhancements to existing features
Opening the Inventory Replenishment view now performs far fewer database lookups by grouping and reusing stock rule and orderpoint calculations more efficiently. This should make replenishment planning noticeably faster for companies with many products or reorder rules, reducing wait times for inventory teams.
Original PR description
The aim of this PR is to reduce the number of queries executed when opening the Replenishment View of the Inventory module. The following ideas were implemented ### Usage of `filtered` instead of a…
The aim of this PR is to reduce the number of queries executed when opening the Replenishment View of the Inventory module.
The following ideas were implemented
### Usage of `filtered` instead of a continue inside compute functions.
`filtered` sets the _prefetch_ids to the records that matches the condition. This reduces the size of the expanded recordset whose computed field value needs to be recomputed.
### Batch `orderpoint._quantity_in_progress`
The method `_quantity_in_progress` is designed to be used on a recordset. Calling it once before the for loop in
`stock_orderpoint.py:_compute_qty_to_order_computed` speeds up the whole compute function.
### Introduce a rule cache in `orderpoint._compute_rules`
A product without stock.rules have no impact on the call to `product_id._get_rules_from_locations`. It's only used to search on stock.rule in `procurement.group._search_rule`. We can therefore partition the orderpoints between orderpoints whose products have and don't have route_ids. This saves calls to `product_id._get_rules_from_locations` because now it only depends on (orderpoint.location_id, orderpoint.route_id) which have more hits than (orderpoint.product_id, orderpoint.location_id, orderpoint.route_id).
### Add preliminary checks to `_compute_days_to_order`
In both mrp and purchase_stock, `_compute_days_to_order` does some computations when the rule_ids' action is 'manufacture' and 'buy' respectively. This forces the computation of rule_ids on all orderpoints in self. Adding some precheck and prefiltering on self reduces the size of the recordset that needs the value of rule_ids.
### Introduce `_search_rule_for_warehouses`
Introduce a new method, `_search_rule_for_warehouses`. It's kind of batched version of `_search_rule` that works for multiple
location_dest_ids and warehouse_ids. It is designed to be called by `_get_rule`.
This method uses a _read_group to group the stock.rules by (location_dest_id, warehouse_id, route_id). It then constructs
a dict mappin (location_dest_id, route_id) -> warehouse_id -> stock.rule. This is done because when bool(warehouse_id) in parameter is False, any existing rule matching the location_dest_id, route_id is fine. When bool(warehouse_id) in parameter is True, the candidate stock.rules.warehouse must be equal to the parameter or False.
`_get_rule` is also revamped to find the correct stock.rule among the candidates. It first goes through the location hierarchy, starting from location_id, to build the rule_domain. Then, using the result of `_search_rule_for_warehouses`, it goes through the hierarchy again in the while loop, breaking at the first valid stock.rule found.
`_search_rule` is kept as is in case of overriding.
----
### Benchmark:
Customer saas-17.4 database with 10 000 active orderpoints and 140 active stock.rules.
Requests when opening Replenishment: run + search_panel_select_range + web_search_read
#### Before PR:
Number of queries: 90569 + 90471 + 90497 = 271 352 queries
Timings: 1min30 + 2min40 + 1min30 = 5min40
#### After PR:
Number of queries: 36819 + 36744 + 36767 = 110 330 queries
Timings: 43s + 1min20s + 54s = 2min57
Queries reduction: 250%
Speedup: 192%
NB: Should this PR be merged, it will be backported to 17.0 with some modifications.
---
I confirm I have signed the CLA and read the PR guidelines at www.odoo.com/submit-pr