Odoo Changelog — 2024-06-17

Monday, June 17, 2024

1 change · 17.0

Improvements(1)

Enhancements to existing features

[FW][PERF] account_accountant: add CTE to invoice amls candidates query

Enterprise17.0account_accountantMedium

This update improves the performance of the invoice reconciliation feature by optimizing how the system searches for matching invoices. The change reduces query execution time by 70% (from 7.5 seconds to 2.3 seconds) on large databases, making the reconciliation process significantly faster and more responsive for users working with many transactions.

Original PR description

When triggering the onchange method of the reconciliation widget, the `_get_invoice_matching_amls_candidates` method is executed. If there are both numerical and exact tokens, it produces a query…

When triggering the onchange method of the reconciliation widget, the `_get_invoice_matching_amls_candidates` method is executed. If there are both numerical and exact tokens, it produces a query with 6 subqueries (a general query with 6 UNION ALL). This can become quite slow on database with lots of amls.

Because for each of these 6 "subqueries" the FROM, JOIN and WHERE clauses are the same, we can extract all of them in a single CTE. Thanks to that the CTE is executed only once and each UNION ALL simply scans the CTE to get the correct columns + performs an additional filtering. This reduces the average execution of the query by a factor of 3-4 on hot-cache. Since the onchange is triggered each time we do something in the reconciliation widget, on average the cache is expected to be hot so the average speedup boils down to the hot-cache scenario.

#### speedup

Customer 17.0 database with 7M account_move_lines and 8000 accounts, the query execution, given a st_line and with both numerical and exact tokens: 7.5s -> 2.3s

##### dalibo

[Standard](https://explain.dalibo.com/plan/0a6fe16h503614e0) vs [CTE](https://explain.dalibo.com/plan/495a777ef95d2980)

There is also a speedup achievable with a partial multicolumn index but that's more of a case by case index.

opw-3938995

Forward-Port-Of: odoo/enterprise#64433

#performance#reconciliation#database optimization#user experience

When triggering the onchange method of the reconciliation widget, the `_get_invoice_matching_amls_candidates` method is executed. If there are both numerical and exact tokens, it produces a query with 6 subqueries (a general query with 6 UNION ALL). This can become quite slow on database with lots of amls. Because for each of these 6 "subqueries" the FROM, JOIN and WHERE clauses are the same, we can extract all of them in a single CTE. Thanks to that the CTE is executed only once and each UNION ALL simply scans the CTE to get the correct columns + performs an additional filtering. This reduces the average execution of the query by a factor of 3-4 on hot-cache. Since the onchange is triggered each time we do something in the reconciliation widget, on average the cache is expected to be hot so the average speedup boils down to the hot-cache scenario. #### speedup Customer 17.0 database with 7M account_move_lines and 8000 accounts, the query execution, given a st_line and with both numerical and exact tokens: 7.5s -> 2.3s ##### dalibo [Standard](https://explain.dalibo.com/plan/0a6fe16h503614e0) vs [CTE](https://explain.dalibo.com/plan/495a777ef95d2980) There is also a speedup achievable with a partial multicolumn index but that's more of a case by case index. opw-3938995 Forward-Port-Of: odoo/enterprise#64433