Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0038045Openbravo ERP09. Financial managementpublic2018-03-05 18:122018-03-19 16:19
Reporteregoitz 
Assigned Tomarkmm82 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR18Q2 
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
Summary0038045: 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
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2018-03-05 18:12egoitzNew Issue
2018-03-05 18:12egoitzAssigned To => Triage Finance
2018-03-05 18:12egoitzModules => Core
2018-03-05 18:12egoitzResolution time => 1521068400
2018-03-05 18:12egoitzTriggers an Emergency Pack => No
2018-03-05 21:06markmm82Assigned ToTriage Finance => markmm82
2018-03-05 21:07markmm82Statusnew => scheduled
2018-03-07 19:18markmm82Note Added: 0103068
2018-03-12 16:06markmm82Note Edited: 0103068bug_revision_view_page.php?bugnote_id=0103068#r16827
2018-03-12 16:08markmm82Note Edited: 0103068bug_revision_view_page.php?bugnote_id=0103068#r16828
2018-03-16 16:40markmm82Note Edited: 0103068bug_revision_view_page.php?bugnote_id=0103068#r16870
2018-03-19 09:45hgbotCheckin
2018-03-19 09:45hgbotNote Added: 0103340
2018-03-19 09:45hgbotStatusscheduled => resolved
2018-03-19 09:45hgbotResolutionopen => fixed
2018-03-19 09:45hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/e8edf135fc2a5fa1569a76682305f1afb14b4b4f [^]
2018-03-19 09:45hgbotCheckin
2018-03-19 09:45hgbotNote Added: 0103341
2018-03-19 10:02aferrazReview Assigned To => aferraz
2018-03-19 10:02aferrazNote Added: 0103342
2018-03-19 10:02aferrazStatusresolved => closed
2018-03-19 10:02aferrazFixed in Version => 3.0PR18Q2
2018-03-19 16:19hudsonbotCheckin
2018-03-19 16:19hudsonbotNote Added: 0103351
2018-03-19 16:19hudsonbotCheckin
2018-03-19 16:19hudsonbotNote Added: 0103352

Notes
(0103068)
markmm82   
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   
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   
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   
2018-03-19 10:02   
Code review + Testing OK
(0103351)
hudsonbot   
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   
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