Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0036312
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSmajorhave not tried2017-06-22 12:202017-06-27 09:03
ReportershuehnerView Statuspublic 
Assigned Tomigueldejuana 
PrioritynormalResolutionfixedFixed in VersionRR17Q3
StatusclosedFix in branchFixed in SCM revisionff89952823e0
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Merge Request Status
Review Assigned Tomarvintm
OBNetwork customerOBPS
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0036312: 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-
TagsPerformance
Attached Filesdiff file icon issue0036263retailConfig.diff [^] (706 bytes) 2017-06-22 17:01 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]
depends on backport 0036348RR17Q2.2 closedmigueldejuana Improve performance of ProducePrice masterdata loading 
depends on backport 0036349RR17Q1.3 closedmigueldejuana Improve performance of ProducePrice masterdata loading 
related to defect 0036327 closedmigueldejuana Api-change: Change obretco_prol_product_un unique constraint 
related to design defect 0037025 closedgorka_gil Perfromance problem in incremental refresh process, with product price query 

-  Notes
(0097662)
hgbot (developer)
2017-06-26 15:40

 Repository: retail/api-checks-retail
Changeset: 26ec4b14582916b26760f3e036de9f49ed9d4afd
Author: Miguel de Juana <miguel.dejuana <at> openbravo.com>
Date: Mon Jun 26 15:40:05 2017 +0200
URL: http://code.openbravo.com/retail/api-checks-retail/rev/26ec4b14582916b26760f3e036de9f49ed9d4afd [^]

Fixed issue 0036327: Api-change: Change obretco_prol_product_un unique constraint. Approved Api change. Related to issue 36312

---
M model/modules/org.openbravo.retail.config/src-db/database/model/tables/OBRETCO_PROL_PRODUCT.xml
---
(0097664)
hgbot (developer)
2017-06-26 15:40

 Repository: erp/pmods/org.openbravo.retail.config
Changeset: be79de65acb9009211857ae3eb0ac27ba47ba66a
Author: Miguel de Juana <miguel.dejuana <at> openbravo.com>
Date: Fri Jun 23 11:41:41 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.config/rev/be79de65acb9009211857ae3eb0ac27ba47ba66a [^]

Fixed issue 0036312: 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
---
(0097667)
hgbot (developer)
2017-06-26 15:41

 Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: ff89952823e0f4431bff9894ed6b4397cba1cd75
Author: Miguel de Juana <miguel.dejuana <at> openbravo.com>
Date: Fri Jun 23 11:39:27 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/ff89952823e0f4431bff9894ed6b4397cba1cd75 [^]

Fixed issue 0036312: 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
---

- Issue History
Date Modified Username Field Change
2017-06-22 12:20 shuehner New Issue
2017-06-22 12:20 shuehner Assigned To => Retail
2017-06-22 12:20 shuehner OBNetwork customer => Yes
2017-06-22 12:20 shuehner Resolution time => 1499896800
2017-06-22 12:20 shuehner Triggers an Emergency Pack => No
2017-06-22 16:45 migueldejuana Assigned To Retail => migueldejuana
2017-06-22 17:01 migueldejuana File Added: issue0036263retailConfig.diff
2017-06-23 14:21 migueldejuana Relationship added related to 0036327
2017-06-26 15:40 hgbot Checkin
2017-06-26 15:40 hgbot Note Added: 0097662
2017-06-26 15:40 hgbot Checkin
2017-06-26 15:40 hgbot Note Added: 0097664
2017-06-26 15:40 hgbot Status new => resolved
2017-06-26 15:40 hgbot Resolution open => fixed
2017-06-26 15:40 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.config/rev/be79de65acb9009211857ae3eb0ac27ba47ba66a [^]
2017-06-26 15:41 hgbot Checkin
2017-06-26 15:41 hgbot Note Added: 0097667
2017-06-26 15:41 hgbot Fixed in SCM revision http://code.openbravo.com/erp/pmods/org.openbravo.retail.config/rev/be79de65acb9009211857ae3eb0ac27ba47ba66a [^] => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/ff89952823e0f4431bff9894ed6b4397cba1cd75 [^]
2017-06-26 18:21 shuehner Tag Attached: Performance
2017-06-26 18:34 marvintm Status resolved => new
2017-06-26 18:34 marvintm Resolution fixed => open
2017-06-26 18:35 marvintm Status new => scheduled
2017-06-26 18:35 marvintm Status scheduled => resolved
2017-06-26 18:35 marvintm Fixed in Version => RR17Q3
2017-06-26 18:35 marvintm Resolution open => fixed
2017-06-27 09:03 marvintm Review Assigned To => marvintm
2017-06-27 09:03 marvintm Status resolved => closed
2017-10-06 14:57 malsasua Relationship added related to 0037025

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker