Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0032510Openbravo ERP09. Financial managementpublic2016-03-21 12:062016-06-17 19:37
Reportermalsasua 
Assigned ToAtulOpenbravo 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version3.0PR15Q4.3 
Target VersionFixed in Version3.0PR16Q3 
Merge Request Status
Review Assigned Toaferraz
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0032510: Payment reprort: it is very slow with big data
DescriptionIn a environment with
220K of payment schedule details
205K of payment details
203K of payments

the execution of payment report to one day is very slow (more than 10 minutes)
Steps To Reproduce. execute the report:
 due date from: 20/06/2015
 due date to: 20/06/2015

The query that it is very slow is:
select fin_paymen0_.FIN_Payment_Scheduledetail_ID as col_0_0_, (select adlist17_.Seqno from AD_Ref_List adlist17_ where adlist17_.AD_Reference_ID='575BCB88A4694C27BC013DE9C73E6FE7' and adlist17_.Value=coalesce(fin_p...
(attached the query complete)

in java:
modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java
Proposed Solution
Additional Information
TagsPerformance
Relationships
related to defect 0035872 closed markmm82 Payment Report has performance problems if expiration date is not set 
Attached Filestxt query_slow.txt (4,586) 2016-03-21 12:06
https://issues.openbravo.com/file_download.php?file_id=9186&type=bug
Issue History
Date ModifiedUsernameFieldChange
2016-03-21 12:06malsasuaNew Issue
2016-03-21 12:06malsasuaAssigned To => Triage Finance
2016-03-21 12:06malsasuaFile Added: query_slow.txt
2016-03-21 12:06malsasuaModules => Core
2016-03-21 12:06malsasuaResolution time => 1461535200
2016-03-21 12:06malsasuaTriggers an Emergency Pack => No
2016-03-21 12:33malsasuaTag Attached: Performance
2016-04-05 12:10aferrazAssigned ToTriage Finance => AtulOpenbravo
2016-04-13 12:46AtulOpenbravoStatusnew => scheduled
2016-04-22 15:30hgbotCheckin
2016-04-22 15:30hgbotNote Added: 0085902
2016-04-22 15:30hgbotStatusscheduled => resolved
2016-04-22 15:30hgbotResolutionopen => fixed
2016-04-22 15:30hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/d1e5ce861cccd5ef8885f0842afcbff3850dc04a [^]
2016-04-22 15:31hgbotCheckin
2016-04-22 15:31hgbotNote Added: 0085903
2016-04-22 15:31aferrazReview Assigned To => aferraz
2016-04-22 15:31aferrazNote Added: 0085904
2016-04-22 15:31aferrazStatusresolved => closed
2016-04-22 15:31aferrazFixed in Version => 3.0PR16Q3
2016-04-26 13:49hgbotCheckin
2016-04-26 13:49hgbotNote Added: 0085988
2016-06-17 19:37hudsonbotCheckin
2016-06-17 19:37hudsonbotNote Added: 0087487
2016-06-17 19:37hudsonbotCheckin
2016-06-17 19:37hudsonbotNote Added: 0087488
2016-06-17 19:37hudsonbotCheckin
2016-06-17 19:37hudsonbotNote Added: 0087491
2017-05-03 12:44aferrazRelationship addedrelated to 0035872

Notes
(0085902)
hgbot   
2016-04-22 15:30   
Repository: erp/devel/pi
Changeset: d1e5ce861cccd5ef8885f0842afcbff3850dc04a
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Fri Apr 22 12:04:27 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/d1e5ce861cccd5ef8885f0842afcbff3850dc04a [^]

Fixes issue 32510: Improve performance of Payment Report

- Improve hql query.
- Avoid joins in query if they are not necessary.
- Retrieve only id instead of dal object in query.
- Use !isEmpty() instead of size()>0.
- Use setMaxResults(1) and uniqueResult() instead of list().get(0).
- Improve simple queries to return needed column instead of full dal object and avoid looping through results.

---
M modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java
---
(0085903)
hgbot   
2016-04-22 15:31   
Repository: erp/devel/pi
Changeset: ec5b60a23972efcb6c49918a1321467aba5a5b0e
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Fri Apr 22 15:20:33 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/ec5b60a23972efcb6c49918a1321467aba5a5b0e [^]

Related to issue 32510: Code review improvements

---
M modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java
---
(0085904)
aferraz   
2016-04-22 15:31   
Code review + Testing OK
(0085988)
hgbot   
2016-04-26 13:49   
Repository: erp/devel/pi
Changeset: 6c9f1be432ef7fe3659c80bb2315d60e1213d674
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Tue Apr 26 13:45:34 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/6c9f1be432ef7fe3659c80bb2315d60e1213d674 [^]

Related to issue 32510: Code review improvements

Do left join to inv.businessPartner also if financial account has been selected.
Use StringUtils to avoid NPE.

---
M modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java
---
(0087487)
hudsonbot   
2016-06-17 19:37   
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/0dc7be081b1c [^]
Maturity status: Test
(0087488)
hudsonbot   
2016-06-17 19:37   
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/0dc7be081b1c [^]
Maturity status: Test
(0087491)
hudsonbot   
2016-06-17 19:37   
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/0dc7be081b1c [^]
Maturity status: Test