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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0035590
TypeCategorySeverityReproducibilityDate SubmittedLast Update
feature request[Openbravo ERP] 09. Financial managementmajorN/A2017-03-22 10:302017-05-29 17:43
ReportervmromanosView Statuspublic 
Assigned Tovmromanos 
PrioritynormalResolutionfixedFixed in Version3.0PR17Q3
StatusclosedFix in branchFixed in SCM revisionb4fdc836924e
ProjectionnoneETAnoneTarget Version3.0PR17Q3
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toaferraz
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0035590: 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
TagsNo tags attached.
Attached Filesdiff file icon 35590.diff [^] (163,798 bytes) 2017-04-04 16:39 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]
depends on defect 0035595 closedvmromanos Functional API change after AD_IsOrgIncluded refactor 
related to defect 0035729 closedalostale Performance issue in Financial Account when having high number of Organizations 
blocks defect 0034676 closedAtulOpenbravo It is possible to see prices for list prices for organizations that you don't have access to. 
blocks defect 0036180 closedmarkmm82 Add Multiple payments pick and execute performance 

-  Notes
(0095936)
hgbot (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (manager)
2017-04-10 14:52

 Code review OK
(0096874)
hudsonbot (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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

- Issue History
Date Modified Username Field Change
2017-03-22 10:30 vmromanos New Issue
2017-03-22 10:30 vmromanos Assigned To => vmromanos
2017-03-22 10:30 vmromanos Modules => Core
2017-03-22 10:30 vmromanos Triggers an Emergency Pack => No
2017-03-22 10:32 vmromanos Relationship added blocks 0034676
2017-03-22 11:36 vmromanos Relationship added depends on 0035595
2017-03-22 14:18 vmromanos File Added: 35590_final.diff
2017-03-28 11:55 vmromanos Status new => scheduled
2017-03-28 11:56 vmromanos File Deleted: 35590_final.diff
2017-03-28 11:56 vmromanos File Added: 35590.diff
2017-04-03 17:47 vmromanos File Deleted: 35590.diff
2017-04-03 17:47 vmromanos File Added: 35590.diff
2017-04-04 16:39 vmromanos File Deleted: 35590.diff
2017-04-04 16:39 vmromanos File Added: 35590.diff
2017-04-04 16:46 vmromanos Review Assigned To => aferraz
2017-04-04 16:46 vmromanos Target Version => 3.0PR17Q3
2017-04-07 14:29 aferraz Description Updated View Revisions
2017-04-10 14:15 hgbot Checkin
2017-04-10 14:15 hgbot Note Added: 0095936
2017-04-10 14:15 hgbot Status scheduled => resolved
2017-04-10 14:15 hgbot Resolution open => fixed
2017-04-10 14:15 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/b4fdc836924e3bc2738803e7039c50d7d39af47f [^]
2017-04-10 14:15 hgbot Checkin
2017-04-10 14:15 hgbot Note Added: 0095937
2017-04-10 14:15 hgbot Checkin
2017-04-10 14:15 hgbot Note Added: 0095938
2017-04-10 14:15 hgbot Checkin
2017-04-10 14:15 hgbot Note Added: 0095939
2017-04-10 14:15 hgbot Checkin
2017-04-10 14:15 hgbot Note Added: 0095940
2017-04-10 14:15 hgbot Checkin
2017-04-10 14:15 hgbot Note Added: 0095941
2017-04-10 14:16 hgbot Checkin
2017-04-10 14:16 hgbot Note Added: 0095942
2017-04-10 14:46 aferraz Relationship added related to 0035729
2017-04-10 14:52 aferraz Note Added: 0095946
2017-04-10 14:52 aferraz Status resolved => closed
2017-04-10 14:52 aferraz Fixed in Version => 3.0PR17Q3
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096874
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096875
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096876
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096877
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096878
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096879
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096880
2017-06-07 12:21 aferraz Relationship added blocks 0036180

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker