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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0035872
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 09. Financial managementmajoralways2017-04-28 10:142017-05-29 17:43
ReporterJONHMView Statuspublic 
Assigned Tomarkmm82 
PriorityhighResolutionfixedFixed in Version3.0PR17Q3
StatusclosedFix in branchFixed in SCM revision2cea82959e2d
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toaferraz
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0035872: 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
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0032510 closedAtulOpenbravo Openbravo ERP Payment reprort: it is very slow with big data 
related to defect 0026701 closedrafaroda Modules High memory usage when executing the payment report 

-  Notes
(0096707)
aferraz (manager)
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 (manager)
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 (developer)
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 (manager)
2017-05-25 12:27

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

- Issue History
Date Modified Username Field Change
2017-04-28 10:14 JONHM New Issue
2017-04-28 10:14 JONHM Assigned To => Triage Finance
2017-04-28 10:14 JONHM Modules => Core
2017-04-28 10:14 JONHM Resolution time => 1495144800
2017-04-28 10:14 JONHM Triggers an Emergency Pack => No
2017-05-03 12:44 aferraz Relationship added related to 0032510
2017-05-03 12:44 aferraz Assigned To Triage Finance => markmm82
2017-05-03 12:47 aferraz Relationship added related to 0026701
2017-05-05 17:36 markmm82 Status new => scheduled
2017-05-22 12:59 aferraz Note Added: 0096707
2017-05-25 11:40 aferraz Note Added: 0096788
2017-05-25 12:26 hgbot Checkin
2017-05-25 12:26 hgbot Note Added: 0096791
2017-05-25 12:26 hgbot Status scheduled => resolved
2017-05-25 12:26 hgbot Resolution open => fixed
2017-05-25 12:26 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/2cea82959e2dcbba346a2a047154c81738ef3974 [^]
2017-05-25 12:27 aferraz Review Assigned To => aferraz
2017-05-25 12:27 aferraz Note Added: 0096792
2017-05-25 12:27 aferraz Status resolved => closed
2017-05-25 12:27 aferraz Fixed in Version => 3.0PR17Q3
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096989

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker