Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0033602Openbravo ERP09. Financial managementpublic2016-08-02 15:362016-08-02 15:51
Reportershuehner 
Assigned ToTriage Omni OMS 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusnewResolutionopen 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0033602: LoginUtils.fillSessionArguments (query Attribute.selectOrgCurrency quite slow) around 100ms with many orgs
DescriptionIn bigger db with 524 organization the query from selectOrgCurrency can be quite slow with runtime around 100ms

Problem is that it runs the ad_isorgincluded for all organizations whose runtime sums up a lot.

Looking at the query it seems that only values from ad_org which is input and any parent orgs are of interest.

Assuming that is correct adding a filter of the ad_org id's of all parents can speed up the query a lot.

As the caller in fillSessionArguments did already construct the OrgTree that parent info is easily accessible.

Note: Had to open up access to getAscendantTree from private to package level access to use it (note before+after still not part of public api so change should be fine).

Attached patch seems to work given the assumption mentioned above is correct. But only very lightly tested.
Steps To Reproduce-
Proposed Solution
Additional Information
TagsPerformance
Relationships
related to feature request 0033650 closed AtulOpenbravo Add columns in AD_Org table to persist data in order to improve performance 
related to defect 0033605 closed shuehner OrgTree.getAccessibleTree is very slow with many organizations 
related to design defect 0035989 acknowledged Triage Platform Base Default session values calculation on login is slow having a big amount of organizations 
Attached Filesdiff 33602_prototype.diff (2,137) 2016-08-02 15:37
https://issues.openbravo.com/file_download.php?file_id=9667&type=bug
Issue History
Date ModifiedUsernameFieldChange
2016-08-02 15:36shuehnerNew Issue
2016-08-02 15:36shuehnerAssigned To => Triage Finance
2016-08-02 15:36shuehnerModules => Core
2016-08-02 15:36shuehnerTriggers an Emergency Pack => No
2016-08-02 15:36shuehnerTag Attached: Performance
2016-08-02 15:37shuehnerFile Added: 33602_prototype.diff
2016-08-02 15:51shuehnerNote Added: 0088754
2016-08-12 12:04dmitry_mezentsevRelationship addedrelated to 0033650
2016-08-23 14:56shuehnerRelationship addedrelated to 0033605
2017-11-10 11:21caristuRelationship addedrelated to 0035989

Notes
(0088754)
shuehner   
2016-08-02 15:51   
Note:
Same problem + maybe same solution some 20 lines later in fillSessionArguments in this code
      List<String> orgList = Arrays.asList(Utility
          .getContext(conn, vars, "#User_Org", "LoginHandler").replace("'", "").split(","));
      String acctSchemaId = OBLedgerUtils.getOrgsLedger(orgList);

takes 200ms each time
select organizati1_.C_Acctschema_ID as col_0_0_ from AD_Org organizati0_ cross join AD_Org organizati1_ where (organizati0_.AD_Org_ID in (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)) and ad_isorgincluded(organizati0_.AD_Org_ID, organizati1_.AD_Org_ID, organizati0_.AD_Client_ID)<>-1 and (organizati1_.C_Acctschema_ID is not null) and organizati1_.IsActive='Y' order by ad_isorgincluded(organizati0_.AD_Org_ID, organizati1_.AD_Org_ID, organizati0_.AD_Client_ID) limit ?

As list is 524 orgs long in my testcase