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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0035221
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSmajoralways2017-02-10 13:122017-02-20 12:18
ReporteraaroncaleroView Statuspublic 
Assigned Tomigueldejuana 
PrioritynormalResolutionfixedFixed in VersionRR17Q2
StatusclosedFix in branchFixed in SCM revision269f4c0e4ae8
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tomarvintm
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0035221: 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.
TagsNo tags attached.
Attached Filesdiff file icon Issue35221.diff [^] (2,114 bytes) 2017-02-10 13:19 [Show Content]

- Relationships Relation Graph ] Dependency Graph ]
related to defect 0035239 closedmigueldejuana [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


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker