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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0033118
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSmajorhave not tried2016-06-02 18:052017-11-30 09:31
ReportershuehnerView Statuspublic 
Assigned Toshuehner 
PriorityhighResolutionfixedFixed in VersionRR16Q3
StatusclosedFix in branchFixed in SCM revision3c581207edd5
ProjectionnoneETAnoneTarget VersionRR16Q3
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

0033118: Cashup does sequential scan of c_order table which takes very long with many orders.

DescriptionThe OrderGroupingProcess.java which is part of the cashup has 2 queries which filter c_order by cashup id (orders related to cashup being currently processes).

As the c_order table does not have an index for that cashup_id field this leads to 2 sequential scan's of the table which for high number or orders take very long.

In test database with only 4.3M c_order each of the 2 queries takes roughly 3.2 seconds because of that.

Adding index on that column reduces that time to a lot less (few hundred ms).

Queries are:
- OrderGroupingProcessorData.java:selectSplitOrderLines
- insertHeaderNoGrouping

Maybe also the grouping version of the 2nd
Steps To ReproduceFound by following sequence:

configure postgres db to log all queries i.e. slower than 1s

Take database with big number of c_order
configure to make invoice per order inside cashup.
Make i.e. 87 tickets with 2 lines each
Then process cashup.

Check pg log
Proposed Solutionadd index for cashup_id to c_order table
TagsPerformance
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
depends on backport 0033119RR16Q2.1 closedRetail Cashup does sequential scan of c_order table which takes very long with many orders. 

-  Notes
(0086955)
shuehner (administrator)
2016-06-02 18:06

Note: Final effect of that index drops cashup runtime from around 10seconds down to 2.2 seconds in my testcase.
(0087124)
hgbot (developer)
2016-06-09 15:47

Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 8f8340d2f494d950011090315dd77feefd5c7cc1
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Tue Jun 07 16:55:04 2016 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/8f8340d2f494d950011090315dd77feefd5c7cc1 [^]

Fixed 33118: Add index on c_order.em_obpos_app_cashup_id to speed up Cashup

That index will be used by at least 2 queries run in the cashup process which
without it do 2 sequential scan of c_order table which is very slow with
many orders.
Used by: OrderGroupingProcessorData:
- selectSplitOrderLines
- insertHeaderNoGrouping

---
M src-db/database/model/modifiedTables/C_ORDER.xml
---
(0088190)
hgbot (developer)
2016-07-05 10:09

Repository: retail/backports/3.0RR16Q2.1/org.openbravo.retail.posterminal
Changeset: 3c581207edd5792732aa61ee082c3103219d0ab5
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Tue Jun 07 16:55:04 2016 +0200
URL: http://code.openbravo.com/retail/backports/3.0RR16Q2.1/org.openbravo.retail.posterminal/rev/3c581207edd5792732aa61ee082c3103219d0ab5 [^]

Fixed 33118: Add index on c_order.em_obpos_app_cashup_id to speed up Cashup

That index will be used by at least 2 queries run in the cashup process which
without it do 2 sequential scan of c_order table which is very slow with
many orders.
Used by: OrderGroupingProcessorData:
- selectSplitOrderLines
- insertHeaderNoGrouping

---
M src-db/database/model/modifiedTables/C_ORDER.xml
---

- Issue History
Date Modified Username Field Change
2016-06-02 18:05 shuehner New Issue
2016-06-02 18:05 shuehner Assigned To => Retail
2016-06-02 18:05 shuehner Triggers an Emergency Pack => No
2016-06-02 18:05 shuehner Tag Attached: Performance
2016-06-02 18:06 shuehner Note Added: 0086955
2016-06-02 18:06 shuehner Priority normal => high
2016-06-02 18:11 Orekaria Status new => scheduled
2016-06-02 18:11 Orekaria Status scheduled => acknowledged
2016-06-09 15:47 hgbot Checkin
2016-06-09 15:47 hgbot Note Added: 0087124
2016-06-09 15:47 hgbot Status acknowledged => resolved
2016-06-09 15:47 hgbot Resolution open => fixed
2016-06-09 15:47 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/8f8340d2f494d950011090315dd77feefd5c7cc1 [^]
2016-06-09 15:50 shuehner Assigned To Retail => shuehner
2016-06-09 15:50 shuehner Review Assigned To => marvintm
2016-06-10 09:04 marvintm Status resolved => closed
2016-07-05 10:09 hgbot Checkin
2016-07-05 10:09 hgbot Note Added: 0088190
2016-07-05 10:09 hgbot Status closed => resolved
2016-07-05 10:09 hgbot Fixed in SCM revision http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/8f8340d2f494d950011090315dd77feefd5c7cc1 [^] => http://code.openbravo.com/retail/backports/3.0RR16Q2.1/org.openbravo.retail.posterminal/rev/3c581207edd5792732aa61ee082c3103219d0ab5 [^]
2016-07-05 10:12 migueldejuana Status resolved => closed
2017-11-30 09:31 shuehner Fixed in Version => RR16Q3


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker