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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0045055
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSmajorhave not tried2020-09-15 14:562020-09-28 07:17
ReporterrafarodaView Statuspublic 
Assigned Toranjith_qualiantech_com 
PrioritynormalResolutionfixedFixed in VersionRR20Q4
StatusclosedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0045055: 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.
TagsNOR
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0044911 closedranjith_qualiantech_com Retail Modules Incremental refresh of Product cannot use index on updated 
related to defect 0044590 closedranjith_qualiantech_com Retail Modules Product tax category changes are not updated in webpos 
related to feature request 0045056 newRetail Retail Modules Faster insertion of big amounts of data in full refresh in indexedDB 
related to defect 0045144 closedprakashmurugesan88 Retail Modules Fix incremental and full refresh with Multi Tax Category module 
related to defect 0045187 closedranjith_qualiantech_com Retail Modules Full Refresh fail: continues the load when it should stop 
related to defect 0045467 closedprakashmurugesan88 Retail Modules Performance issues on the model CharacteristicValue 
related to defect 0045919 closedrqueralta Retail Modules Extra join of product table in master.product 
related to design defect 0045922 acknowledgedTriage Platform Base Openbravo ERP Extra self-join with main table when using computed columns 

-  Notes
(0123399)
hgbot (developer)
2020-09-28 04:06

 Merge Request created: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/merge_requests/108 [^]
(0123404)
hgbot (developer)
2020-09-28 07:17

 Merge request merged: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/merge_requests/108 [^]
(0123405)
hgbot (developer)
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
---

- Issue History
Date Modified Username Field Change
2020-09-15 14:56 rafaroda New Issue
2020-09-15 14:56 rafaroda Assigned To => Retail
2020-09-15 14:56 rafaroda Resolution time => 1598911200
2020-09-15 14:56 rafaroda Triggers an Emergency Pack => No
2020-09-15 14:56 rafaroda Relationship added related to 0044911
2020-09-15 14:56 rafaroda Tag Attached: NOR
2020-09-15 14:59 rafaroda Relationship added blocks 0045056
2020-09-15 15:11 rafaroda Relationship deleted blocks 0045056
2020-09-15 15:11 rafaroda Relationship added related to 0045056
2020-09-16 10:43 rafaroda Steps to Reproduce Updated View Revisions
2020-09-17 08:15 marvintm Resolution time 1598911200 => 1602021600
2020-09-23 06:17 marvintm Proposed Solution updated
2020-09-25 11:43 ranjith_qualiantech_com Assigned To Retail => ranjith_qualiantech_com
2020-09-25 12:13 rafaroda Relationship added related to 0044590
2020-09-25 12:22 rafaroda Relationship added related to 0045144
2020-09-27 16:51 ranjith_qualiantech_com Status new => scheduled
2020-09-28 04:06 hgbot Note Added: 0123399
2020-09-28 07:17 hgbot Resolution open => fixed
2020-09-28 07:17 hgbot Status scheduled => closed
2020-09-28 07:17 hgbot Note Added: 0123404
2020-09-28 07:17 hgbot Fixed in Version => RR20Q4
2020-09-28 07:17 hgbot Note Added: 0123405
2020-10-05 11:36 rafaroda Relationship added related to 0045187
2021-02-22 09:34 rafaroda Relationship added related to 0045467
2021-02-22 10:47 rafaroda Relationship added related to 0045919
2021-02-22 10:47 rafaroda Relationship added related to 0045922

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker