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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0045386
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSmajorhave not tried2020-11-05 17:052020-11-24 17:56
ReporterALopeteguiView Statuspublic 
Assigned Toranjith_qualiantech_com 
PriorityhighResolutionno change requiredFixed in Version
StatusclosedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionRR20Q1.1SCM revision 
Review Assigned Tomarvintm
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0045386: Category tree master data query generates big temporary files

DescriptionThere is a query in CategoryTree.java, in getRegularProductCategoryTreeHqlString frunction which is creating a big amount of temporary files due to bad performance.

The query created is joining obretco_productcategory with m_product_category, and this join has 500.000 rows in one client, so in order to finaly give unique product_category nodes, it makes a distict. Here to sort the 5000.000 rows postgres needs to generate temporary files because it dowsnt fit in work_mem memory.

Here the query:

SELECT DISTINCT adtreenode2_.ad_treenode_id AS col_0_0_,
                adtreenode2_.node_id AS col_1_0_,
                adtreenode2_.parent_id AS col_2_0_,
                adtreenode2_.seqno AS col_3_0_,
                productcat1_.isactive AS col_4_0_
FROM obretco_productcategory obretco_pr0_
INNER JOIN m_product_category productcat1_
ON obretco_pr0_.m_product_category_id=productcat1_.m_product_category_id
CROSS JOIN ad_treenode adtreenode2_
CROSS JOIN ad_tree adtree3_
WHERE adtreenode2_.ad_tree_id=adtree3_.ad_tree_id
AND (adtreenode2_.ad_client_id IN ('9404134F8B374DEE92E79CD87B834E19' ,'0'))
AND adtreenode2_.node_id=productcat1_.m_product_category_id
AND adtree3_.ad_table_id='209'
AND adtreenode2_.isactive='Y'
AND productcat1_.isactive='Y'
AND (
                EXISTS
                      (
                      SELECT 1
                      FROM ad_org organizati4_
                      WHERE (organizati4_.ad_org_id IN ('30A7B8D6917741C6BC835E2E56284B22'))
                      AND Ad_org_isinnaturaltree(adtreenode2_.ad_org_id, organizati4_.ad_org_id, adtreenode2_.ad_client_id)='Y'))
limit '50000'

And here the explain plan: (you can see-> Sort Method: external merge Disk: 54,704kB)
https://explain.depesz.com/s/vEP9 [^]
Steps To ReproduceHaving big amount of m_product_category and obretco_productcategory launch the query. In the case of one custemor has 3.5000 m_product_category and 450.000 obretco_productcategory
Proposed SolutionChange the query in order not to join m_product_category with obretco_productcategory, insted only checking if exists. As the query only seems to join with obretco_productcategory to get those product categories which are related added in an assortment, but finally only gets unique nodes of product categories, just we can remove join at the beginning and put and exists, this way the final amount of categories are filtered at the beginning and does not need to make a DISTINCT.

Proposed Query:

SELECT adtreenode2_.ad_treenode_id AS col_0_0_,
                adtreenode2_.node_id AS col_1_0_,
                adtreenode2_.parent_id AS col_2_0_,
                adtreenode2_.seqno AS col_3_0_,
                productcat1_.isactive AS col_4_0_
FROM m_product_category productcat1_
CROSS JOIN ad_treenode adtreenode2_
CROSS JOIN ad_tree adtree3_
WHERE adtreenode2_.ad_tree_id=adtree3_.ad_tree_id
AND (adtreenode2_.ad_client_id IN ('9404134F8B374DEE92E79CD87B834E19' ,'0'))
AND adtreenode2_.node_id=productcat1_.m_product_category_id
AND adtree3_.ad_table_id='209'
AND adtreenode2_.isactive='Y'
AND productcat1_.isactive='Y'
AND (
                EXISTS
                      (
                      SELECT 1
                      FROM ad_org organizati4_
                      WHERE (organizati4_.ad_org_id IN ('30A7B8D6917741C6BC835E2E56284B22'))
                      AND Ad_org_isinnaturaltree(adtreenode2_.ad_org_id, organizati4_.ad_org_id, adtreenode2_.ad_client_id)='Y'))
AND EXISTS(
                     SELECT 1 FROM obretco_productcategory obretco_pr0_
                     WHERE obretco_pr0_.m_product_category_id=productcat1_.m_product_category_id)
limit '50000'


Explain plan: https://explain.depesz.com/s/c78f [^]
There is no disk usage and the result of the query is the same.
TagsPerformance
Attached Filesdiff file icon 45386_20Q1_posterminal.diff [^] (1,524 bytes) 2020-11-24 07:31 [Show Content]

- Relationships Relation Graph ] Dependency Graph ]

-  Notes
(0124599)
marvintm (developer)
2020-11-24 17:56

This query was removed in more recent releases and no change is required in pi.

We have attached a patch that could be used in 20Q1.

- Issue History
Date Modified Username Field Change
2020-11-05 17:05 ALopetegui New Issue
2020-11-05 17:05 ALopetegui Assigned To => Retail
2020-11-05 17:05 ALopetegui Triggers an Emergency Pack => No
2020-11-05 17:10 ALopetegui Tag Attached: Performance
2020-11-10 09:20 ALopetegui Priority normal => high
2020-11-10 09:20 ALopetegui Severity minor => major
2020-11-23 06:42 ranjith_qualiantech_com Assigned To Retail => ranjith_qualiantech_com
2020-11-23 06:44 ranjith_qualiantech_com Status new => scheduled
2020-11-24 02:08 ranjith_qualiantech_com File Added: 45386_procurement.diff
2020-11-24 07:07 ranjith_qualiantech_com File Deleted: 45386_procurement.diff
2020-11-24 07:31 ranjith_qualiantech_com File Added: 45386_20Q1_posterminal.diff
2020-11-24 17:50 marvintm Review Assigned To => marvintm
2020-11-24 17:50 marvintm Note Added: 0124598
2020-11-24 17:50 marvintm Status scheduled => closed
2020-11-24 17:50 marvintm Resolution open => no change required
2020-11-24 17:51 marvintm Status closed => new
2020-11-24 17:51 marvintm Resolution no change required => open
2020-11-24 17:51 marvintm Note Deleted: 0124598
2020-11-24 17:56 marvintm Note Added: 0124599
2020-11-24 17:56 marvintm Status new => closed
2020-11-24 17:56 marvintm Resolution open => no change required


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker