Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035221Retail ModulesWeb POSpublic2017-02-10 13:122017-02-20 12:18
Reporteraaroncalero 
Assigned Tomigueldejuana 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR17Q2 
Merge Request Status
Review Assigned Tomarvintm
OBNetwork customer
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035221: The order by clause on the CategoryTree master data query should be removed
DescriptionCurrently 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 ReproduceHere 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 SolutionRemoving 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.
Additional Information
TagsNo tags attached.
Relationships
related to defect 0035239 closed migueldejuana [SER-QA 2431] Complementary products does not uses *Remote products* preference 
Attached Filesdiff Issue35221.diff (2,114) 2017-02-10 13:19
https://issues.openbravo.com/file_download.php?file_id=10435&type=bug
Issue History
Date ModifiedUsernameFieldChange
2017-02-10 13:12aaroncaleroNew Issue
2017-02-10 13:12aaroncaleroAssigned To => Retail
2017-02-10 13:12aaroncaleroResolution time => 1487890800
2017-02-10 13:12aaroncaleroTriggers an Emergency Pack => No
2017-02-10 13:19aaroncaleroFile Added: Issue35221.diff
2017-02-10 13:19aaroncaleroProposed Solution updated
2017-02-13 12:28adrianromeroRelationship addedrelated to 0035239
2017-02-13 16:49migueldejuanaAssigned ToRetail => migueldejuana
2017-02-13 16:49migueldejuanaStatusnew => scheduled
2017-02-15 11:49hgbotCheckin
2017-02-15 11:49hgbotNote Added: 0094357
2017-02-15 11:49hgbotStatusscheduled => resolved
2017-02-15 11:49hgbotResolutionopen => fixed
2017-02-15 11:49hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/269f4c0e4ae871f8dd97ed34e7fe7b28af1f47a5 [^]
2017-02-20 12:18marvintmReview Assigned To => marvintm
2017-02-20 12:18marvintmStatusresolved => closed
2017-02-20 12:18marvintmFixed in Version => RR17Q2

Notes
(0094357)
hgbot   
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
---