Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0033629Openbravo ERP09. Financial managementpublic2016-08-05 09:322016-09-07 19:58
Reporteralostale 
Assigned Toalostale 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR16Q4 
Merge Request Status
Review Assigned Toaferraz
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0033629: slow order/invoice grid population in Add Payment
DescriptionWhen Add Payment process is opened, the population of the order/invoice table is very slow in environments with big volume of data in FIN_Payment_ScheduleDetail table (tested in an env with 7.16927e+06 rows in that table).

The use case is opening that process from Payment In window having selected a Received From partner. In this case a Seq Scan is performed on FIN_Payment_ScheduleDetail table:
  * BP is obtained from order or invoice instead of directly from FIN_Payment_ScheduleDetail
  * BP column is not indexed
Steps To ReproduceIn an env with 7M rows in FIN_Payment_ScheduleDetail:
  1. Create a new Payment In with Received from field set
  2. Open Add Payment process:
     -> Check it is slow to populate order/invoice grid (depending on the case it can be up to 5minutes
     -> Check the plan for the generated query: there is a Seq Scan to FIN_Payment_ScheduleDetail
Proposed SolutionFIN_Payment_ScheduleDetail BP column is populated and matches the same BP its related order/invoice has, so the proposal is to get BP directly from FIN_Payment_ScheduleDetail and create an index for it.

In this way we would be able to:
 1. Remove 2 left joins to BP table from order and invoice
 2. Improve BP filtering by querying directly FIN_Payment_ScheduleDetail.BP which will be indexed

Find attached proposed fix:
  * before fix:(cost=520149.52..520149.52 rows=1 width=525) actual time=302199.311
  * after fix: (cost=1267.79..1267.79 rows=1 width=462) actual time=0.037
Additional Information
TagsPerformance
Relationships
related to defect 00335813.0PR17Q2 closed vmromanos Openbravo ERP slow defaults retrieval in add payment 
depends on defect 00275703.0PR15Q1 closed fsoto82 Openbravo ERP The Business Partner should be filled in the Payment Out Lines 
depends on defect 0033671 closed mario_castello Retail Modules FIN_PaymentScheduleDetail created from POS doesn't include the Business Partner 
depends on defect 0033776 closed Sanjota Openbravo ERP Business partner being null in Payment scheduledetails 
related to defect 0038785 closed aferraz Openbravo ERP perfomance issue in add details button of payment in window 
related to design defect 0038930 new Triage Omni OMS Openbravo ERP Perfomance issue in add details button of payment in window 
Attached Files? 33629.export (8,585) 2016-08-05 10:04
https://issues.openbravo.com/file_download.php?file_id=9679&type=bug
Issue History
Date ModifiedUsernameFieldChange
2016-08-05 09:32alostaleNew Issue
2016-08-05 09:32alostaleAssigned To => Triage Finance
2016-08-05 09:32alostaleOBNetwork customer => No
2016-08-05 09:32alostaleModules => Core
2016-08-05 09:32alostaleTriggers an Emergency Pack => No
2016-08-05 09:32alostaleTag Attached: Performance
2016-08-05 09:32alostaleRelationship addedrelated to 0033581
2016-08-05 09:58alostaleProposed Solution updated
2016-08-05 10:04alostaleProposed Solution updated
2016-08-05 10:04alostaleFile Added: 33629.export
2016-08-05 11:11vmromanosRelationship addeddepends on 0027570
2016-08-05 11:18vmromanosNote Added: 0088837
2016-08-08 11:44alostaleNote Added: 0088881
2016-08-10 10:39vmromanosRelationship addeddepends on 0033671
2016-08-23 23:17SanjotaRelationship addeddepends on 0033776
2016-08-30 11:58SanjotaNote Added: 0089544
2016-09-07 10:24hgbotCheckin
2016-09-07 10:24hgbotNote Added: 0089798
2016-09-07 10:24hgbotStatusnew => resolved
2016-09-07 10:24hgbotResolutionopen => fixed
2016-09-07 10:24hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/076cf96db90296f74cf366a57a45160d7e8eec31 [^]
2016-09-07 10:24hgbotCheckin
2016-09-07 10:24hgbotNote Added: 0089799
2016-09-07 10:25aferrazAssigned ToTriage Finance => alostale
2016-09-07 10:25aferrazReview Assigned To => aferraz
2016-09-07 10:25aferrazNote Added: 0089800
2016-09-07 10:25aferrazStatusresolved => closed
2016-09-07 10:25aferrazFixed in Version => 3.0PR16Q4
2016-09-07 19:58hudsonbotCheckin
2016-09-07 19:58hudsonbotNote Added: 0089814
2016-09-07 19:58hudsonbotCheckin
2016-09-07 19:58hudsonbotNote Added: 0089815
2018-06-19 14:07aferrazRelationship addedrelated to 0038785
2018-07-18 10:21aferrazRelationship addedrelated to 0038930

Notes
(0088837)
vmromanos   
2016-08-05 11:18   
In theory the proposed solution seems to be right, because in the business partner column in PSD is always populated for Orders and Invoices (see 0027570).

However I have detected that we could have an scenario where this rule is not fulfilled; when you run an install.source, the UpdateBpPaymentLine module script is executed in sampledata and it updates 1 record:

Executing moduleScript: org.openbravo.modulescript.UpdateBpPaymentLine
Updated 1 Payment Scheduled Details.



Before applying the proposed solution, we need to understand the reason why the module script updates that record. It might be a real bug in core that we should fix at the same time we fix this issue, or it might be just a problem in sampledata.
(0088881)
alostale   
2016-08-08 11:44   
Proposed solution works correctly in PostgreSQL but it fails in Oracle.

See try-aprm execution:

Changeset : https://code.openbravo.com/erp/devel/try/graph/fb46016e03f7c866612ad12f4e3159955da050b5 [^]

Description : addPayment
default+apr
Automation-repo : pi
PostgreSQL version : 9.1


FAILED:
        https://ci.openbravo.com//job/try-checks-oracle/4420/ [^]
        https://ci.openbravo.com//job/try-basic-oracle/4486/ [^]
        https://ci.openbravo.com//job/try-addpayment-oracle/62/ [^]
        https://ci.openbravo.com//job/try-transaction-oracle/61/ [^]
        https://ci.openbravo.com//job/try-reconciliation-oracle/62/ [^]
        https://ci.openbravo.com//job/try-aprinreg-oracle/65/ [^]
        https://ci.openbravo.com//job/try-aproutreg-oracle/63/ [^]
        https://ci.openbravo.com//job/try-salproreg-oracle/61/ [^]

SUCCESS:
        https://ci.openbravo.com//job/try-full-pgsql/4610/ [^]
        https://ci.openbravo.com//job/try-full-oracle/4597/ [^]
        https://ci.openbravo.com//job/try-checks-pgsql/4419/ [^]
        https://ci.openbravo.com//job/try-dbcons-pgsql/4233/ [^]
        https://ci.openbravo.com//job/try-dbcons-oracle/4244/ [^]
        https://ci.openbravo.com//job/try-api/3872/ [^]
        https://ci.openbravo.com//job/try-gui-pgsql/4535/ [^]
        https://ci.openbravo.com//job/try-gui-oracle/4538/ [^]
        https://ci.openbravo.com//job/try-initial-pgsql/4489/ [^]
        https://ci.openbravo.com//job/try-initial-oracle/4491/ [^]
        https://ci.openbravo.com//job/try-basic-pgsql/4485/ [^]
        https://ci.openbravo.com//job/try-modules-pgsql/4430/ [^]
        https://ci.openbravo.com//job/try-upgrade-pgsql/4404/ [^]
        https://ci.openbravo.com//job/try-dummy-pgsql/4468/ [^]
        https://ci.openbravo.com//job/try-addpayment-pgsql/66/ [^]
        https://ci.openbravo.com//job/try-transaction-pgsql/63/ [^]
        https://ci.openbravo.com//job/try-reconciliation-pgsql/62/ [^]
        https://ci.openbravo.com//job/try-aprinreg-pgsql/64/ [^]
        https://ci.openbravo.com//job/try-aproutreg-pgsql/62/ [^]
        https://ci.openbravo.com//job/try-salproreg-pgsql/61/ [^]
(0089544)
Sanjota   
2016-08-30 11:58   
Test plan:
1) During install.source 1 PSD record was updating
Executing moduleScript: org.openbravo.modulescript.UpdateBpPaymentLine
Updated 1 Payment Scheduled Details.
This is due to sample data, in which business partner was null for PSD related to invoice
There are scenario in which business partner in PSD can be null.
Details are explained and fixed in issue 33776

2) Test failures in oracle due to proposed solution.
This is grammer exception in select query of the proposed solution.
This is fixed by changing the
selectClause.append(" bp.name as businessPartnerName, ");
to selectClause.append(getAggregatorFunction("bp.name") + " as businessPartnerName, ");
in the proposed solution.
(0089798)
hgbot   
2016-09-07 10:24   
Repository: erp/devel/pi
Changeset: 076cf96db90296f74cf366a57a45160d7e8eec31
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Aug 05 10:03:10 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/076cf96db90296f74cf366a57a45160d7e8eec31 [^]

fixed bug 33629: slow order/invoice grid population in Add Payment Description

  In evironments with big volumes it was slow because it performed a Seq Scan
  on FIN_Payment_ScheduleDetail table.

  It has been fixed for the cases when BP is preset by:
   - Removing left joins to get BP from order/invoice to retrieve it directly
     from FIN_Payment_ScheduleDetail
   - Adding an index on FIN_Payment_ScheduleDetail.BP so that previous query
     does not require of a Seq Scan

---
M modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml
M modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java
M src-db/database/model/tables/FIN_PAYMENT_SCHEDULEDETAIL.xml
---
(0089799)
hgbot   
2016-09-07 10:24   
Repository: erp/devel/pi
Changeset: 077bc1244bc04572b4eb8372ac9e786d8563b835
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Tue Sep 06 12:04:58 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/077bc1244bc04572b4eb8372ac9e786d8563b835 [^]

Related to issue 33629: Code review improvements

Group by bp.id and bp.name instead of by bp to avoid errors in Oracle.

---
M modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java
---
(0089800)
aferraz   
2016-09-07 10:25   
Code review + Testing OK
(0089814)
hudsonbot   
2016-09-07 19:58   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/97008b02fc55 [^]
Maturity status: Test
(0089815)
hudsonbot   
2016-09-07 19:58   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/97008b02fc55 [^]
Maturity status: Test