Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0044911Retail ModulesWeb POSpublic2020-08-27 15:052020-09-11 08:01
ReporterAugustoMauch 
Assigned Toranjith_qualiantech_com 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR20Q4 
Merge Request Status
Review Assigned To
OBNetwork customer
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0044911: Incremental refresh of Product cannot use index on updated
DescriptionThis is part of the query done to do an incremental refresh of products:

hql += "AND ((product.$incrementalUpdateCriteria) OR (pli.$incrementalUpdateCriteria) OR (ppp.$incrementalUpdateCriteria) OR (product.uOM.$incrementalUpdateCriteria))";

This means that a product will be returned if any of these have been modified:
- the product itself
- its entry in the assortment
- its entry in the price list
- its unit of measure

This is very complete, but it does not have good performance with big volumes, because the OR clauses prevents the use of indexes.

An index should be added to m_product.updated
Steps To ReproduceTo reproduce this just do an incremental refresh, and check the explain plan that results when executing the product query. I attached one to this issue
Proposed SolutionCreate a new preference that allows to check only the "updated" column of m_product when doing an incremental refresh.

Then each integration module should be in charge of making sure that the m_product.updated column is updated when needed
Additional Information
TagsNOR
Relationships
duplicate of defect 0045919 closed rqueralta Retail Modules Extra join of product table in master.product 
related to defect 0044590 closed ranjith_qualiantech_com Retail Modules Product tax category changes are not updated in webpos 
related to defect 0045055 closed ranjith_qualiantech_com Retail Modules Incremental refresh of ProductCharacteristic cannot use index on updated 
related to feature request 0045056 new Retail Retail Modules Faster insertion of big amounts of data in full refresh in indexedDB 
related to defect 0045144 closed prakashmurugesan88 Retail Modules Fix incremental and full refresh with Multi Tax Category module 
related to defect 0045187 closed ranjith_qualiantech_com Retail Modules Full Refresh fail: continues the load when it should stop 
related to defect 0045467 closed prakashmurugesan88 Retail Modules Performance issues on the model CharacteristicValue 
related to design defect 0045922 acknowledged Triage Platform Base Openbravo ERP Extra self-join with main table when using computed columns 
related to defect 0047423 closed ranjith_qualiantech_com Retail Modules Slow incremental refresh of products 
Attached Files? productIncrementalRefresh.sql (7,042) 2020-08-27 15:06
https://issues.openbravo.com/file_download.php?file_id=14905&type=bug
txt productIncrementalRefreshExplainPlan.txt (73,727) 2020-08-27 15:07
https://issues.openbravo.com/file_download.php?file_id=14906&type=bug
Issue History
Date ModifiedUsernameFieldChange
2020-08-27 15:05AugustoMauchNew Issue
2020-08-27 15:05AugustoMauchAssigned To => Retail
2020-08-27 15:05AugustoMauchTriggers an Emergency Pack => No
2020-08-27 15:06AugustoMauchResolution time => 1599688800
2020-08-27 15:06AugustoMauchFile Added: productIncrementalRefresh.sql
2020-08-27 15:07AugustoMauchFile Added: productIncrementalRefreshExplainPlan.txt
2020-08-27 15:08AugustoMauchDescription Updatedbug_revision_view_page.php?rev_id=21566#r21566
2020-08-28 09:50rafarodaTag Attached: NOR
2020-08-28 15:41rafarodaRelationship addedrelated to 0044590
2020-08-28 15:43rafarodaNote Added: 0122434
2020-09-04 04:55ranjith_qualiantech_comAssigned ToRetail => ranjith_qualiantech_com
2020-09-10 10:43ranjith_qualiantech_comStatusnew => scheduled
2020-09-11 07:34hgbotNote Added: 0122939
2020-09-11 08:01hgbotResolutionopen => fixed
2020-09-11 08:01hgbotStatusscheduled => closed
2020-09-11 08:01hgbotNote Added: 0122940
2020-09-11 08:01hgbotFixed in Version => RR20Q4
2020-09-11 08:01hgbotNote Added: 0122941
2020-09-15 14:56rafarodaRelationship addedrelated to 0045055
2020-09-15 14:59rafarodaRelationship addedrelated to 0045056
2020-09-25 12:22rafarodaRelationship addedrelated to 0045144
2020-10-05 11:37rafarodaRelationship addedrelated to 0045187
2021-02-22 09:34rafarodaRelationship addedrelated to 0045467
2021-02-22 10:47rafarodaRelationship addedduplicate of 0045919
2021-02-22 10:47rafarodaRelationship addedrelated to 0045922
2021-07-21 22:31rafarodaRelationship addedrelated to 0047423

Notes
(0122434)
rafaroda   
2020-08-28 15:43   
See comment on 0044590: triggers required in order to updated the M_PRODUCT.UPDATED column as it exists nowadays in Multi UPC table and Multiple Tax Category table
(0122939)
hgbot   
2020-09-11 07:34   
Merge Request created: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/merge_requests/94 [^]
(0122940)
hgbot   
2020-09-11 08:01   
Merge request merged: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/merge_requests/94 [^]
(0122941)
hgbot   
2020-09-11 08:01   
Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal [^]
Changeset: 0d22ec64d6071f68b79c8a25f4cf32314d6385b8
Author: Ranjith S R <ranjith@qualiantech.com>
Date: 2020-09-11T11:03:13+05:30
URL: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/commit/0d22ec64d6071f68b79c8a25f4cf32314d6385b8 [^]

Fixed ISSUE-44911: Filter MasterData Product by Related Entities on doing refresh

---
M src-db/database/sourcedata/AD_PREFERENCE.xml
M src-db/database/sourcedata/AD_REF_LIST.xml
M src/org/openbravo/retail/posterminal/master/Product.java
---