Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0033119Retail ModulesWeb POSpublic2016-06-02 18:052016-07-05 18:45
Reportershuehner 
Assigned ToRetail 
PriorityhighSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionRR16Q2.1Fixed in Version 
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
Summary0033119: 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
TagsNo tags attached.
Relationships
blocks defect 0033118RR16Q3 closed shuehner 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:11OrekariaTypedefect => backport
2016-06-02 18:11OrekariaTarget VersionRR16Q3 => RR16Q2
2016-06-03 15:03marvintmTarget VersionRR16Q2 => RR16Q2.1
2016-07-05 10:12migueldejuanaNote Added: 0088196
2016-07-05 10:12migueldejuanaStatusscheduled => resolved
2016-07-05 10:12migueldejuanaResolutionopen => fixed
2016-07-05 18:45marvintmReview Assigned To => marvintm
2016-07-05 18:45marvintmStatusresolved => closed

Notes
(0088196)
migueldejuana   
2016-07-05 10:12   
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
---