Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0033118Retail ModulesWeb POSpublic2016-06-02 18:052017-11-30 09:31
Reportershuehner 
Assigned Toshuehner 
PriorityhighSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionRR16Q3Fixed in VersionRR16Q3 
Merge Request Status
Review Assigned Tomarvintm
OBNetwork customerNo
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0033118: 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
Additional Information
TagsPerformance
Relationships
depends on backport 0033119RR16Q2.1 closed Retail Cashup does sequential scan of c_order table which takes very long with many orders. 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2016-06-02 18:05shuehnerNew Issue
2016-06-02 18:05shuehnerAssigned To => Retail
2016-06-02 18:05shuehnerOBNetwork customer => No
2016-06-02 18:05shuehnerTriggers an Emergency Pack => No
2016-06-02 18:05shuehnerTag Attached: Performance
2016-06-02 18:06shuehnerNote Added: 0086955
2016-06-02 18:06shuehnerPrioritynormal => high
2016-06-02 18:11OrekariaStatusnew => scheduled
2016-06-02 18:11OrekariaStatusscheduled => acknowledged
2016-06-09 15:47hgbotCheckin
2016-06-09 15:47hgbotNote Added: 0087124
2016-06-09 15:47hgbotStatusacknowledged => resolved
2016-06-09 15:47hgbotResolutionopen => fixed
2016-06-09 15:47hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/8f8340d2f494d950011090315dd77feefd5c7cc1 [^]
2016-06-09 15:50shuehnerAssigned ToRetail => shuehner
2016-06-09 15:50shuehnerReview Assigned To => marvintm
2016-06-10 09:04marvintmStatusresolved => closed
2016-07-05 10:09hgbotCheckin
2016-07-05 10:09hgbotNote Added: 0088190
2016-07-05 10:09hgbotStatusclosed => resolved
2016-07-05 10:09hgbotFixed in SCM revisionhttp://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:12migueldejuanaStatusresolved => closed
2017-11-30 09:31shuehnerFixed in Version => RR16Q3

Notes
(0086955)
shuehner   
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   
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   
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
---