Openbravo Issue Tracking System - Retail Modules
View Issue Details
0036348Retail ModulesWeb POSpublic2017-06-22 12:202017-08-02 13:07
shuehner 
migueldejuana 
normalmajorhave not tried
closedfixed 
5
 
RR17Q2.2 
migueldejuana
No
0036348: Improve performance of ProducePrice masterdata loading
Main 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.
-
No tags attached.
blocks defect 0036312 closed migueldejuana Improve performance of ProducePrice masterdata loading 
Issue History
2017-06-26 18:35marvintmTypedefect => backport
2017-06-26 18:35marvintmTarget Version => RR17Q2.2
2017-08-01 12:08hgbotCheckin
2017-08-01 12:08hgbotNote Added: 0098302
2017-08-01 12:08hgbotStatusscheduled => resolved
2017-08-01 12:08hgbotResolutionopen => fixed
2017-08-01 12:08hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/ff89952823e0f4431bff9894ed6b4397cba1cd75 [^] => http://code.openbravo.com/retail/backports/3.0RR17Q2.2/org.openbravo.retail.posterminal/rev/a75573f403cff0f841b735396016b033a6f4baf9 [^]
2017-08-01 12:10hgbotCheckin
2017-08-01 12:10hgbotNote Added: 0098303
2017-08-01 12:10hgbotFixed in SCM revisionhttp://code.openbravo.com/retail/backports/3.0RR17Q2.2/org.openbravo.retail.posterminal/rev/a75573f403cff0f841b735396016b033a6f4baf9 [^] => http://code.openbravo.com/retail/backports/3.0RR17Q2.2/org.openbravo.retail.config/rev/fd82cc7f487a9405799d99b5a181aaf0904cb5db [^]
2017-08-02 13:07migueldejuanaReview Assigned To => migueldejuana
2017-08-02 13:07migueldejuanaNote Added: 0098333
2017-08-02 13:07migueldejuanaStatusresolved => closed

Notes
(0098302)
hgbot   
2017-08-01 12:08   
Repository: retail/backports/3.0RR17Q2.2/org.openbravo.retail.posterminal
Changeset: a75573f403cff0f841b735396016b033a6f4baf9
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Tue Aug 01 12:07:52 2017 +0200
URL: http://code.openbravo.com/retail/backports/3.0RR17Q2.2/org.openbravo.retail.posterminal/rev/a75573f403cff0f841b735396016b033a6f4baf9 [^]

Fixed issue 36348. 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/org/openbravo/retail/posterminal/master/ProductPriceProperties.java
---
(0098303)
hgbot   
2017-08-01 12:10   
Repository: retail/backports/3.0RR17Q2.2/org.openbravo.retail.config
Changeset: fd82cc7f487a9405799d99b5a181aaf0904cb5db
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Tue Aug 01 12:09:42 2017 +0200
URL: http://code.openbravo.com/retail/backports/3.0RR17Q2.2/org.openbravo.retail.config/rev/fd82cc7f487a9405799d99b5a181aaf0904cb5db [^]

Fixed issue 36348. 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-db/database/model/tables/OBRETCO_PROL_PRODUCT.xml
---
(0098333)
migueldejuana   
2017-08-02 13:07   
Reviewed