Anonymous | Login
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
TypeCategorySeverityReproducibilityDate SubmittedLast Update
design defect[Openbravo ERP] 09. Financial managementmajoralways2023-09-22 14:242023-11-22 06:15
ReportermalsasuaView Statuspublic 
Assigned Tojonae 
PriorityhighResolutionopenFixed in Version
StatusnewFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Web browser
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo

0053530: Bad performance with query executed in "Add Payment" process

DescriptionIn a customer with a high volume, the query used in Add Payment process, the response time is high
Steps To Reproduce. go to Sales Invoice window
. click in "Payment In" button
the query takes a long time to execute
Proposed SolutionIn aprm_orderinvoice table:
HQL Query:
select @selectClause@
from FIN_Payment_ScheduleDetail as psd
join psd.businessPartner as bp
left outer join psd.orderPaymentSchedule as ops
left outer join ops.order as ord @joinClauseOrder@
left outer join ops.fINPaymentPriority as opriority
left outer join ops.finPaymentmethod opsfp
left outer join psd.invoicePaymentSchedule as ips
left outer join ips.invoice as inv @joinClauseInvoice@
left outer join ips.fINPaymentPriority as ipriority
left outer join ips.finPaymentmethod as ipsfp
left outer join psd.organization as org
left outer join org.organizationInformationList as oinfo
left outer join psd.paymentDetails as pd
left outer join pd.finPayment as fp
where @additional_filters@
and @whereClause@
group by @groupByClause@

is executed
TagsNo tags attached.
Attached Filestxt file icon sql_query.txt [^] (6,715 bytes) 2023-10-05 09:35 [Show Content]

- Relationships Relation Graph ] Dependency Graph ]

-  Notes
jonae (developer)
2023-11-22 06:15

After analyzing the queries involved in this topic, several considerations:
- There are two custom indexes over the table fin_payment_scheduledetail afecting the query. Both indexes need to be defined in a different way, in order to be more efficient. In this case, the indexes must be defined with the more restrictive column in the first position. The queries to define properly the indexes are:
CREATE INDEX IF NOT EXISTS em_fe_psd_org_bp_cli_idx
    ON public.fin_payment_scheduledetail USING btree
    (c_bpartner_id COLLATE pg_catalog."default" ASC NULLS LAST, ad_org_id COLLATE pg_catalog."default" ASC NULLS LAST, ad_client_id COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS em_rlcfn21_psd_orgpartner
    ON public.fin_payment_scheduledetail USING btree
    (c_bpartner_id COLLATE pg_catalog."default" ASC NULLS LAST, ad_org_id COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

With this new definition the reduction in the execution is from 190 seconds to 120 seconds.
- To get a bigger reduction, a change in the query needs to be performed. There is an OR condition included in the query that can be moved to a union of both queries that reduces the execution time to 40 seconds. But, this is a change in the structure of the query that has to be managed as a design defect, that's why this defect has been moved to design defect.
The or clause to change is:
AND (fin_paymen0_.FIN_Payment_Detail_ID IS NULL OR fin_paymen13_.Fin_Payment_ID = 'BDB8C52C6C684C64B2FC3F9169CC5B83')

- Issue History
Date Modified Username Field Change
2023-09-22 14:24 malsasua New Issue
2023-09-22 14:24 malsasua Assigned To => Triage Omni OMS
2023-09-22 14:24 malsasua Modules => Core
2023-09-22 14:24 malsasua Triggers an Emergency Pack => No
2023-10-04 10:38 aferraz Assigned To Triage Omni OMS => jonae
2023-10-05 09:35 malsasua File Added: sql_query.txt
2023-11-22 06:15 jonae Note Added: 0157439
2023-11-22 06:15 jonae Type defect => design defect

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker