Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0045055Retail ModulesWeb POSpublic2020-09-15 14:562020-09-28 07:17
Reporterrafaroda 
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
Summary0045055: Incremental refresh of ProductCharacteristic cannot use index on updated
DescriptionWith many records in Product Charachteristics Value table, the incremental refresh (and full refresh) are slow.
Steps To ReproduceTo reproduce this just do an incremental refresh, and check the explain plan that results when executing the productcharechteristics query

Query:

select productcha0_.M_Product_Ch_Value_ID as col_0_0_, product3_.M_Product_ID as col_1_0_, characteri1_.M_Characteristic_ID as col_2_0_, characteri1_.Name as col_3_0_, characteri1_.Isactive as col_4_0_, characteri1_.EM_Obpos_Filteronwebpos as col_5_0_, characteri2_.M_Ch_Value_ID as col_6_0_ from M_Product_Ch_Value productcha0_ inner join M_Characteristic characteri1_ on productcha0_.M_Characteristic_ID=characteri1_.M_Characteristic_ID inner join M_Ch_Value characteri2_ on productcha0_.M_Ch_Value_ID=characteri2_.M_Ch_Value_ID inner join M_Product product3_ on productcha0_.M_Product_ID=product3_.M_Product_ID inner join OBRETCO_Prol_Product obretcopro4_ on product3_.M_Product_ID=obretcopro4_.M_Product_ID inner join M_ProductPrice pricingpro5_ on product3_.M_Product_ID=pricingpro5_.M_Product_ID
where (productcha0_.AD_Client_ID in ($16 , $17))
and (characteri1_.M_Characteristic_ID in ($1 , $2 , $3 , $4 , $5)) and $18=$19 and $20=$21
and (productcha0_.AD_Org_ID in ($6 , $7 , $8 , $9 , $10))
and obretcopro4_.Obretco_Productlist_ID=$11 and pricingpro5_.M_PriceList_Version_ID=$12
and (obretcopro4_.Updated>$13 or product3_.Updated>$14)
and $22=$23 order by productcha0_.M_Product_Ch_Value_ID limit $15
Proposed SolutionSee similar case 0044911. The idea is to do the same thing:
- Reuse the same preference
- When the preference is set, replace this expression in the query:

and (obretcopro4_.Updated>$13 or product3_.Updated>$14)

by only the following filter:

and (product3_.Updated>$14)

This way the index can be used and the query will be very fast.
Additional Information
TagsNOR
Relationships
related to defect 0044911 closed ranjith_qualiantech_com Retail Modules Incremental refresh of Product cannot use index on updated 
related to defect 0044590 closed ranjith_qualiantech_com Retail Modules Product tax category changes are not updated in webpos 
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 defect 0045919 closed rqueralta Retail Modules Extra join of product table in master.product 
related to design defect 0045922 acknowledged Triage Platform Base Openbravo ERP Extra self-join with main table when using computed columns 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2020-09-15 14:56rafarodaNew Issue
2020-09-15 14:56rafarodaAssigned To => Retail
2020-09-15 14:56rafarodaResolution time => 1598911200
2020-09-15 14:56rafarodaTriggers an Emergency Pack => No
2020-09-15 14:56rafarodaRelationship addedrelated to 0044911
2020-09-15 14:56rafarodaTag Attached: NOR
2020-09-15 14:59rafarodaRelationship addedblocks 0045056
2020-09-15 15:11rafarodaRelationship deletedblocks 0045056
2020-09-15 15:11rafarodaRelationship addedrelated to 0045056
2020-09-16 10:43rafarodaSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=21690#r21690
2020-09-17 08:15marvintmResolution time1598911200 => 1602021600
2020-09-23 06:17marvintmProposed Solution updated
2020-09-25 11:43ranjith_qualiantech_comAssigned ToRetail => ranjith_qualiantech_com
2020-09-25 12:13rafarodaRelationship addedrelated to 0044590
2020-09-25 12:22rafarodaRelationship addedrelated to 0045144
2020-09-27 16:51ranjith_qualiantech_comStatusnew => scheduled
2020-09-28 04:06hgbotNote Added: 0123399
2020-09-28 07:17hgbotResolutionopen => fixed
2020-09-28 07:17hgbotStatusscheduled => closed
2020-09-28 07:17hgbotNote Added: 0123404
2020-09-28 07:17hgbotFixed in Version => RR20Q4
2020-09-28 07:17hgbotNote Added: 0123405
2020-10-05 11:36rafarodaRelationship addedrelated to 0045187
2021-02-22 09:34rafarodaRelationship addedrelated to 0045467
2021-02-22 10:47rafarodaRelationship addedrelated to 0045919
2021-02-22 10:47rafarodaRelationship addedrelated to 0045922

Notes
(0123399)
hgbot   
2020-09-28 04:06   
Merge Request created: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/merge_requests/108 [^]
(0123404)
hgbot   
2020-09-28 07:17   
Merge request merged: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/merge_requests/108 [^]
(0123405)
hgbot   
2020-09-28 07:17   
Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal [^]
Changeset: 1c4436722f17c6636a55acc67524062471eeb74f
Author: Ranjith S R <ranjith@qualiantech.com>
Date: 2020-09-28T07:35:04+05:30
URL: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/commit/1c4436722f17c6636a55acc67524062471eeb74f [^]

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

---
M src/org/openbravo/retail/posterminal/master/ProductCharacteristicValue.java
---