Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0042385Retail ModulesWeb POSpublic2019-11-25 11:022019-12-09 14:59
ReporterALopetegui 
Assigned Torqueralta 
PriorityimmediateSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR20Q1 
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
Summary0042385: Performance improvement in PaidReceipts query
DescriptionPerformance issue in PaidReceipts query, when loading payment method for the financial account related to the payments of the order.

The problem of the query is that first finds all the payment methods in all financial account of all the terminals, and then have a sub-select for payments of the order to filter it.

In Big customer the query takes 200ms ans is executed 30.000 times by day
Steps To ReproduceFind and load a Tickets in a database with high volumes
Proposed SolutionThe solution is to change the order of the query, first find payments of the order, and then only find those payment methods.

With this improvement the query takes 10 times less time, and reads 3 times less from disk or memory.

Attached, diff of solution.
Additional Information
TagsNo tags attached.
Relationships
depends on defect 0040876 closed rqueralta Multiple payment methods are shown when their have the same financial account 
related to defect 0037653 closed ranjith_qualiantech_com Performance problem in PaidReceipts 
Attached Filesdiff improvePerformancePaidReceipts_pi.diff (2,955) 2019-11-25 11:02
https://issues.openbravo.com/file_download.php?file_id=13579&type=bug
png 2019112610:01:56.png (65,116) 2019-11-26 10:02
https://issues.openbravo.com/file_download.php?file_id=13587&type=bug
png
Issue History
Date ModifiedUsernameFieldChange
2019-11-25 11:02ALopeteguiNew Issue
2019-11-25 11:02ALopeteguiAssigned To => Retail
2019-11-25 11:02ALopeteguiFile Added: improvePerformancePaidReceipts_pi.diff
2019-11-25 11:02ALopeteguiTriggers an Emergency Pack => No
2019-11-25 12:27guilleaerNote Added: 0115795
2019-11-25 12:27guilleaerNote Edited: 0115795bug_revision_view_page.php?bugnote_id=0115795#r19766
2019-11-26 10:01guilleaerNote Added: 0115817
2019-11-26 10:01guilleaerFile Added: 2019112609:52:57.png
2019-11-26 10:01guilleaerFile Deleted: 2019112609:52:57.png
2019-11-26 10:02guilleaerFile Added: 2019112610:01:56.png
2019-11-26 10:02guilleaerRelationship addeddepends on 0040876
2019-11-26 10:02guilleaerAssigned ToRetail => rqueralta
2019-11-27 13:41ALopeteguiNote Added: 0115883
2019-11-27 13:41ALopeteguiNote Edited: 0115883bug_revision_view_page.php?bugnote_id=0115883#r19793
2019-11-27 13:54ALopeteguiNote Added: 0115887
2019-12-06 20:34hgbotCheckin
2019-12-06 20:34hgbotNote Added: 0116129
2019-12-06 20:34hgbotStatusnew => resolved
2019-12-06 20:34hgbotResolutionopen => fixed
2019-12-06 20:34hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/344327c0b6d79a1d1ff3fd60879bc377d104936a [^]
2019-12-06 20:34hgbotCheckin
2019-12-06 20:34hgbotNote Added: 0116130
2019-12-07 08:11ranjith_qualiantech_comRelationship addedrelated to 0037653
2019-12-09 12:39hgbotCheckin
2019-12-09 12:39hgbotNote Added: 0116150
2019-12-09 14:59marvintmReview Assigned To => marvintm
2019-12-09 14:59marvintmStatusresolved => closed
2019-12-09 14:59marvintmFixed in Version => RR20Q1

Notes
(0115795)
guilleaer   
2019-11-25 12:27   
Patch reviewed by me. Now passing try
(0115817)
guilleaer   
2019-11-26 10:01   
After pass try one test failed

retail.pack.selenium.tests.cashmanagement.I40876_VerifyOnlyOnePayMethodShownIfSharesFinAcct [attached image with the problem]


It seems that the fix done for issue 40876 needs to be reapplied (in a correct way) to maintain the performance improvement but also to cover this case.
(0115883)
ALopetegui   
2019-11-27 13:41   
There are another two places where that hql code is repeated, So it's also necessary also to replace them:

https://code.openbravo.com/erp/pmods/org.openbravo.retail.digitalcoupons/file/tip/src/org/openbravo/retail/digitalcoupons/process/DigitalCouponsPaidReceiptsPaymentsTypeHook.java#l35 [^]

https://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/file/tip/src/org/openbravo/retail/posterminal/Invoices.java#l196Invoices.java [^]
(this is the same but for invoiceid instead of orderid)
(0115887)
ALopetegui   
2019-11-27 13:54   
The are two different results depending if the order has payments or not:

If yes:
Old query: 1ms -> https://explain.depesz.com/s/7S4Z [^]
New query: 22ms -> https://explain.depesz.com/s/i5zt [^]

If not:
Old Query: 200ms -> https://explain.depesz.com/s/Eg3oL [^]
New query: 19ms -> https://explain.depesz.com/s/P0s [^]

That happens because if there is no payments in the old query the heavy part is not executed, but if yes it's very slow.

However if we add a new index:
New query takes only 1ms in both cases -> https://explain.depesz.com/s/QBjS [^]

Proposed index:
create index obpos_app_fin_account on OBPOS_APP_PAYMENT (fin_financial_account_id);
(0116129)
hgbot   
2019-12-06 20:34   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 344327c0b6d79a1d1ff3fd60879bc377d104936a
Author: Guillermo Alvarez de Eulate <guillermo.alvarez <at> openbravo.com>
Date: Wed Dec 04 15:23:59 2019 -0500
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/344327c0b6d79a1d1ff3fd60879bc377d104936a [^]

Fixed issue 42385: Performance improvement in PaidReceipts query

 Improve performance in paid Receipt

---
M src/org/openbravo/retail/posterminal/PaidReceipts.java
---
(0116130)
hgbot   
2019-12-06 20:34   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 386b15cdffb4e30cb73c0182275eff8cabe25236
Author: Rafael Queralta <rafaelcuba81 <at> gmail.com>
Date: Thu Dec 05 16:43:31 2019 -0500
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/386b15cdffb4e30cb73c0182275eff8cabe25236 [^]

Related to issue 42385: Performance improvement in PaidReceipts query

- Readapted issue 40876 according to the solution of issue 42385 and added a new
index to improve the query

---
M src-db/database/model/tables/OBPOS_APP_PAYMENT.xml
M src/org/openbravo/retail/posterminal/Invoices.java
M src/org/openbravo/retail/posterminal/PaidReceipts.java
---
(0116150)
hgbot   
2019-12-09 12:39   
Repository: erp/pmods/org.openbravo.retail.digitalcoupons
Changeset: 147fe13c2ce22345182ee031450add2e5bbd83fa
Author: Rafael Queralta <rafaelcuba81 <at> gmail.com>
Date: Mon Dec 09 06:34:12 2019 -0500
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.digitalcoupons/rev/147fe13c2ce22345182ee031450add2e5bbd83fa [^]

Related to issue 42385: Performance improvement in PaidReceipts query

- Replaced the query of this module by the same query that was improved in
posterminal module

---
M src/org/openbravo/retail/digitalcoupons/process/DigitalCouponsPaidReceiptsPaymentsTypeHook.java
---