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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0035664
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 09. Financial managementmajorhave not tried2017-03-31 10:312017-05-29 17:43
ReporterSandrahuguetView Statuspublic 
Assigned Tomarkmm82 
PrioritynormalResolutionfixedFixed in Version3.0PR17Q3
StatusclosedFix in branchFixed in SCM revision5557f82495f4
ProjectionnoneETAnoneTarget Version
OSLinux 32 bitDatabasePostgreSQLJava version1.6.0_18
OS VersionCommunity ApplianceDatabase version8.3.9Ant version1.7.1
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

0035664: Performance of Add Multiple Payments P&E grid should be improved

DescriptionLoad payments P&E grid from Add multiple payments process takes around 30s in a hgvol enviroment.
Steps To Reproduce-Login in the ERP
-Go to Financial Account
-Select a financial account
-Click in Add multiple payments

Notice that load payments P&E grid takes around 30s in a hgvol enviroment
Proposed SolutionCREATE INDEX fin_payment_status
  ON public.fin_payment
  USING btree
  (status COLLATE pg_catalog."default");
TagsPerformance
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0035690 closedmarkmm82 Modules Performance of Select Payments P&E grid should be improved 
related to defect 0038551 closedAtulOpenbravo Openbravo ERP Improve performance of Add Multiple Payments process in Financial Account window 

-  Notes
(0095817)
markmm82 (developer)
2017-04-03 18:01

 QUERY
-----
EXPLAIN SELECT fin_paymen0_.Fin_Payment_ID as Fin1_206_, fin_paymen0_.AD_Client_ID as AD2_206_, fin_paymen0_.AD_Org_ID as AD3_206_,
       fin_paymen0_.Created as Created206_, fin_paymen0_.Createdby as Createdby206_, fin_paymen0_.Updated as Updated206_,
       fin_paymen0_.Updatedby as Updatedby206_, fin_paymen0_.Isactive as Isactive206_, fin_paymen0_.Isreceipt as Isreceipt206_,
       fin_paymen0_.C_Bpartner_ID as C10_206_, fin_paymen0_.Paymentdate as Payment11_206_, fin_paymen0_.C_Currency_ID as C12_206_,
       fin_paymen0_.Amount as Amount206_, fin_paymen0_.Writeoffamt as Writeof14_206_, fin_paymen0_.Fin_Paymentmethod_ID as Fin15_206_,
       fin_paymen0_.DocumentNo as DocumentNo206_, fin_paymen0_.Referenceno as Referen17_206_, fin_paymen0_.Status as Status206_,
       fin_paymen0_.Processed as Processed206_, fin_paymen0_.Processing as Processing206_, fin_paymen0_.Posted as Posted206_,
       fin_paymen0_.Description as Descrip22_206_, fin_paymen0_.Fin_Financial_Account_ID as Fin23_206_, fin_paymen0_.C_DocType_ID as C24_206_,
       fin_paymen0_.C_Project_ID as C25_206_, fin_paymen0_.C_Campaign_ID as C26_206_, fin_paymen0_.C_Activity_ID as C27_206_,
       fin_paymen0_.User1_ID as User28_206_, fin_paymen0_.User2_ID as User29_206_, fin_paymen0_.Generated_Credit as Generated30_206_,
       fin_paymen0_.Used_Credit as Used31_206_, fin_paymen0_.CreatedByAlgorithm as Created32_206_,
       fin_paymen0_.Finacc_Txn_Convert_Rate as Finacc33_206_, fin_paymen0_.Finacc_Txn_Amount as Finacc34_206_, fin_paymen0_.EM_APRM_Process_Payment as EM35_206_,
       fin_paymen0_.FIN_Rev_Payment_ID as FIN36_206_, fin_paymen0_.EM_APRM_Reconcile_Payment as EM37_206_, fin_paymen0_.EM_Aprm_Add_Scheduledpayments as EM38_206_,
       fin_paymen0_.EM_Aprm_Executepayment as EM39_206_, fin_paymen0_.EM_APRM_ReversePayment as EM40_206_, fin_paymen0_.C_Costcenter_ID as C41_206_
from FIN_Payment fin_paymen0_
where (
       fin_paymen0_.AD_Org_ID in ('0' , 'E443A31992CB4635AFCAEABE7183CE85' , 'B843C30461EA4501935CB1D125C9C25A' , 'DC206C91AA6A4897B44DA897936E0EC3' , '19404EAD144C49A0AF37D54377CF452D'))
       and (fin_paymen0_.Status in ('RPR' , 'PPM'))
       and fin_paymen0_.Amount<>0
       and (exists (select 1 from FIN_FinAcc_PaymentMethod financialm1_
                      inner join FIN_Financial_Account fin_financ2_ on financialm1_.FIN_Financial_Account_ID=fin_financ2_.Fin_Financial_Account_ID
                      cross join FIN_Financial_Account fin_financ3_
                    where fin_paymen0_.Fin_Financial_Account_ID=fin_financ3_.Fin_Financial_Account_ID
                      and financialm1_.FIN_Paymentmethod_ID=fin_paymen0_.Fin_Paymentmethod_ID
                      and fin_financ2_.Fin_Financial_Account_ID='DEDDE613C5314ACD8DCC60C474D1A107'
                      and case when fin_paymen0_.Isreceipt='Y' then financialm1_.Payin_Allow else financialm1_.Payout_Allow end='Y'
                      and fin_financ2_.C_Currency_ID=fin_financ3_.C_Currency_ID))
                      and not (exists (select 1 from FIN_Finacc_Transaction fin_finacc4_ where fin_paymen0_.Fin_Payment_ID=fin_finacc4_.Fin_Payment_ID))
                      and ad_isorgincluded(fin_paymen0_.AD_Org_ID, '0', fin_paymen0_.AD_Client_ID)<>-1
                      and fin_paymen0_.Fin_Financial_Account_ID='DEDDE613C5314ACD8DCC60C474D1A107' and (fin_paymen0_.AD_Client_ID in ('23C59575B9CF467C9620760EB255B389' , '0'))
                      and fin_paymen0_.Isactive='Y' order by fin_paymen0_.Paymentdate, fin_paymen0_.DocumentNo, fin_paymen0_.Fin_Payment_ID;

                      
ORIGINAL PLAN
---------------
Sort (cost=486.63..486.63 rows=1 width=936)
  Sort Key: fin_paymen0_.paymentdate, fin_paymen0_.documentno, fin_paymen0_.fin_payment_id
  -> Nested Loop Anti Join (cost=0.55..486.62 rows=1 width=936)
        -> Nested Loop Semi Join (cost=0.28..482.31 rows=1 width=936)
              Join Filter: (((fin_paymen0_.fin_paymentmethod_id)::text = (financialm1_.fin_paymentmethod_id)::text) AND (CASE WHEN (fin_paymen0_.isreceipt = 'Y'::bpchar) THEN financialm1_.payin_allow ELSE financialm1_.payout_allow END = 'Y'::bpchar))
              -> Seq Scan on fin_payment fin_paymen0_ (cost=0.00..462.25 rows=2 width=936)
                    Filter: (((status)::text = ANY ('{RPR,PPM}'::text[])) AND (amount <> 0::numeric) AND ((ad_client_id)::text = ANY ('{23C59575B9CF467C9620760EB255B389,0}'::text[])) AND (isactive = 'Y'::bpchar) AND ((fin_financial_account_id)::text = 'DED (...)
              -> Materialize (cost=0.28..20.02 rows=1 width=119)
                    -> Nested Loop (cost=0.28..20.02 rows=1 width=119)
                          -> Nested Loop (cost=0.28..16.32 rows=1 width=164)
                                Join Filter: ((fin_financ2_.c_currency_id)::text = (fin_financ3_.c_currency_id)::text)
                                -> Index Scan using fin_financial_account_key on fin_financial_account fin_financ2_ (cost=0.14..8.15 rows=1 width=164)
                                      Index Cond: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
                                -> Index Scan using fin_financial_account_key on fin_financial_account fin_financ3_ (cost=0.14..8.15 rows=1 width=164)
                                      Index Cond: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
                          -> Seq Scan on fin_finacc_paymentmethod financialm1_ (cost=0.00..3.67 rows=2 width=70)
                                Filter: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
        -> Index Only Scan using fin_finacc_transaction_un on fin_finacc_transaction fin_finacc4_ (cost=0.28..4.29 rows=1 width=33)
              Index Cond: (fin_payment_id = (fin_paymen0_.fin_payment_id)::text)


                      
PLAN WITH INDEX
---------------
Sort (cost=39.46..39.46 rows=1 width=936)
  Sort Key: fin_paymen0_.paymentdate, fin_paymen0_.documentno, fin_paymen0_.fin_payment_id
  -> Nested Loop Anti Join (cost=0.83..39.45 rows=1 width=936)
        -> Nested Loop Semi Join (cost=0.55..35.14 rows=1 width=936)
              Join Filter: (((fin_paymen0_.fin_paymentmethod_id)::text = (financialm1_.fin_paymentmethod_id)::text) AND (CASE WHEN (fin_paymen0_.isreceipt = 'Y'::bpchar) THEN financialm1_.payin_allow ELSE financialm1_.payout_allow END = 'Y'::bpchar))
              -> Index Scan using fin_payment_status on fin_payment fin_paymen0_ (cost=0.28..15.08 rows=2 width=936)
                    Index Cond: ((status)::text = ANY ('{RPR,PPM}'::text[]))
                    Filter: ((amount <> 0::numeric) AND ((ad_client_id)::text = ANY ('{23C59575B9CF467C9620760EB255B389,0}'::text[])) AND (isactive = 'Y'::bpchar) AND ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text) AND ((ad_or (...)
              -> Materialize (cost=0.28..20.02 rows=1 width=119)
                    -> Nested Loop (cost=0.28..20.02 rows=1 width=119)
                          -> Nested Loop (cost=0.28..16.32 rows=1 width=164)
                                Join Filter: ((fin_financ2_.c_currency_id)::text = (fin_financ3_.c_currency_id)::text)
                                -> Index Scan using fin_financial_account_key on fin_financial_account fin_financ2_ (cost=0.14..8.15 rows=1 width=164)
                                      Index Cond: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
                                -> Index Scan using fin_financial_account_key on fin_financial_account fin_financ3_ (cost=0.14..8.15 rows=1 width=164)
                                      Index Cond: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
                          -> Seq Scan on fin_finacc_paymentmethod financialm1_ (cost=0.00..3.67 rows=2 width=70)
                                Filter: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
        -> Index Only Scan using fin_finacc_transaction_un on fin_finacc_transaction fin_finacc4_ (cost=0.28..4.29 rows=1 width=33)
(0095878)
hgbot (developer)
2017-04-06 20:23

 Repository: erp/devel/pi
Changeset: 5557f82495f489adbc0d2673b1ab35bf26837b3e
Author: Mark <markmm82 <at> gmail.com>
Date: Mon Apr 03 10:47:01 2017 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/5557f82495f489adbc0d2673b1ab35bf26837b3e [^]

Fixes issue 35664: Improved performance of Add Multiple Payments P&E grid

Improved performance when load payments P&E grid from Add multiple payments process.
Added a new index on status column of fin_payment table to improve the load of
P&E grid.

---
M src-db/database/model/tables/FIN_PAYMENT.xml
---
(0095879)
aferraz (manager)
2017-04-06 20:26

 Code review + Testing OK
(0096869)
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-03-31 10:31 Sandrahuguet New Issue
2017-03-31 10:31 Sandrahuguet Assigned To => Triage Finance
2017-03-31 10:31 Sandrahuguet Modules => Core
2017-03-31 10:31 Sandrahuguet Triggers an Emergency Pack => No
2017-03-31 10:32 Sandrahuguet Tag Attached: Performance
2017-03-31 10:36 Sandrahuguet Steps to Reproduce Updated View Revisions
2017-03-31 11:44 Sandrahuguet Resolution time => 1492725600
2017-03-31 12:26 aferraz Assigned To Triage Finance => markmm82
2017-03-31 16:13 markmm82 Status new => acknowledged
2017-03-31 16:22 markmm82 Status acknowledged => scheduled
2017-04-03 18:01 markmm82 Note Added: 0095817
2017-04-04 17:03 Sandrahuguet Relationship added blocks 0035690
2017-04-04 17:03 Sandrahuguet Relationship deleted blocks 0035690
2017-04-04 17:03 Sandrahuguet Relationship added related to 0035690
2017-04-06 20:23 hgbot Checkin
2017-04-06 20:23 hgbot Note Added: 0095878
2017-04-06 20:23 hgbot Status scheduled => resolved
2017-04-06 20:23 hgbot Resolution open => fixed
2017-04-06 20:23 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/5557f82495f489adbc0d2673b1ab35bf26837b3e [^]
2017-04-06 20:26 aferraz Review Assigned To => aferraz
2017-04-06 20:26 aferraz Note Added: 0095879
2017-04-06 20:26 aferraz Status resolved => closed
2017-04-06 20:26 aferraz Fixed in Version => 3.0PR17Q3
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096869
2018-05-17 09:22 vmromanos Relationship added related to 0038551

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker