Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0033602Openbravo ERP09. Financial managementpublic2016-08-02 15:362016-08-02 15:51
shuehner 
Triage Omni OMS 
normalminorhave not tried
newopen 
5
 
 
Core
No
0033602: LoginUtils.fillSessionArguments (query Attribute.selectOrgCurrency quite slow) around 100ms with many orgs
In 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.
-
Performance
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 
diff 33602_prototype.diff (2,137) 2016-08-02 15:37
https://issues.openbravo.com/file_download.php?file_id=9667&type=bug
Issue History
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