Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035872Openbravo ERP09. Financial managementpublic2017-04-28 10:142017-05-29 17:43
ReporterJONHM 
Assigned Tomarkmm82 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q3 
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
Summary0035872: Payment Report has performance problems if expiration date is not set
DescriptionPayment Report has performance problems if expiration date is not set
Steps To Reproduce*** ASK ENVIRONMENT TO JONHM

- Number of records in table fin_payment is "169343"

*Running payment report with expiration date = '01/01/2015': 10 seconds
 -> 54 records
*Running payment report with no expiration date: 31 minutes
 -> Same 54 records
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to defect 0032510 closed AtulOpenbravo Openbravo ERP Payment reprort: it is very slow with big data 
related to defect 0026701 closed rafaroda Modules High memory usage when executing the payment report 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2017-04-28 10:14JONHMNew Issue
2017-04-28 10:14JONHMAssigned To => Triage Finance
2017-04-28 10:14JONHMModules => Core
2017-04-28 10:14JONHMResolution time => 1495144800
2017-04-28 10:14JONHMTriggers an Emergency Pack => No
2017-05-03 12:44aferrazRelationship addedrelated to 0032510
2017-05-03 12:44aferrazAssigned ToTriage Finance => markmm82
2017-05-03 12:47aferrazRelationship addedrelated to 0026701
2017-05-05 17:36markmm82Statusnew => scheduled
2017-05-22 12:59aferrazNote Added: 0096707
2017-05-25 11:40aferrazNote Added: 0096788
2017-05-25 12:26hgbotCheckin
2017-05-25 12:26hgbotNote Added: 0096791
2017-05-25 12:26hgbotStatusscheduled => resolved
2017-05-25 12:26hgbotResolutionopen => fixed
2017-05-25 12:26hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/2cea82959e2dcbba346a2a047154c81738ef3974 [^]
2017-05-25 12:27aferrazReview Assigned To => aferraz
2017-05-25 12:27aferrazNote Added: 0096792
2017-05-25 12:27aferrazStatusresolved => closed
2017-05-25 12:27aferrazFixed in Version => 3.0PR17Q3
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096989

Notes
(0096707)
aferraz   
2017-05-22 12:59   
This SQL could be improved like this (it takes around 2 seconds in client environment):

SELECT fpsd.fin_payment_scheduledetail_id, (
  SELECT arl.seqno
  FROM ad_ref_list arl
  WHERE arl.ad_reference_id = '575BCB88A4694C27BC013DE9C73E6FE7'
  AND arl.value = COALESCE(fp.status, 'RPAP')
) AS seqno
FROM fin_payment_scheduledetail fpsd
LEFT JOIN fin_payment_detail fpd ON fpsd.fin_payment_detail_id = fpd.fin_payment_detail_id
LEFT JOIN fin_payment fp ON fpd.fin_payment_id = fp.fin_payment_id
LEFT JOIN fin_payment_schedule fps ON fpsd.fin_payment_schedule_invoice = fps.fin_payment_schedule_id
LEFT JOIN c_invoice i ON fps.c_invoice_id = i.c_invoice_id
WHERE fpsd.ad_org_id IN ('9CACE370E47F4EEAB88E3A2FE3BD9866', '93E80626532A426DA815879E47EB541A', '382B17C5F8AB482C970DA9E16331D8BE')
AND ((fp.status IN ('RPAP')
AND fp.c_bpartner_id = '938BAFD9EA964958AB1F3F473FE0F213'
AND fp.fin_paymentmethod_id = 'C05ECE6F565D49B3871D05338A1DD5CE'
AND fp.isreceipt = 'Y')
OR (fpsd.fin_payment_detail_id IS NULL
AND i.c_bpartner_id = '938BAFD9EA964958AB1F3F473FE0F213'
AND fps.fin_paymentmethod_id = 'C05ECE6F565D49B3871D05338A1DD5CE'
AND i.issotrx = 'Y'))
ORDER BY seqno, COALESCE(fp.status, 'RPAP'), i.dateinvoiced, fpsd.fin_payment_schedule_invoice;
(0096788)
aferraz   
2017-05-25 11:40   
If we only change the following clause:
(fin_paymen1_.fin_payment_detail_id IS NOT NULL OR fin_paymen3_.fin_payment_schedule_id IS NOT NULL)

with the following one:
(fin_paymen0_.fin_payment_detail_id IS NOT NULL OR fin_paymen0_.fin_payment_schedule_invoice IS NOT NULL)

the query improves a lot: it takes less than 2 seconds in client environment.

Duplicated filter by ad_org_id should be also removed.

Final query:

SELECT fin_paymen0_.fin_payment_scheduledetail_id AS
       col_0_0_,
       (SELECT adlist5_.seqno
        FROM ad_ref_list adlist5_
        WHERE adlist5_.ad_reference_id = '575BCB88A4694C27BC013DE9C73E6FE7'
               AND adlist5_.value = COALESCE(fin_paymen2_.status, 'RPAP')) AS
       col_1_0_
FROM fin_payment_scheduledetail fin_paymen0_
       LEFT OUTER JOIN fin_payment_detail fin_paymen1_
                    ON fin_paymen0_.fin_payment_detail_id =
                       fin_paymen1_.fin_payment_detail_id
       LEFT OUTER JOIN fin_payment fin_paymen2_
                    ON fin_paymen1_.fin_payment_id = fin_paymen2_.fin_payment_id
       LEFT OUTER JOIN fin_payment_schedule fin_paymen3_
                    ON fin_paymen0_.fin_payment_schedule_invoice =
                       fin_paymen3_.fin_payment_schedule_id
       LEFT OUTER JOIN c_invoice invoice4_
                    ON fin_paymen3_.c_invoice_id = invoice4_.c_invoice_id
WHERE (fin_paymen0_.fin_payment_detail_id IS NOT NULL OR fin_paymen0_.fin_payment_schedule_invoice IS NOT NULL)
       AND ( fin_paymen0_.ad_org_id IN (
                   '9CACE370E47F4EEAB88E3A2FE3BD9866',
                   '93E80626532A426DA815879E47EB541A',
                                             '382B17C5F8AB482C970DA9E16331D8BE'
                                       ) )
       AND ( COALESCE(fin_paymen2_.c_bpartner_id, invoice4_.c_bpartner_id) IN (
                 '938BAFD9EA964958AB1F3F473FE0F213' ) )
       AND ( fin_paymen2_.status IN ( 'RPAP' )
              OR fin_paymen0_.fin_payment_detail_id IS NULL )
       AND
COALESCE(fin_paymen2_.fin_paymentmethod_id,
fin_paymen3_.fin_paymentmethod_id) = 'C05ECE6F565D49B3871D05338A1DD5CE'
       AND ( fin_paymen2_.isreceipt = 'Y'
              OR invoice4_.issotrx = 'Y' )
ORDER BY col_1_0_,
          COALESCE(fin_paymen2_.status, 'RPAP'),
          invoice4_.dateinvoiced,
          fin_paymen0_.fin_payment_schedule_invoice;
(0096791)
hgbot   
2017-05-25 12:26   
Repository: erp/devel/pi
Changeset: 2cea82959e2dcbba346a2a047154c81738ef3974
Author: Mark <markmm82 <at> gmail.com>
Date: Mon May 22 10:24:17 2017 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/2cea82959e2dcbba346a2a047154c81738ef3974 [^]

Fixes issue 35872: Fixed Payment Report performance problems

We have changed following condition:
where (fpd is not null or invps is not null)
with this one:
where (fpsd.paymentDetails is not null or fpsd.invoicePaymentSchedule is not null)
in order to use fin_payment_scheduledetail table and avoid join with
fin_payment_detail and fin_payment_schedule tables when both have null values.

Also was removed duplicated filter by organization.

---
M modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java
---
(0096792)
aferraz   
2017-05-25 12:27   
Code review + Testing OK
(0096989)
hudsonbot   
2017-05-29 17:43   
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/1ee70113bdc4 [^]
Maturity status: Test