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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0038045
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 09. Financial managementmajoralways2018-03-05 18:122018-03-19 16:19
ReporteregoitzView Statuspublic 
Assigned Tomarkmm82 
PrioritynormalResolutionfixedFixed in Version3.0PR18Q2
StatusclosedFix in branchFixed in SCM revisione8edf135fc2a
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

0038045: Slow query when executing the payment report

DescriptionDuring the execution of the payment report on an environment with thousands/million of payments and payment details, there is a query that is executed and that takes more than a minute.

The query is:

SELECT DISTINCT fin_paymen4_.C_Invoice_ID AS col_0_0_ FROM FIN_Payment_Credit fin_paymen0_ CROSS JOIN FIN_Payment fin_paymen1_ CROSS JOIN FIN_Payment fin_paymen2_ CROSS JOIN FIN_Payment_Detail_V fin_paymen3_, FIN_Payment_Sched_Inv_V fin_paymen4_ WHERE fin_paymen3_.Fin_Payment_Sched_Inv_V_Id = fin_paymen4_.Fin_Payment_Sched_Inv_V_ID AND fin_paymen1_.Fin_Payment_ID = fin_paymen0_.FIN_Payment_Id_Used AND fin_paymen0_.FIN_Payment_ID = fin_paymen2_.Fin_Payment_ID AND fin_paymen3_.FIN_Payment_ID = fin_paymen2_.Fin_Payment_ID AND fin_paymen1_.Fin_Payment_ID = ?;
Steps To ReproduceExecute the report (or only the query) on an enviromment with big amount of data.

Ask support for an environment
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]

-  Notes
(0103068)
markmm82 (developer)
2018-03-07 19:18
edited on: 2018-03-16 16:40

 The cost of the original query is around 2223835.88, and the cost of the new query is 51.88. Also the report hasn't delays when it is executed.

--Original query
EXPLAIN ANALYZE SELECT DISTINCT fin_paymen4_.C_Invoice_ID AS col_0_0_
FROM FIN_Payment_Credit fin_paymen0_
  CROSS JOIN FIN_Payment fin_paymen1_
  CROSS JOIN FIN_Payment fin_paymen2_
  CROSS JOIN FIN_Payment_Detail_V fin_paymen3_,
  FIN_Payment_Sched_Inv_V fin_paymen4_
WHERE fin_paymen3_.Fin_Payment_Sched_Inv_V_Id = fin_paymen4_.Fin_Payment_Sched_Inv_V_ID
  AND fin_paymen1_.Fin_Payment_ID = fin_paymen0_.FIN_Payment_Id_Used
  AND fin_paymen0_.FIN_Payment_ID = fin_paymen2_.Fin_Payment_ID
  AND fin_paymen3_.FIN_Payment_ID = fin_paymen2_.Fin_Payment_ID
  AND fin_paymen1_.Fin_Payment_ID = 'F2EBB3A4C90B40B6AD17203379BDBF59';

/*
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate (cost=2223835.87..2223835.88 rows=1 width=33) (actual time=0.806..0.806 rows=0 loops=1)
   -> Nested Loop (cost=1096630.24..2223835.86 rows=1 width=33) (actual time=0.805..0.805 rows=0 loops=1)
         Join Filter: ((psd.fin_payment_schedule_invoice)::text = (ps.fin_payment_schedule_id)::text)
         -> Nested Loop (cost=1096629.68..2223834.88 rows=1 width=66) (actual time=0.804..0.804 rows=0 loops=1)
               Join Filter: ((p.fin_payment_id)::text = (fin_paymen2_.fin_payment_id)::text)
               -> Nested Loop (cost=1096629.12..2223833.87 rows=1 width=165) (actual time=0.803..0.803 rows=0 loops=1)
                     -> Nested Loop (cost=1096628.56..2223825.28 rows=1 width=198) (actual time=0.803..0.803 rows=0 loops=1)
                           -> Hash Join (cost=1096628.00..2223819.21 rows=6 width=165) (actual time=0.803..0.803 rows=0 loops=1)
                                 Hash Cond: ((pd.fin_payment_id)::text = (fin_paymen0_.fin_payment_id)::text)
                                 -> Hash Join (cost=1096446.34..2202384.51 rows=5667459 width=99) (never executed)
                                       Hash Cond: ((psd.fin_payment_schedule_invoice)::text = (psi.fin_payment_schedule_id)::text)
                                       -> Hash Join (cost=435566.21..1188328.79 rows=5693840 width=66) (never executed)
                                             Hash Cond: ((psd.fin_payment_detail_id)::text = (pd.fin_payment_detail_id)::text)
                                             -> Seq Scan on fin_payment_scheduledetail psd (cost=0.00..363551.59 rows=5881459 width=99) (never executed)
                                             -> Hash (cost=274174.65..274174.65 rows=5738365 width=99) (never executed)
                                                   -> Seq Scan on fin_payment_detail pd (cost=0.00..274174.65 rows=5738365 width=99) (never executed)
                                       -> Hash (cost=470999.28..470999.28 rows=7840228 width=66) (never executed)
                                             -> Seq Scan on fin_payment_schedule psi (cost=0.00..470999.28 rows=7840228 width=66) (never executed)
                                 -> Hash (cost=181.65..181.65 rows=1 width=66) (actual time=0.798..0.798 rows=0 loops=1)
                                       Buckets: 1024 Batches: 1 Memory Usage: 0kB
                                       -> Seq Scan on fin_payment_credit fin_paymen0_ (cost=0.00..181.65 rows=1 width=66) (actual time=0.797..0.797 rows=0 loops=1)
                                             Filter: ((fin_payment_id_used)::text = 'F2EBB3A4C90B40B6AD17203379BDBF59'::text)
                                             Rows Removed by Filter: 3103
                           -> Index Scan using fin_payment_key on fin_payment p (cost=0.56..1.00 rows=1 width=66) (never executed)
                                 Index Cond: ((fin_payment_id)::text = (pd.fin_payment_id)::text)
                     -> Index Only Scan using fin_payment_key on fin_payment fin_paymen1_ (cost=0.56..8.57 rows=1 width=33) (never executed)
                           Index Cond: (fin_payment_id = 'F2EBB3A4C90B40B6AD17203379BDBF59'::text)
                           Heap Fetches: 0
               -> Index Only Scan using fin_payment_key on fin_payment fin_paymen2_ (cost=0.56..1.00 rows=1 width=33) (never executed)
                     Index Cond: (fin_payment_id = (pd.fin_payment_id)::text)
                     Heap Fetches: 0
         -> Index Scan using fin_payment_schedule_key on fin_payment_schedule ps (cost=0.56..0.97 rows=1 width=66) (never executed)
               Index Cond: ((fin_payment_schedule_id)::text = (psi.fin_payment_schedule_id)::text)
 Total runtime: 1.489 ms
*/


--NEW HQL QUERY
/*
select distinct(psiv.invoice.id)
    from FIN_Payment_Sched_Inv_V psiv
    where exists (
      select 1
      from FIN_Payment_Credit pc, FIN_Payment_Detail pd,
        FIN_Payment_ScheduleDetail psd, FIN_Payment_Schedule psi
      where
        pc.creditPaymentUsed.id = 'DC30D44BD8C44FA5B9DFF6ABD8A5520C'
        and pd.finPayment=pc.payment
        and pd.id = psd.paymentDetails.id
        and psd.invoicePaymentSchedule.id = psi.id
        and psi.id = psiv.id
    );
    */
    
--Generated SQL
explain select distinct fin_paymen0_.C_Invoice_ID as col_0_0_
from FIN_Payment_Sched_Inv_V fin_paymen0_
where exists (
   select 1
   from FIN_Payment_Credit fin_paymen1_
     cross join FIN_Payment_Detail fin_paymen2_
     cross join FIN_Payment_ScheduleDetail fin_paymen3_
     cross join FIN_Payment_Schedule fin_paymen4_
   where fin_paymen1_.FIN_Payment_Id_Used='DC30D44BD8C44FA5B9DFF6ABD8A5520C'
     and fin_paymen2_.Fin_Payment_ID=fin_paymen1_.FIN_Payment_ID
     and fin_paymen2_.Fin_Payment_Detail_ID=fin_paymen3_.FIN_Payment_Detail_ID
     and fin_paymen3_.FIN_Payment_Schedule_Invoice=fin_paymen4_.Fin_Payment_Schedule_ID
     and fin_paymen4_.Fin_Payment_Schedule_ID=fin_paymen0_.Fin_Payment_Sched_Inv_V_ID);
     
/* QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate (cost=51.82..51.88 rows=6 width=33)
   -> Nested Loop (cost=47.36..51.81 rows=6 width=33)
         Join Filter: ((ps.fin_payment_schedule_id)::text = (fin_paymen3_.fin_payment_schedule_invoice)::text)
         -> HashAggregate (cost=46.80..46.86 rows=6 width=66)
               -> Nested Loop (cost=6.00..46.79 rows=6 width=66)
                     -> Nested Loop (cost=5.44..41.96 rows=6 width=33)
                           -> Nested Loop (cost=4.88..36.94 rows=6 width=33)
                                 -> Index Scan using fin_paymentcredit_payment_id_usedx on fin_payment_credit fin_paymen1_ (cost=0.28..8.30 rows=1 width=33)
                                       Index Cond: ((fin_payment_id_used)::text = 'DC30D44BD8C44FA5B9DFF6ABD8A5520C'::text)
                                 -> Bitmap Heap Scan on fin_payment_detail fin_paymen2_ (cost=4.60..28.58 rows=6 width=66)
                                       Recheck Cond: ((fin_payment_id)::text = (fin_paymen1_.fin_payment_id)::text)
                                       -> Bitmap Index Scan on fin_pymt_detail_pymt_ixd (cost=0.00..4.60 rows=6 width=0)
                                             Index Cond: ((fin_payment_id)::text = (fin_paymen1_.fin_payment_id)::text)
                           -> Index Scan using fin_pymt_schdet_det_idx on fin_payment_scheduledetail fin_paymen3_ (cost=0.56..0.83 rows=1 width=66)
                                 Index Cond: ((fin_payment_detail_id)::text = (fin_paymen2_.fin_payment_detail_id)::text)
                     -> Index Only Scan using fin_payment_schedule_key on fin_payment_schedule fin_paymen4_ (cost=0.56..0.79 rows=1 width=33)
                           Index Cond: (fin_payment_schedule_id = (fin_paymen3_.fin_payment_schedule_invoice)::text)
         -> Index Scan using fin_payment_schedule_key on fin_payment_schedule ps (cost=0.56..0.81 rows=1 width=66)
               Index Cond: ((fin_payment_schedule_id)::text = (fin_paymen4_.fin_payment_schedule_id)::text)
*/
(0103340)
hgbot (developer)
2018-03-19 09:45

 Repository: erp/devel/pi
Changeset: e8edf135fc2a5fa1569a76682305f1afb14b4b4f
Author: Mark <markmm82 <at> gmail.com>
Date: Mon Mar 12 11:21:51 2018 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/e8edf135fc2a5fa1569a76682305f1afb14b4b4f [^]

Fixes issue 38045: Improved performance when executing the payment report

The query to retrieve the invoices using credit in getInvoicesUsingCredit method
was restructured to improve the performance, avoiding the join with the FIN_Payment_Detail_V
view and extracting the logic of it but without do some joins with not needed tables on
this scenario. Also the new query is using the defined indexes to execute the query
faster and decrease the cost of the execution.

---
M modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java
---
(0103341)
hgbot (developer)
2018-03-19 09:45

 Repository: erp/devel/pi
Changeset: 04567df3957aff391a3fbc07958152dad7ee3835
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Fri Mar 16 15:02:20 2018 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/04567df3957aff391a3fbc07958152dad7ee3835 [^]

Related to issue 38045: Code review improvements

Improve more the query.
Use setParameter method.

---
M modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java
---
(0103342)
aferraz (manager)
2018-03-19 10:02

 Code review + Testing OK
(0103351)
hudsonbot (developer)
2018-03-19 16:19

 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/07f42b4b2051 [^]
Maturity status: Test
(0103352)
hudsonbot (developer)
2018-03-19 16:19

 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/07f42b4b2051 [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2018-03-05 18:12 egoitz New Issue
2018-03-05 18:12 egoitz Assigned To => Triage Finance
2018-03-05 18:12 egoitz Modules => Core
2018-03-05 18:12 egoitz Resolution time => 1521068400
2018-03-05 18:12 egoitz Triggers an Emergency Pack => No
2018-03-05 21:06 markmm82 Assigned To Triage Finance => markmm82
2018-03-05 21:07 markmm82 Status new => scheduled
2018-03-07 19:18 markmm82 Note Added: 0103068
2018-03-12 16:06 markmm82 Note Edited: 0103068 View Revisions
2018-03-12 16:08 markmm82 Note Edited: 0103068 View Revisions
2018-03-16 16:40 markmm82 Note Edited: 0103068 View Revisions
2018-03-19 09:45 hgbot Checkin
2018-03-19 09:45 hgbot Note Added: 0103340
2018-03-19 09:45 hgbot Status scheduled => resolved
2018-03-19 09:45 hgbot Resolution open => fixed
2018-03-19 09:45 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/e8edf135fc2a5fa1569a76682305f1afb14b4b4f [^]
2018-03-19 09:45 hgbot Checkin
2018-03-19 09:45 hgbot Note Added: 0103341
2018-03-19 10:02 aferraz Review Assigned To => aferraz
2018-03-19 10:02 aferraz Note Added: 0103342
2018-03-19 10:02 aferraz Status resolved => closed
2018-03-19 10:02 aferraz Fixed in Version => 3.0PR18Q2
2018-03-19 16:19 hudsonbot Checkin
2018-03-19 16:19 hudsonbot Note Added: 0103351
2018-03-19 16:19 hudsonbot Checkin
2018-03-19 16:19 hudsonbot Note Added: 0103352

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker