Anonymous | Login

Project:

News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ]

[ Issue History ] [ Print ]

ID

0045386

Type

Category

Severity

Reproducibility

Date Submitted

Last Update

defect

[Retail Modules] Web POS

major

have not tried

2020-11-05 17:05

2020-11-24 17:56

Reporter

ALopetegui

View Status

public

Assigned To

ranjith_qualiantech_com

Priority

high

Resolution

no change required

Fixed in Version

Status

closed

Fix in branch

Fixed in SCM revision

Projection

none

ETA

none

Target Version

OS

Any

Database

Any

Java version

OS Version

Database version

Ant version

Product Version

RR20Q1.1

SCM revision

Review Assigned To

marvintm

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

No

Summary

0045386: Category tree master data query generates big temporary files

Description

There 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 Reproduce

Having 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 Solution

Change 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.

Tags

45386_20Q1_posterminal.diff [^] (1,524 bytes) 2020-11-24 07:31 [Show Content] [Hide Content]

diff --git a/src/org/openbravo/retail/posterminal/master/CategoryTree.java b/src/org/openbravo/retail/posterminal/master/CategoryTree.java
index a1e09ac61..c3a89df2a 100644
--- a/src/org/openbravo/retail/posterminal/master/CategoryTree.java
+++ b/src/org/openbravo/retail/posterminal/master/CategoryTree.java
@@ -86,10 +86,9 @@ public class CategoryTree extends ProcessHQLQuery {
   private String getRegularProductCategoryTreeHqlString(
       final HQLPropertyList regularProductsCategoriesTreeHQLProperties, final Long lastUpdated) {
     final StringBuilder query = new StringBuilder();
-    query.append(" select distinct");
+    query.append(" select");
     query.append(regularProductsCategoriesTreeHQLProperties.getHqlSelect());
-    query.append(" from OBRETCO_Productcategory pCat");
-    query.append(" join pCat.productCategory pc");
+    query.append(" from ProductCategory pc");
     query.append(" , ADTreeNode tn");
     query.append(" where tn.$readableSimpleClientCriteria");
     query.append(" and tn.node = pc.id");
@@ -102,6 +101,9 @@ public class CategoryTree extends ProcessHQLQuery {
     query.append("   where o.id in :orgIds");
     query.append("   and ad_org_isinnaturaltree(tn.organization.id, o.id, tn.client.id) = 'Y'");
     query.append(" )");
+    query.append(" and exists (select 1 from OBRETCO_Productcategory pCat");
+    query.append("   where pCat.$readableSimpleClientCriteria ");
+    query.append("   and pCat.productCategory.id = pc.id)");
     return query.toString();
   }

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0124599) marvintm (manager) 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	Resolution time	=> 1605740400
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