Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0053530Openbravo ERP09. Financial managementpublic2023-09-22 14:242023-11-22 06:15
Reportermalsasua 
Assigned Tojonae 
PriorityhighSeveritymajorReproducibilityalways
StatusnewResolutionopen 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0053530: 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
@joinClause@
where @additional_filters@
and @whereClause@
group by @groupByClause@
@havingClause@

is executed
Additional Information
TagsNo tags attached.
Relationships
Attached Filestxt sql_query.txt (6,715) 2023-10-05 09:35
https://issues.openbravo.com/file_download.php?file_id=19017&type=bug
Issue History
Date ModifiedUsernameFieldChange
2023-09-22 14:24malsasuaNew Issue
2023-09-22 14:24malsasuaAssigned To => Triage Omni OMS
2023-09-22 14:24malsasuaModules => Core
2023-09-22 14:24malsasuaTriggers an Emergency Pack => No
2023-10-04 10:38aferrazAssigned ToTriage Omni OMS => jonae
2023-10-05 09:35malsasuaFile Added: sql_query.txt
2023-11-22 06:15jonaeNote Added: 0157439
2023-11-22 06:15jonaeTypedefect => design defect

Notes
(0157439)
jonae   
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')