Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035590Openbravo ERP09. Financial managementpublic2017-03-22 10:302017-05-29 17:43
Reportervmromanos 
Assigned Tovmromanos 
PrioritynormalSeveritymajorReproducibilityN/A
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR17Q3Fixed in Version3.0PR17Q3 
Merge Request Status
Review Assigned Toaferraz
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035590: AD_IsOrgIncluded performance improvements
DescriptionAD_IsOrgIncluded is a database function that receives three parameters: ad_org_id, parent_org_id and ad_client_id and returns the level in the organization tree for the given ad_client_id where ad_org_id is with respect to parent_org_id.
If not in the natural tree, it returns -1

This function is used in many places as part of the where clause specially in functional flow related SQL queries.


It would be very interesting to improve this function performance, so it will automatically improve the performance of every place that calls it.

http://wiki.openbravo.com/wiki/Projects:AD_IsOrgIncluded_Performance_Improvements [^]
Steps To ReproduceNA
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
depends on defect 0035595 closed vmromanos Functional API change after AD_IsOrgIncluded refactor 
related to defect 0035729 closed alostale Performance issue in Financial Account when having high number of Organizations 
blocks defect 0034676 closed AtulOpenbravo It is possible to see prices for list prices for organizations that you don't have access to. 
blocks defect 0036180 closed markmm82 Add Multiple payments pick and execute performance 
Attached Filesdiff 35590.diff (163,798) 2017-04-04 16:39
https://issues.openbravo.com/file_download.php?file_id=10649&type=bug
Issue History
Date ModifiedUsernameFieldChange
2017-03-22 10:30vmromanosNew Issue
2017-03-22 10:30vmromanosAssigned To => vmromanos
2017-03-22 10:30vmromanosModules => Core
2017-03-22 10:30vmromanosTriggers an Emergency Pack => No
2017-03-22 10:32vmromanosRelationship addedblocks 0034676
2017-03-22 11:36vmromanosRelationship addeddepends on 0035595
2017-03-22 14:18vmromanosFile Added: 35590_final.diff
2017-03-28 11:55vmromanosStatusnew => scheduled
2017-03-28 11:56vmromanosFile Deleted: 35590_final.diff
2017-03-28 11:56vmromanosFile Added: 35590.diff
2017-04-03 17:47vmromanosFile Deleted: 35590.diff
2017-04-03 17:47vmromanosFile Added: 35590.diff
2017-04-04 16:39vmromanosFile Deleted: 35590.diff
2017-04-04 16:39vmromanosFile Added: 35590.diff
2017-04-04 16:46vmromanosReview Assigned To => aferraz
2017-04-04 16:46vmromanosTarget Version => 3.0PR17Q3
2017-04-07 14:29aferrazDescription Updatedbug_revision_view_page.php?rev_id=14987#r14987
2017-04-10 14:15hgbotCheckin
2017-04-10 14:15hgbotNote Added: 0095936
2017-04-10 14:15hgbotStatusscheduled => resolved
2017-04-10 14:15hgbotResolutionopen => fixed
2017-04-10 14:15hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/b4fdc836924e3bc2738803e7039c50d7d39af47f [^]
2017-04-10 14:15hgbotCheckin
2017-04-10 14:15hgbotNote Added: 0095937
2017-04-10 14:15hgbotCheckin
2017-04-10 14:15hgbotNote Added: 0095938
2017-04-10 14:15hgbotCheckin
2017-04-10 14:15hgbotNote Added: 0095939
2017-04-10 14:15hgbotCheckin
2017-04-10 14:15hgbotNote Added: 0095940
2017-04-10 14:15hgbotCheckin
2017-04-10 14:15hgbotNote Added: 0095941
2017-04-10 14:16hgbotCheckin
2017-04-10 14:16hgbotNote Added: 0095942
2017-04-10 14:46aferrazRelationship addedrelated to 0035729
2017-04-10 14:52aferrazNote Added: 0095946
2017-04-10 14:52aferrazStatusresolved => closed
2017-04-10 14:52aferrazFixed in Version => 3.0PR17Q3
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096874
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096875
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096876
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096877
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096878
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096879
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096880
2017-06-07 12:21aferrazRelationship addedblocks 0036180

Notes
(0095936)
hgbot   
2017-04-10 14:15   
Repository: erp/devel/pi
Changeset: b4fdc836924e3bc2738803e7039c50d7d39af47f
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Wed Mar 22 14:16:46 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/b4fdc836924e3bc2738803e7039c50d7d39af47f [^]

Fixed issue 35590: AD_IsOrgIncluded performance improvements

Created AD_Org_Tree table to store the plain parent organization tree.
The unique constraint defined in this table is the key to get the performance improvement.
This table is populated when the organization is set as ready. In that moment we ensure its parent organization tree won’t change anymore in the future.

The AD_IsOrgIncluded function has been totally rewritten to read the AD_Org_Tree table, so there is no need to recursively iterate over the AD_TreeNode table anymore.
Important: after this changeset the AD_IsOrgIncluded function will only work for organizations set as ready!

A new AD_IsOrgIncluded_TreeNode is created as a copy of the previous AD_IsOrgIncluded code to be used only by the processes working with non ready organizations.
The Set as Ready process (AD_ORG_READY) has been updated to use this legacy function when required.

A new module script PopulateADOrgTree.java has been developed to populate the new table for existing instances.
Sampledata has been updated too.

A set of automatic tests have been created to ensure the function returns the same values after the refactor, and that we get a real performance improvement.
The improvement heavily depends on the organization tree structure (the more levels the better it will perform), parameters (the farther the passed orgs are the better it will perform) and query/table over the function is called from (the bigger table the better it will perform). In local testings we get improvements from 2,70x to 200x

---
M src-db/database/model/functions/AD_ISORGINCLUDED.xml
M src-db/database/model/functions/AD_ORG_CHK_CALENDAR.xml
M src-db/database/model/functions/AD_ORG_READY.xml
M src-db/database/sourcedata/AD_COLUMN.xml
M src-db/database/sourcedata/AD_DATASET_TABLE.xml
M src-db/database/sourcedata/AD_ELEMENT.xml
M src-db/database/sourcedata/AD_TABLE.xml
M src-test/src/org/openbravo/test/db/model/functions/Ad_isorgincludedTest.java
A referencedata/sampledata/F_B_International_Group/AD_ORG_TREE.xml
A referencedata/sampledata/QA_Testing/AD_ORG_TREE.xml
A src-db/database/model/functions/AD_ISORGINCLUDED_TREENODE.xml
A src-db/database/model/tables/AD_ORG_TREE.xml
A src-test/src/org/openbravo/test/IsOrgIncluded/ADOrgTreeTest.java
A src-util/modulescript/build/classes/org/openbravo/modulescript/PopulateADOrgTree.class
A src-util/modulescript/build/classes/org/openbravo/modulescript/PopulateADOrgTreeData.class
A src-util/modulescript/src/org/openbravo/modulescript/PopulateADOrgTree.java
A src-util/modulescript/src/org/openbravo/modulescript/PopulateADOrgTree_data.xsql
---
(0095937)
hgbot   
2017-04-10 14:15   
Repository: erp/devel/pi
Changeset: 74b6c9e2a2adcdc910234fba05818c3123ed5712
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Wed Mar 22 16:46:14 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/74b6c9e2a2adcdc910234fba05818c3123ed5712 [^]

Related to issue 35590: Added tests to AllAntTaskTests

---
M src-test/src/org/openbravo/test/AllAntTaskTests.java
---
(0095938)
hgbot   
2017-04-10 14:15   
Repository: erp/devel/pi
Changeset: d4bf9e7a0ac1f1a8cb1c40ac1160cd2e6d07d130
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Thu Mar 23 09:56:38 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/d4bf9e7a0ac1f1a8cb1c40ac1160cd2e6d07d130 [^]

Related to issue 35590: Added new testChildOrgValidation
Removed useless flush()

---
M src-test/src/org/openbravo/test/IsOrgIncluded/ADOrgTreeTest.java
---
(0095939)
hgbot   
2017-04-10 14:15   
Repository: erp/devel/pi
Changeset: 3c783e8b2bf36b34eff0da25e87268b4308a51f9
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Thu Mar 23 13:59:57 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/3c783e8b2bf36b34eff0da25e87268b4308a51f9 [^]

Related to issue 35590: Removed AD_ORG_TREE sampledata
and execute module script on install.source

This changeset should be reverted when creating sampledata for 3.0PR17Q3

---
M src-util/modulescript/build/classes/org/openbravo/modulescript/PopulateADOrgTree.class
M src-util/modulescript/src/org/openbravo/modulescript/PopulateADOrgTree.java
R referencedata/sampledata/F_B_International_Group/AD_ORG_TREE.xml
R referencedata/sampledata/QA_Testing/AD_ORG_TREE.xml
---
(0095940)
hgbot   
2017-04-10 14:15   
Repository: erp/devel/pi
Changeset: 40c88ac352df08171636b38f33885dfd3500b660
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Tue Mar 28 11:51:59 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/40c88ac352df08171636b38f33885dfd3500b660 [^]

Related to issue 35590: Added sampledata. Manage install.source in modulescript

Ensure the module script is executed on install.source to control the scenario where a sampledata module is installed.
The module script won't add duplicate lines to AD_Org_Tree in case of sampledata

---
M src-util/modulescript/build/classes/org/openbravo/modulescript/PopulateADOrgTree.class
M src-util/modulescript/build/classes/org/openbravo/modulescript/PopulateADOrgTreeData.class
M src-util/modulescript/src/org/openbravo/modulescript/PopulateADOrgTree.java
M src-util/modulescript/src/org/openbravo/modulescript/PopulateADOrgTree_data.xsql
A referencedata/sampledata/F_B_International_Group/AD_ORG_TREE.xml
A referencedata/sampledata/QA_Testing/AD_ORG_TREE.xml
---
(0095941)
hgbot   
2017-04-10 14:15   
Repository: erp/devel/pi
Changeset: 18c5039ab7628986464b5b8823af0fd2208595be
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Mon Apr 03 14:47:10 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/18c5039ab7628986464b5b8823af0fd2208595be [^]

Related to issue 35590: Applied code review feedback

Deactivated performance tests as they might create false positives in CI servers.
Removed info message in module script as the tests show the time to finish the script is good enough (around 10 minutes for 700 orgs)

---
M src-test/src/org/openbravo/test/IsOrgIncluded/ADOrgTreeTest.java
M src-util/modulescript/build/classes/org/openbravo/modulescript/PopulateADOrgTree.class
M src-util/modulescript/build/classes/org/openbravo/modulescript/PopulateADOrgTreeData.class
M src-util/modulescript/src/org/openbravo/modulescript/PopulateADOrgTree.java
---
(0095942)
hgbot   
2017-04-10 14:16   
Repository: erp/devel/pi
Changeset: 72f382b06664023c8ef7de1912215162edf56798
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Mon Apr 10 14:08:16 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/72f382b06664023c8ef7de1912215162edf56798 [^]

Related to issue 35590: Code review improvements

Update modulescript limits.
Apply code format in modulescript.
Move ADOrgTreeTest test to same package as Ad_isorgincludedTest test.

---
M src-test/src/org/openbravo/test/AllAntTaskTests.java
M src-util/modulescript/build/classes/org/openbravo/modulescript/PopulateADOrgTree.class
M src-util/modulescript/src/org/openbravo/modulescript/PopulateADOrgTree.java
A src-test/src/org/openbravo/test/db/model/functions/ADOrgTreeTest.java
R src-test/src/org/openbravo/test/IsOrgIncluded/ADOrgTreeTest.java
---
(0095946)
aferraz   
2017-04-10 14:52   
Code review OK
(0096874)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096875)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096876)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096877)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096878)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096879)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096880)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test