0046437: Aging report have low performance when is executed in an environment with a huge amount of information

Notes
(0127736) vmromanos (manager) 2021-05-03 12:33	Please provide the explain plan to properly understand the problem.

(0127753) salvador_campanella (developer) 2021-05-03 17:46	Sure, Let me share some plans with you: 1.- Aging Credit - Many loops due joins https://explain.dalibo.com/plan/zvR [^] 2.-Aging Credit Native: https://explain.dalibo.com/plan/QFY [^] 3.-Aging Credit Custom: https://explain.dalibo.com/plan/ETb [^]

(0128009) vmromanos (manager) 2021-05-11 18:47	Test plan: Login as Openbravo admin Create a new business partner: Org: Región Norte Name and searchkey: credit Customer tab: Price list: Tarifa de ventas Payment method: Transferencia Payment terms: Immmediate Financial account: Cuenta de Banco Invoice Terms: Immediate Location/Address: Enter any address Go to Payment In and create a new record. Received from: credit Amount: 10 Press Add details and select "Leave the credit to be used later" in the Overpayment Action selector. Press Done Launch the Receivables Aging Schedule report for "Región Norte" organization. Leave the default filters. Ensure the "credit" business partner has 10€ of credit, and the balance is -10€ Go to sales invoice window and create a new record for the "credit" business partner. Enter 10 units of Agua sin Gas. Complete the invoice. In the consume credit popup, select the previous 10€ and "Use the Credit". Launch the Receivables Aging Schedule report for "Región Norte" organization. Leave the default filters. Ensure the "credit" business partner has 0€ of credit, because we have already consumed it. Go to sales order window and create a new record for the "credit" business partner. Enter 10 units of Agua sin Gas. Book the order. Press Add Payment button, and enter 10€. Press Done Launch the Receivables Aging Schedule report for "Región Norte" organization. Leave the default filters. Ensure the "credit" business partner has 10€ of credit, and the balance is -1.49€ Go to sales invoice window and create a new record for the "credit" business partner. Press Create Lines From Order and select the previous sales order. Press Done. Complete the invoice. Launch the Receivables Aging Schedule report for "Región Norte" organization. Leave the default filters. Ensure the "credit" business partner has 0€ of credit, because we have already consumed it. Now go to payment in and search for the payment that consumed the first credit payment (it ends with Z). Unpost & and reactivate it. Verify the payment is in Awaiting Payment status. Launch the Receivables Aging Schedule report for "Región Norte" organization. Leave the default filters. Ensure the "credit" business partner has 10€ of credit. Now, for all the payments associated to "credit" business partner: Go to the associated transaction, unpost it, reactivate it and delete it. Go to the payment and reactivate it. Launch the Receivables Aging Schedule report for "Región Norte" organization. Leave the default filters. Ensure the "credit" business partner has 0€ of credit.

(0128020) vmromanos (manager) 2021-05-12 09:35	Performance analysis: * Before the fix (3m 50s): https://explain.dalibo.com/plan/E1A [^] * After the fix (46s): https://explain.dalibo.com/plan/MSn [^]

(0128032) hgbot (developer) 2021-05-12 11:14	Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/359 [^]

(0128396) hgbot (developer) 2021-05-19 13:06	Directly closing issue as related merge request is already approved. Repository: https://gitlab.com/openbravo/product/openbravo [^] Changeset: c0c2ef68ca7449ac41d1ba4b67f50f0d0bce7593 Author: Víctor Martínez Romanos <victor.martinez@openbravo.com> Date: 2021-05-19T11:06:05+00:00 URL: https://gitlab.com/openbravo/product/openbravo/-/commit/c0c2ef68ca7449ac41d1ba4b67f50f0d0bce7593 [^] Fixed ISSUE-46437: Aging balanced selectCredit improved performance The selectCredit query does the following: GENERATED_CREDIT - CONSUMED_CREDIT FROM CREDIT PAYMENTS IN A PAID STATUS BEFORE DATE UNION PREPAYMENTS IN A PAID STATUS BEFORE DATE LEFT JOIN CONSUMED CREDIT BEFORE DATE The fix consists on adding several indexes and to slightly change the SQL query. The following indexes have been added: 1. FIN_PAYMENT.FIN_PAYMENT_GENERATEDCREDIT: partial index to get the payments that generate credit. 2. FIN_PAYMENT_SCHEDULEDETAIL.FIN_PYMT_SCHDET_DET_ORNOIN: fpsd linked to an order and not to an invoice (aka prepayments). We include the payment detail and amount so the index has everything needed by the query. 3. FIN_PAYMENT_CREDIT.FIN_PAYMENTCREDIT_USED_IDX: to quickly link to the FIN_PAYMENT_ID_USED. The SQL query includes the following changes: 1. trunc the date parameter instead of the date's column, so any index over the date's column could be used by the planner. 2. The LEFT JOIN to get the CONSUMED CREDIT has been refactorized: 2.1 Usage of LATERAL to avoid a nested loop over the consumed credit data. The loop is executed only for pure credit payments (not for prepayments as it doesn't make sense in this case) 2.2 Remove exists that searched for: (fpsd1.isinvoicepaid = 'Y' AND fpsd1.fin_payment_schedule_invoice IS NOT NULL) OR ( fp1.status IN ('1') AND fpsd1.fin_payment_schedule_invoice IS NULL) Instead we considered that any payment with paid status before the date and linked to a fin_payment_credit is actually consumed. 3. Usage of UNION ALL as it is faster and totally safe here. --- M src-db/database/model/tables/FIN_PAYMENT.xml M src-db/database/model/tables/FIN_PAYMENT_CREDIT.xml M src-db/database/model/tables/FIN_PAYMENT_SCHEDULEDETAIL.xml M src/org/openbravo/erpCommon/ad_reports/AgingDao_data.xsql ---

(0128397) hgbot (developer) 2021-05-19 13:06	Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/359 [^]

Issue History
Date Modified	Username	Field	Change
2021-04-30 12:24	salvador_campanella	New Issue
2021-04-30 12:24	salvador_campanella	Assigned To	=> Triage Finance
2021-04-30 12:24	salvador_campanella	Modules	=> Core
2021-04-30 12:24	salvador_campanella	Resolution time	=> 1621548000
2021-04-30 12:24	salvador_campanella	Triggers an Emergency Pack	=> No
2021-04-30 15:59	ahernandezgil	Issue Monitored: ahernandezgil
2021-05-03 12:33	vmromanos	Note Added: 0127736
2021-05-03 12:33	vmromanos	Assigned To	Triage Finance => salvador_campanella
2021-05-03 12:33	vmromanos	Status	new => feedback
2021-05-03 17:46	salvador_campanella	Note Added: 0127753
2021-05-03 17:46	salvador_campanella	Note View State: public: 127753
2021-05-04 12:15	vmromanos	Assigned To	salvador_campanella => Triage Finance
2021-05-04 12:15	vmromanos	Status	feedback => new
2021-05-07 12:13	vmromanos	Status	new => scheduled
2021-05-07 12:13	vmromanos	Assigned To	Triage Finance => vmromanos
2021-05-11 18:47	vmromanos	Note Added: 0128009
2021-05-12 09:35	vmromanos	Note Added: 0128020
2021-05-12 11:14	hgbot	Note Added: 0128032
2021-05-19 13:06	hgbot	Resolution	open => fixed
2021-05-19 13:06	hgbot	Status	scheduled => closed
2021-05-19 13:06	hgbot	Fixed in Version	=> PR21Q3
2021-05-19 13:06	hgbot	Note Added: 0128396
2021-05-19 13:06	hgbot	Note Added: 0128397

Relationships [ Relation Graph ] [ Dependency Graph ]