Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0038790Retail ModulesWeb POSpublic2018-06-19 14:182018-06-22 14:47
Reportervmromanos 
Assigned Toranjith_qualiantech_com 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR18Q3 
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
Summary0038790: Redundant index in C_Order table
DescriptionC_Order table seems to have redundant indexes that could make it slower insert/update/delete actions.

Core adds the index:
<index name="C_ORDER_DATEORDERED_ID" unique="false">
   <index-column name="DATEORDERED"/>
   <index-column name="C_ORDER_ID"/>
</index>

org.openbravo.retail.posterminal adds the index:
<index name="EM_OBPOS_ORDERDATE" unique="false">
   <index-column name="DATEORDERED"/>
</index>


Both indexes actually are declared over the same DATEORDERED column.
Core's one is a multi-column index that adds C_ORDER_ID column. Regardless that difference, if a query filters by dateordered it will use any of the indexes.

Steps To ReproduceNA
Proposed SolutionFirst of all, for me it's clear that we can safely remove the EM_OBPOS_ORDERDATE index.

IMHO it's strange to index by the C_Order_ID column in C_ORDER_DATEORDERED_ID. It was introduced here: https://code.openbravo.com/erp/devel/pi/rev/b26f38d145ac [^]
I would propose to review this decision and remove the C_Order_ID column from the index to make it faster.
Anyway, the decision over the C_ORDER_DATEORDERED_ID index shouldn't affect the removal of EM_OBPOS_ORDERDATE index.
Additional Information
TagsPerformance
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2018-06-19 14:18vmromanosNew Issue
2018-06-19 14:18vmromanosAssigned To => Retail
2018-06-19 14:18vmromanosOBNetwork customer => No
2018-06-19 14:18vmromanosTriggers an Emergency Pack => No
2018-06-19 14:22vmromanosTag Attached: Performance
2018-06-19 14:22vmromanosProposed Solution updated
2018-06-19 16:09vmromanosNote Added: 0105260
2018-06-21 14:12ranjith_qualiantech_comAssigned ToRetail => ranjith_qualiantech_com
2018-06-21 14:12ranjith_qualiantech_comStatusnew => scheduled
2018-06-22 10:09hgbotCheckin
2018-06-22 10:09hgbotNote Added: 0105328
2018-06-22 10:09hgbotStatusscheduled => resolved
2018-06-22 10:09hgbotResolutionopen => fixed
2018-06-22 10:09hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/148bdaa50dd982c3f7378d2c192ade8163d79db0 [^]
2018-06-22 14:47marvintmReview Assigned To => marvintm
2018-06-22 14:47marvintmStatusresolved => closed
2018-06-22 14:47marvintmFixed in Version => RR18Q3

Notes
(0105260)
vmromanos   
2018-06-19 16:09   
About C_ORDER_DATEORDERED_ID: it has the C_Order_ID as part of the index because in Openbravo UI tabs, when we order by a column, we always append the record id to the order criteria to ensure we always get the same order. Having this multi-index improves the performance, so we must keep it as it is.
(0105328)
hgbot   
2018-06-22 10:09   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 148bdaa50dd982c3f7378d2c192ade8163d79db0
Author: Ranjith S R <ranjith <at> qualiantech.com>
Date: Fri Jun 22 13:39:46 2018 +0530
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/148bdaa50dd982c3f7378d2c192ade8163d79db0 [^]

Fixed issue 38790 : Removed index EM_OBPOS_ORDERDATE from c_order table duw to performance issues

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