0035221: The order by clause on the CategoryTree master data query should be removed

Description

Currently the master data query to load Category Tree data in web pos is sorting the tree nodes by their sequenceNo.
In an environment with lots of categories (0001390:0001300), the order by is causing the query to take 17 seconds.

Steps To Reproduce

Here is an example explain plan of the query with the order by:
'Unique (cost=24033.48..24246.86 rows=14225 width=73) (actual time=16347.413..17130.579 rows=1338 loops=1)'
' -> Sort (cost=24033.48..24069.04 rows=14225 width=73) (actual time=16347.412..17010.405 rows=399911 loops=1)'
' Sort Key: adtreenode0_.seqno, adtreenode0_.ad_treenode_id, adtreenode0_.node_id, adtreenode0_.parent_id, obretco_pr1_.isactive'
' Sort Method: external merge Disk: 45328kB'
' -> Hash Join (cost=7657.21..23052.23 rows=14225 width=73) (actual time=2.047..219.759 rows=399911 loops=1)'
' Hash Cond: ((obretco_pr1_.m_product_category_id)::text = (adtreenode0_.node_id)::text)'
' -> Seq Scan on obretco_productcategory obretco_pr1_ (cost=0.00..13753.11 rows=399911 width=35) (actual time=0.004..50.847 rows=399911 loops=1)'
' -> Hash (cost=7572.93..7572.93 rows=6742 width=71) (actual time=2.029..2.029 rows=2312 loops=1)'
' Buckets: 1024 Batches: 1 Memory Usage: 307kB'
' -> Nested Loop (cost=1721.21..7572.93 rows=6742 width=71) (actual time=0.282..1.542 rows=2312 loops=1)'
' -> Seq Scan on ad_tree adtree2_ (cost=0.00..1.35 rows=1 width=82) (actual time=0.007..0.017 rows=1 loops=1)'
' Filter: ((ad_table_id)::text = '209'::text)'
' Rows Removed by Filter: 27'
' -> Bitmap Heap Scan on ad_treenode adtreenode0_ (cost=1721.21..7256.96 rows=31462 width=103) (actual time=0.272..1.231 rows=2312 loops=1)'
' Recheck Cond: ((ad_tree_id)::text = (adtree2_.ad_tree_id)::text)'
' Filter: (((ad_client_id)::text = ANY ('{9404134F8B374DEE92E79CD87B834E19,0}'::text[])) AND (isactive = 'Y'::bpchar) AND ((ad_org_id)::text = ANY ('{0,666F00005B054CE49CB29F2A6D53FB2A,E39FFB10775A41B388EE673ECD6BB8F1,5F1C1401 (...)'
' -> Bitmap Index Scan on ad_treenode_tree_node_un (cost=0.00..1713.35 rows=31590 width=0) (actual time=0.254..0.254 rows=2312 loops=1)'
' Index Cond: ((ad_tree_id)::text = (adtree2_.ad_tree_id)::text)'
'Total runtime: 17137.056 ms'

This explain plan can be seen in a more readable way on https://explain.depesz.com [^]

Proposed Solution

Removing the order by from the master data queries fixes the performance problem.
There is no functional change in web pos as the clien is still sorting by sequenceNo while building the tree.
Attached a diff with the proposed changes.

Tags

No tags attached.

Attached Files

Issue35221.diff [^] (2,114 bytes) 2017-02-10 13:19 [Show Content] [Hide Content]

diff --git a/src/org/openbravo/retail/posterminal/master/CategoryTree.java b/src/org/openbravo/retail/posterminal/master/CategoryTree.java
--- a/src/org/openbravo/retail/posterminal/master/CategoryTree.java
+++ b/src/org/openbravo/retail/posterminal/master/CategoryTree.java
@@ -103,8 +103,7 @@
               + regularProductsCategoriesTreeHQLProperties.getHqlSelect() //
               + "from ADTreeNode tn, OBRETCO_Productcategory pc "
               + "where tn.$incrementalUpdateCriteria and tn.$naturalOrgCriteria and tn.$readableSimpleClientCriteria "
-              + " and tn.node = pc.productCategory.id and tn.tree.table.id = :productCategoryTableId  "
-              + " order by tn.sequenceNumber");
+              + " and tn.node = pc.productCategory.id and tn.tree.table.id = :productCategoryTableId  ");
       hqlQueries
           .add("select"
               + regularProductsCategoriesTreeHQLProperties.getHqlSelect() //
@@ -112,16 +111,14 @@
               + "where tn.$incrementalUpdateCriteria and tn.$naturalOrgCriteria and tn.$readableSimpleClientCriteria "
               + " and tn.node = pc.id and tn.tree.table.id = :productCategoryTableId "
               + " and pc.summaryLevel = 'Y'"
-              + " and not exists (select obpc.id from OBRETCO_Productcategory obpc where tn.node = obpc.productCategory.id)"
-              + " order by tn.sequenceNumber");
+              + " and not exists (select obpc.id from OBRETCO_Productcategory obpc where tn.node = obpc.productCategory.id)");
     } else {
       hqlQueries
           .add("select"
               + regularProductsCategoriesTreeHQLProperties.getHqlSelect() //
               + "from ADTreeNode tn, ProductCategory pc "
               + "where tn.$incrementalUpdateCriteria and tn.$naturalOrgCriteria and tn.$readableSimpleClientCriteria "
-              + " and tn.node = pc.id and tn.tree.table.id = :productCategoryTableId "
-              + "order by tn.sequenceNumber");
+              + " and tn.node = pc.id and tn.tree.table.id = :productCategoryTableId ");
     }
 
     String whereClause = "p.client.id = '"

Relationships [ Relation Graph ] [ Dependency Graph ]

related to

defect

0035239

closed

migueldejuana

[SER-QA 2431] Complementary products does not uses *Remote products* preference

Notes
(0094357) hgbot (developer) 2017-02-15 11:49	Repository: erp/pmods/org.openbravo.retail.posterminal Changeset: 269f4c0e4ae871f8dd97ed34e7fe7b28af1f47a5 Author: Miguel de Juana <miguel.dejuana <at> openbravo.com> Date: Tue Feb 14 16:54:44 2017 +0100 URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/269f4c0e4ae871f8dd97ed34e7fe7b28af1f47a5 [^] Fixed issue 0035221: The order by clause on the CategoryTree master data query should be removed - Removing the order by from the master data queries fixes the performance problem. There is no functional change in web pos as the clien is still sorting by sequenceNo while building the tree. --- M src/org/openbravo/retail/posterminal/master/CategoryTree.java ---

Issue History
Date Modified	Username	Field	Change
2017-02-10 13:12	aaroncalero	New Issue
2017-02-10 13:12	aaroncalero	Assigned To	=> Retail
2017-02-10 13:12	aaroncalero	Resolution time	=> 1487890800
2017-02-10 13:12	aaroncalero	Triggers an Emergency Pack	=> No
2017-02-10 13:19	aaroncalero	File Added: Issue35221.diff
2017-02-10 13:19	aaroncalero	Proposed Solution updated
2017-02-13 12:28	adrianromero	Relationship added	related to 0035239
2017-02-13 16:49	migueldejuana	Assigned To	Retail => migueldejuana
2017-02-13 16:49	migueldejuana	Status	new => scheduled
2017-02-15 11:49	hgbot	Checkin
2017-02-15 11:49	hgbot	Note Added: 0094357
2017-02-15 11:49	hgbot	Status	scheduled => resolved
2017-02-15 11:49	hgbot	Resolution	open => fixed
2017-02-15 11:49	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/269f4c0e4ae871f8dd97ed34e7fe7b28af1f47a5 [^]
2017-02-20 12:18	marvintm	Review Assigned To	=> marvintm
2017-02-20 12:18	marvintm	Status	resolved => closed
2017-02-20 12:18	marvintm	Fixed in Version	=> RR17Q2