Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0033123Retail ModulesWeb POSpublic2016-06-02 18:192017-11-30 10:12
Reportershuehner 
Assigned Toshuehner 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR16Q3 
Merge Request Status
Review Assigned Tomarvintm
OBNetwork customer
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0033123: OrderLoader countPayments function uses very complex and potentially very slow query for simple check
Descriptionthe countPayments function in OrderLoader queries payments related to a c_order via the FIN_Payment_Detail_V and via the DAL filter for order even joins to a 2nd FIN_Payment_Sched_ord_V.

Checking view definition both views are very complex and heavy in terms of processing.

Checking the views + that the OrderLoader query only needs a count they coul dbe replaced by custom much simpler query only having 2-3 joins instead of the complex views querying over 10 tables.
Steps To Reproduce-
Proposed Solution
Additional Information
TagsPerformance
Relationships
related to defect 0033176 closed Sandrahuguet OrderLoader countPayments functions is called to often (once per payment) 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2016-06-02 18:19shuehnerNew Issue
2016-06-02 18:19shuehnerAssigned To => Retail
2016-06-02 18:19shuehnerTriggers an Emergency Pack => No
2016-06-02 18:19shuehnerTag Attached: Performance
2016-06-02 18:20shuehnerNote Added: 0086956
2016-06-07 12:23shuehnerNote Added: 0087057
2016-06-07 18:07shuehnerRelationship addedrelated to 0033176
2016-06-09 15:48hgbotCheckin
2016-06-09 15:48hgbotNote Added: 0087132
2016-06-09 15:48hgbotStatusnew => resolved
2016-06-09 15:48hgbotResolutionopen => fixed
2016-06-09 15:48hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/722755b9766c9d113a041b958bba8a8424540c7f [^]
2016-06-09 15:58shuehnerAssigned ToRetail => shuehner
2016-06-09 15:58shuehnerReview Assigned To => marvintm
2016-06-15 19:11marvintmStatusresolved => closed
2017-11-30 10:12shuehnerFixed in Version => RR16Q3

Notes
(0086956)
shuehner   
2016-06-02 18:20   
Probably a query as simple like the following could be replaced for the view usage:
explain SELECT count(*)
FROM fin_payment_detail pd
JOIN fin_payment_scheduledetail psd ON pd.fin_payment_detail_id = psd.fin_payment_detail_id
LEFT JOIN fin_payment_schedule pso ON psd.fin_payment_schedule_order = pso.fin_payment_schedule_id
WHERE pso.c_order_id= ?

Note: untested just given as a starting point here.
(0087057)
shuehner   
2016-06-07 12:23   
New minimal SQL which queries the correct condition wanted (after discussion SHU + AMO):

explain SELECT count(*)
FROM fin_payment_scheduledetail psd
LEFT JOIN fin_payment_schedule pso ON psd.fin_payment_schedule_order = pso.fin_payment_schedule_id
WHERE pso.c_order_id= ?
(0087132)
hgbot   
2016-06-09 15:48   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 722755b9766c9d113a041b958bba8a8424540c7f
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Tue Jun 07 18:48:02 2016 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/722755b9766c9d113a041b958bba8a8424540c7f [^]

Fixed 33123. Simplify SQL used in countPayments a lot.

That countPayments function used 2 very heavy view definition to do
a simple count of pamyents related to an Order.
This replaces this by a simpl 2 table join to avoid th other very heavy
processing happening inside the 2 views.

---
M src/org/openbravo/retail/posterminal/OrderLoader.java
---