Thursday, November 23, 2023
1 change · 17.0
Enhancements to existing features
This update significantly speeds up the Budget Analysis report, which was running very slowly for companies with large amounts of budget data. The fix reduces loading time from 40-70 seconds down to 3-4 seconds by optimizing how the system processes budget calculations, making the report practical to use even with thousands of budget lines.
Original PR description
## Description The pivot view of the budget analysis can be really slow for large database with lots of `crossovered.budget.lines`. This issue is also present if one exports the view to the Dashboard…
## Description The pivot view of the budget analysis can be really slow for large database with lots of `crossovered.budget.lines`. This issue is also present if one exports the view to the Dashboard app. ## Analysis The pivot view will request multiple `read_group`, computing the sum of `practical_amount`, triggering it's compute. Said compute loops over all lines, making a separate SQL query for each. In the context of having a large amount lines, the inherent cost of running a query (serialization/deserialization, network transfer) can accumulate to some non-negligible amount. ## Fix Group by the lines on their date-range and account_id. In the case of line that has an `account.analytic.line` associated with it, we also make sure that if a `general_budget_id` is present on the `crossovered.budget.lines`, then the accounts of the budget should match those of the analytic line. ## Benchmark Database on 16.0, 17k `crossovered.budget.lines`, 18k `account.analytic.line`, 31k `account.move.line`. Loading the Budget Analysis pivot view (lines have an analytic account on them) | | Before | After | |-------------|----------|----------| | Timing | 40 secs | 3.1 secs | | Query count | 39k | 150 | Loading the Budget Analysis pivot view (lines have *no* analytic account on them) | | Before | After | |-------------|---------|--------| | Timing | 70 secs | 4 secs | | Query count | 35k | 130 | ## Reference opw-3535910 opw-3573566 Forward-Port-Of: odoo/enterprise#51282 Forward-Port-Of: odoo/enterprise#49871