Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0042370Openbravo ERPA. Platformpublic2019-11-21 18:282022-05-03 08:57
Reportermauricio_peccorini 
Assigned ToAugustoMauch 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionPR22Q3 
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
Summary0042370: Unnecessary executions of a query that loads the complete organizational structure of a client
DescriptionCreation of new OBContexts or reinitialization of OrganizationStructureProviders cause many unnecessary executions of the query that loads the organizational tree of a client.
Steps To Reproduce- Set a breakpoint on line 95 of org.openbravo.dal.security.OrganizationStructureProvider (List<Object[]> treeNodes = qry.list();)
- Perform regular actions like logging in from different accounts even on the same terminal, creating tickets and waiting for Import Entries to be processed. The breakpoint is hit for many of the actions and, for some of them, several times.
Proposed SolutionSince Import Entry reinitializes the available OrganizationStructureProviders it is not enough to cache those instances. Instead, the internal Map or organization nodes should be cached by client Id.

To manage the cases when the organization structure changes, a Business Entity Event Observer should be developed to listen changes to Organization and clear the cached maps when organizations are created, deleted or its relevant properties are updated. The properties are: PeriodControlAllowedOrganization, CalendarOwnerOrganization, BusinessUnitOrganization and LegalEntityOrganization.

Attached diffs with the proposed implementation.
Additional Information
TagsPerformance
Relationships
related to defect 0048756 closed alostale unnecesary OrganizationStructureProvider double initialization on ticket creation 
Attached Filesdiff OrganizationStructureProvider.diff (6,948) 2019-11-21 18:45
https://issues.openbravo.com/file_download.php?file_id=13574&type=bug
Issue History
Date ModifiedUsernameFieldChange
2019-11-21 18:28mauricio_peccoriniNew Issue
2019-11-21 18:28mauricio_peccoriniAssigned To => platform
2019-11-21 18:28mauricio_peccoriniFile Added: OrganizationStructureProvider.diff
2019-11-21 18:28mauricio_peccoriniModules => Core
2019-11-21 18:28mauricio_peccoriniTriggers an Emergency Pack => No
2019-11-21 18:45mauricio_peccoriniFile Deleted: OrganizationStructureProvider.diff
2019-11-21 18:45mauricio_peccoriniFile Added: OrganizationStructureProvider.diff
2019-12-26 11:18AugustoMauchAssigned Toplatform => mauricio_peccorini
2020-08-14 16:41ALopeteguiIssue Monitored: ALopetegui
2020-08-14 16:43ALopeteguiTag Attached: Performance
2021-12-30 10:38AugustoMauchAssigned Tomauricio_peccorini => AugustoMauch
2021-12-30 10:56ioritzCiaNote Added: 0134069
2022-02-01 07:24alostaleAssigned ToAugustoMauch => Triage Platform Base
2022-03-09 15:16ioritzCiaNote Added: 0135593
2022-03-09 15:17ioritzCiaNote Edited: 0135593bug_revision_view_page.php?bugnote_id=0135593#r23765
2022-03-10 07:30alostaleRelationship addedrelated to 0048756
2022-03-11 13:05AugustoMauchAssigned ToTriage Platform Base => cberner
2022-03-11 13:05AugustoMauchStatusnew => scheduled
2022-04-07 15:51AugustoMauchAssigned Tocberner => AugustoMauch
2022-04-12 14:26hgbotNote Added: 0136491
2022-05-03 08:57hgbotResolutionopen => fixed
2022-05-03 08:57hgbotStatusscheduled => closed
2022-05-03 08:57hgbotFixed in Version => PR22Q3
2022-05-03 08:57hgbotNote Added: 0136895
2022-05-03 08:57hgbotNote Added: 0136896

Notes
(0134069)
ioritzCia   
2021-12-30 10:56   
I would like to raise this issue again. We looked in two of hour biggest customers taking a look at pg_stat_statements and in one of them is the 2nd query reading the most and in the other the 9th. It is not that it is bad performant but it gets executed too often. In the first of the two customers around 200 times per minute, which does not make sense because the organization tree does not change as often.
(0135593)
ioritzCia   
2022-03-09 15:16   
(edited on: 2022-03-09 15:17)
In another review of the statistics. It was the 2nd in two of our biggest customers, the 9th in another. In on of the first two, analyzing the peak hour of the peak day it was 1st most reading query, it had read almost 6.5TB in that hour and it had an average execution of 72 times per second.
(0136491)
hgbot   
2022-04-12 14:26   
Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/559 [^]
(0136895)
hgbot   
2022-05-03 08:57   
Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/openbravo [^]
Changeset: 2e14b5c56ecce6699486a10c6f8072b29cdabad8
Author: Augusto Mauch <augusto.mauch@openbravo.com>
Date: 03-05-2022 06:39:21
URL: https://gitlab.com/openbravo/product/openbravo/-/commit/2e14b5c56ecce6699486a10c6f8072b29cdabad8 [^]

Fixes ISSUE-42370: OrganizationStructureProvider caches the organization node data

Now the organization node data will be stored in a TimeInvalidatedCache so that it is cached but periodically recalculated (current
periodicity is 5 minutes). Users of OrganizationStructureProvider that used to invoke the reinitialize method no longer do it unless
they really require an updated org info (only use cases were tests that invoked assertPersistOrgInfo to ensure the OrganizationStructureProvider
reflected the changes they just did to the organization structure and InitialOrgSetup, right after creating a new organization).

---
A src/org/openbravo/dal/security/OrganizationNodeCache.java
M src/org/openbravo/dal/security/OrganizationStructureProvider.java
M src/org/openbravo/erpCommon/ad_forms/InitialOrgSetup.java
M src/org/openbravo/service/importprocess/ImportEntryProcessor.java
---
(0136896)
hgbot   
2022-05-03 08:57   
Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/559 [^]