Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0046437Openbravo ERP09. Financial managementpublic2021-04-30 12:242021-05-19 13:06
Reportersalvador_campanella 
Assigned Tovmromanos 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionPR21Q3 
Merge Request Statusapproved
Review Assigned To
OBNetwork customerGold
Web browser
ModulesCore
Support ticket22615
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0046437: Aging report have low performance when is executed in an environment with a huge amount of information
DescriptionAging report have low performance when is executed in an environment with a huge amount of information

The query related with SqlMethod name="selectCredit" is having a low performance
https://gitlab.com/openbravo/devel/openbravo/-/blob/master/src/org/openbravo/erpCommon/ad_reports/AgingDao_data.xsql [^]

Steps To ReproduceExecute the aging report in an environment with a huge amount of information
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2021-04-30 12:24salvador_campanellaNew Issue
2021-04-30 12:24salvador_campanellaAssigned To => Triage Finance
2021-04-30 12:24salvador_campanellaOBNetwork customer => Gold
2021-04-30 12:24salvador_campanellaModules => Core
2021-04-30 12:24salvador_campanellaSupport ticket => 22615
2021-04-30 12:24salvador_campanellaResolution time => 1621548000
2021-04-30 12:24salvador_campanellaTriggers an Emergency Pack => No
2021-04-30 15:59ahernandezgilIssue Monitored: ahernandezgil
2021-05-03 12:33vmromanosNote Added: 0127736
2021-05-03 12:33vmromanosAssigned ToTriage Finance => salvador_campanella
2021-05-03 12:33vmromanosStatusnew => feedback
2021-05-03 17:46salvador_campanellaNote Added: 0127753
2021-05-03 17:46salvador_campanellaNote View State: public: 127753
2021-05-04 12:15vmromanosAssigned Tosalvador_campanella => Triage Finance
2021-05-04 12:15vmromanosStatusfeedback => new
2021-05-07 12:13vmromanosStatusnew => scheduled
2021-05-07 12:13vmromanosAssigned ToTriage Finance => vmromanos
2021-05-11 18:47vmromanosNote Added: 0128009
2021-05-12 09:35vmromanosNote Added: 0128020
2021-05-12 11:14hgbotMerge Request Status => open
2021-05-12 11:14hgbotNote Added: 0128032
2021-05-18 12:02hgbotMerge Request Statusopen => approved
2021-05-19 13:06hgbotResolutionopen => fixed
2021-05-19 13:06hgbotStatusscheduled => closed
2021-05-19 13:06hgbotFixed in Version => PR21Q3
2021-05-19 13:06hgbotNote Added: 0128396
2021-05-19 13:06hgbotNote Added: 0128397

Notes
(0127736)
vmromanos   
2021-05-03 12:33   
Please provide the explain plan to properly understand the problem.
(0127753)
salvador_campanella   
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   
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   
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   
2021-05-12 11:14   
Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/359 [^]
(0128396)
hgbot   
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   
2021-05-19 13:06   
Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/359 [^]