Project:
View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
ID | ||||||||
0036348 | ||||||||
Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
backport | [Retail Modules] Web POS | major | have not tried | 2017-06-22 12:20 | 2017-08-02 13:07 | |||
Reporter | shuehner | View Status | public | |||||
Assigned To | migueldejuana | |||||||
Priority | normal | Resolution | fixed | Fixed in Version | ||||
Status | closed | Fix in branch | Fixed in SCM revision | fd82cc7f487a | ||||
Projection | none | ETA | none | Target Version | RR17Q2.2 | |||
OS | Any | Database | Any | Java version | ||||
OS Version | Database version | Ant version | ||||||
Product Version | SCM revision | |||||||
Review Assigned To | migueldejuana | |||||||
Regression level | ||||||||
Regression date | ||||||||
Regression introduced in release | ||||||||
Regression introduced by commit | ||||||||
Triggers an Emergency Pack | No | |||||||
Summary | 0036348: Improve performance of ProducePrice masterdata loading | |||||||
Description | 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. | |||||||
Steps To Reproduce | - | |||||||
Tags | No tags attached. | |||||||
Attached Files | ||||||||
Relationships [ Relation Graph ] [ Dependency Graph ] | ||||||||
|
Notes | |
(0098302) hgbot (developer) 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 (developer) 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 (developer) 2017-08-02 13:07 |
Reviewed |
Copyright © 2000 - 2009 MantisBT Group |