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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0036349
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Retail Modules] Web POSmajorhave not tried2017-06-22 12:202017-08-02 13:07
ReportershuehnerView Statuspublic 
Assigned Tomigueldejuana 
PrioritynormalResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revisionba0a9102972a
ProjectionnoneETAnoneTarget VersionRR17Q1.3
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tomigueldejuana
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0036349: 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-
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
blocks defect 0036312 closedmigueldejuana Improve performance of ProducePrice masterdata loading 

-  Notes
(0098300)
hgbot (developer)
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 (developer)
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 (developer)
2017-08-02 13:07

 Reviewed

- Issue History
Date Modified Username Field Change
2017-06-26 18:35 marvintm Type defect => backport
2017-06-26 18:35 marvintm Target Version => RR17Q1.3
2017-08-01 12:04 hgbot Checkin
2017-08-01 12:04 hgbot Note Added: 0098300
2017-08-01 12:04 hgbot Status scheduled => resolved
2017-08-01 12:04 hgbot Resolution open => fixed
2017-08-01 12:04 hgbot Fixed in SCM revision http://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:05 hgbot Checkin
2017-08-01 12:05 hgbot Note Added: 0098301
2017-08-01 12:05 hgbot Fixed in SCM revision http://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:07 migueldejuana Review Assigned To => migueldejuana
2017-08-02 13:07 migueldejuana Note Added: 0098332
2017-08-02 13:07 migueldejuana Status resolved => closed

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker