Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0033629
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 09. Financial managementmajorhave not tried2016-08-05 09:322016-09-07 19:58
ReporteralostaleView Statuspublic 
Assigned Toalostale 
PrioritynormalResolutionfixedFixed in Version3.0PR16Q4
StatusclosedFix in branchFixed in SCM revision076cf96db902
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
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
Summary

0033629: 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
TagsPerformance
Attached Files? file icon 33629.export [^] (8,585 bytes) 2016-08-05 10:04

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 00335813.0PR17Q2 closedvmromanos Openbravo ERP slow defaults retrieval in add payment 
depends on defect 00275703.0PR15Q1 closedfsoto82 Openbravo ERP The Business Partner should be filled in the Payment Out Lines 
depends on defect 0033671 closedmario_castello Retail Modules FIN_PaymentScheduleDetail created from POS doesn't include the Business Partner 
depends on defect 0033776 closedSanjota Openbravo ERP Business partner being null in Payment scheduledetails 
related to defect 0038785 closedaferraz Openbravo ERP perfomance issue in add details button of payment in window 
related to design defect 0038930 newTriage Omni OMS Openbravo ERP Perfomance issue in add details button of payment in window 

-  Notes
(0088837)
vmromanos (viewer)
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 (viewer)
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 (viewer)
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 (developer)
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 (developer)
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 (viewer)
2016-09-07 10:25

 Code review + Testing OK
(0089814)
hudsonbot (viewer)
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 (viewer)
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

- Issue History
Date Modified Username Field Change
2016-08-05 09:32 alostale New Issue
2016-08-05 09:32 alostale Assigned To => Triage Finance
2016-08-05 09:32 alostale OBNetwork customer => No
2016-08-05 09:32 alostale Modules => Core
2016-08-05 09:32 alostale Triggers an Emergency Pack => No
2016-08-05 09:32 alostale Tag Attached: Performance
2016-08-05 09:32 alostale Relationship added related to 0033581
2016-08-05 09:58 alostale Proposed Solution updated
2016-08-05 10:04 alostale Proposed Solution updated
2016-08-05 10:04 alostale File Added: 33629.export
2016-08-05 11:11 vmromanos Relationship added depends on 0027570
2016-08-05 11:18 vmromanos Note Added: 0088837
2016-08-08 11:44 alostale Note Added: 0088881
2016-08-10 10:39 vmromanos Relationship added depends on 0033671
2016-08-23 23:17 Sanjota Relationship added depends on 0033776
2016-08-30 11:58 Sanjota Note Added: 0089544
2016-09-07 10:24 hgbot Checkin
2016-09-07 10:24 hgbot Note Added: 0089798
2016-09-07 10:24 hgbot Status new => resolved
2016-09-07 10:24 hgbot Resolution open => fixed
2016-09-07 10:24 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/076cf96db90296f74cf366a57a45160d7e8eec31 [^]
2016-09-07 10:24 hgbot Checkin
2016-09-07 10:24 hgbot Note Added: 0089799
2016-09-07 10:25 aferraz Assigned To Triage Finance => alostale
2016-09-07 10:25 aferraz Review Assigned To => aferraz
2016-09-07 10:25 aferraz Note Added: 0089800
2016-09-07 10:25 aferraz Status resolved => closed
2016-09-07 10:25 aferraz Fixed in Version => 3.0PR16Q4
2016-09-07 19:58 hudsonbot Checkin
2016-09-07 19:58 hudsonbot Note Added: 0089814
2016-09-07 19:58 hudsonbot Checkin
2016-09-07 19:58 hudsonbot Note Added: 0089815
2018-06-19 14:07 aferraz Relationship added related to 0038785
2018-07-18 10:21 aferraz Relationship added related to 0038930

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker