Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0036349Retail ModulesWeb POSpublic2017-06-22 12:202017-08-02 13:07
Reportershuehner 
Assigned Tomigueldejuana 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionRR17Q1.3Fixed in Version 
Merge Request Status
Review Assigned Tomigueldejuana
OBNetwork customer
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0036349: Improve performance of ProducePrice masterdata loading
DescriptionMain query is from ProductPrice.java

current for access of obretco_prol_product it uses the auto-created index for the unique constraint:
    "obretco_prol_product_un" UNIQUE CONSTRAINT, btree (ad_client_id, m_product_id, obretco_productlist_id)

2 Changes should be done
a.) The part ad_client_id seems functionally useless. as no data can be created for this table in ad_client_id=0 and no normal uuid for products or assortment is possible to be re-used cross-client.
So extra adding ad_client_id does not change anything.

Note: Still api-change which should be done properly.

b.) That a.) the order of the remaining columns should be swapped to be
obretco_productlist_id, m_product_id

As the login query does filter by fixed value assortment_id

That change improved query time by ca. 40%
Example improvement seen:
Testing incremental one:
- without index: 5.3s
- with index 3.3s

Testing full one:
- without: 10s
- with index: 6.3s

Note: That order change makes it impossible to use the combined index for filtering by 'only' m_product_id. However this table already has another index for m_product_id which will be used.

Note:
Apart there is single column index on obretco_productlist_id which is functionally covered by the 'new' modified index from b.)
So it could be deleted.
However reading from smaller single column index is slightly faster then multi-column index -> So some testing is needed before decising if to keep or delete that index.



Apart the query has an extra ueless 2nd join to m_pricelist_version to get the m_pricelist_id column of the resultset. That property definition should be fixed to not required the extra join.
Steps To Reproduce-
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
blocks defect 0036312 closed migueldejuana Improve performance of ProducePrice masterdata loading 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2017-06-26 18:35marvintmTypedefect => backport
2017-06-26 18:35marvintmTarget Version => RR17Q1.3
2017-08-01 12:04hgbotCheckin
2017-08-01 12:04hgbotNote Added: 0098300
2017-08-01 12:04hgbotStatusscheduled => resolved
2017-08-01 12:04hgbotResolutionopen => fixed
2017-08-01 12:04hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/ff89952823e0f4431bff9894ed6b4397cba1cd75 [^] => http://code.openbravo.com/retail/backports/3.0RR17Q1.3/org.openbravo.retail.config/rev/a1566d74be3c5b0d790c5a4ba1fe364123b1b8e2 [^]
2017-08-01 12:05hgbotCheckin
2017-08-01 12:05hgbotNote Added: 0098301
2017-08-01 12:05hgbotFixed in SCM revisionhttp://code.openbravo.com/retail/backports/3.0RR17Q1.3/org.openbravo.retail.config/rev/a1566d74be3c5b0d790c5a4ba1fe364123b1b8e2 [^] => http://code.openbravo.com/retail/backports/3.0RR17Q1.3/org.openbravo.retail.posterminal/rev/ba0a9102972a7d1fe5a667412c9efc562dccb0dc [^]
2017-08-02 13:07migueldejuanaReview Assigned To => migueldejuana
2017-08-02 13:07migueldejuanaNote Added: 0098332
2017-08-02 13:07migueldejuanaStatusresolved => closed

Notes
(0098300)
hgbot   
2017-08-01 12:04   
Repository: retail/backports/3.0RR17Q1.3/org.openbravo.retail.config
Changeset: a1566d74be3c5b0d790c5a4ba1fe364123b1b8e2
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Tue Aug 01 12:04:37 2017 +0200
URL: http://code.openbravo.com/retail/backports/3.0RR17Q1.3/org.openbravo.retail.config/rev/a1566d74be3c5b0d790c5a4ba1fe364123b1b8e2 [^]

Fixed issue 36349. Improve performance of ProducePrice masterdata loading

- remove extra useless 2nd join to m_pricelist_version to get the m_pricelist_id column of the resultset.

---
M src-db/database/model/tables/OBRETCO_PROL_PRODUCT.xml
---
(0098301)
hgbot   
2017-08-01 12:05   
Repository: retail/backports/3.0RR17Q1.3/org.openbravo.retail.posterminal
Changeset: ba0a9102972a7d1fe5a667412c9efc562dccb0dc
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Tue Aug 01 12:04:04 2017 +0200
URL: http://code.openbravo.com/retail/backports/3.0RR17Q1.3/org.openbravo.retail.posterminal/rev/ba0a9102972a7d1fe5a667412c9efc562dccb0dc [^]

Fixed issue 36349. Improve performance of ProducePrice masterdata loading

- current for access of obretco_prol_product it uses the auto-created index for the unique constraint:
    obretco_prol_product_un UNIQUE CONSTRAINT, btree (ad_client_id, m_product_id, obretco_productlist_id)

The part ad_client_id seems functionally useless. as no data can be created for this table in ad_client_id=0 and no normal uuid for products or assortment is possible to be re-used cross-client.
So extra adding ad_client_id does not change anything.

---
M src/org/openbravo/retail/posterminal/master/ProductPriceProperties.java
---
(0098332)
migueldejuana   
2017-08-02 13:07   
Reviewed