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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0033123
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSminorhave not tried2016-06-02 18:192017-11-30 10:12
ReportershuehnerView Statuspublic 
Assigned Toshuehner 
PrioritynormalResolutionfixedFixed in VersionRR16Q3
StatusclosedFix in branchFixed in SCM revision722755b9766c
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tomarvintm
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0033123: 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-
TagsPerformance
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
related to defect 0033176 closedSandrahuguet OrderLoader countPayments functions is called to often (once per payment) 

-  Notes
(0086956)
shuehner (administrator)
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 (administrator)
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 (developer)
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
---

- Issue History
Date Modified Username Field Change
2016-06-02 18:19 shuehner New Issue
2016-06-02 18:19 shuehner Assigned To => Retail
2016-06-02 18:19 shuehner Triggers an Emergency Pack => No
2016-06-02 18:19 shuehner Tag Attached: Performance
2016-06-02 18:20 shuehner Note Added: 0086956
2016-06-07 12:23 shuehner Note Added: 0087057
2016-06-07 18:07 shuehner Relationship added related to 0033176
2016-06-09 15:48 hgbot Checkin
2016-06-09 15:48 hgbot Note Added: 0087132
2016-06-09 15:48 hgbot Status new => resolved
2016-06-09 15:48 hgbot Resolution open => fixed
2016-06-09 15:48 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/722755b9766c9d113a041b958bba8a8424540c7f [^]
2016-06-09 15:58 shuehner Assigned To Retail => shuehner
2016-06-09 15:58 shuehner Review Assigned To => marvintm
2016-06-15 19:11 marvintm Status resolved => closed
2017-11-30 10:12 shuehner Fixed in Version => RR16Q3


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker