Openbravo Issue Tracking System - Retail Modules |
| View Issue Details |
|
| ID | Project | Category | View Status | Date Submitted | Last Update |
| 0045386 | Retail Modules | Web POS | public | 2020-11-05 17:05 | 2020-11-24 17:56 |
|
| Reporter | ALopetegui | |
| Assigned To | ranjith_qualiantech_com | |
| Priority | high | Severity | major | Reproducibility | have not tried |
| Status | closed | Resolution | no change required | |
| Platform | | OS | 5 | OS Version | |
| Product Version | RR20Q1.1 | |
| Target Version | | Fixed in Version | | |
| Merge Request Status | |
| Review Assigned To | marvintm |
| OBNetwork customer | |
| Support ticket | |
| 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.
|
| Additional Information | |
| Tags | Performance |
| Relationships | |
| Attached Files |  45386_20Q1_posterminal.diff (1,524) 2020-11-24 07:31
https://issues.openbravo.com/file_download.php?file_id=15118&type=bug |
|
| 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 |