Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0028479Openbravo ERPA. Platformpublic2014-12-23 16:072018-01-03 12:38
Reporteralostale 
Assigned Toalostale 
PriorityhighSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR18Q1 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0028479: unneeded queries to ad_langue table
DescriptionWhenever a new DAL object instance of any entity that has a reference to AD_Language is created, a query to AD_Language table is executed. This is caused because:

1. Hibernate eagerly initializes many-to-one references that are linked not by primary key but by unique constraint.
2. After this eager initialization the object is not included in 1st level cache.

This is noticeable, for example, when standard views are generated in an instance with translations installed, ad_langue table is unnecessarily queried.

The problem is caused while initializing proxies for trl lists (ie. fld.getADFieldTrlList()). Translation relationship is based not on FK but on unique constraint, due to some issue in Hibernate these relationships are not proxied nor cached, resulting on being queried whenever the object is initialized.

In a customer with translations installed, it's been measured queries to AD_Language to be ~ 8% of the total number of executed queries.
Steps To Reproduce1. Install Spanish translation
2. Set all modules in development so view is always computed
3. Set pg to log all queries
4. Open BP window
    -> Check there are 8500 queries to ad_language (all of them retrieving the same record)
Proposed SolutionBackport HHH-11703 [1] Hibernate issue that adds objects initialized by a unique contraint to 1st level cache. In this way, only one query per different language is executed per request (which typically is just one) instead one per record initialized.

---
[1] https://hibernate.atlassian.net/browse/HHH-11703 [^]
Additional Information
TagsPerformance
Relationships
related to defect 0028477 closed alostale view generation: unneeded queries to trl tables 
related to defect 00207423.0MP13 closed mtaal Many-to-one references to language are always eagerly fetched using a separate query 
related to feature request 0037064 closed caristu upgrade hibernate to 5.3.2 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2014-12-23 16:07alostaleNew Issue
2014-12-23 16:07alostaleAssigned To => AugustoMauch
2014-12-23 16:07alostaleModules => Core
2014-12-23 16:07alostaleTriggers an Emergency Pack => No
2014-12-23 16:09alostaleTag Attached: per
2014-12-23 16:09alostaleTag Detached: per
2014-12-23 16:09alostaleTag Attached: Performance
2014-12-23 16:10alostaleRelationship addedrelated to 0028477
2015-01-07 16:06shuehnerIssue Monitored: shuehner
2015-03-06 14:45alostalePrioritynormal => urgent
2015-03-06 14:45alostaleStatusnew => scheduled
2015-03-06 14:46alostaleStatusscheduled => acknowledged
2015-03-11 08:10alostaleTarget Version3.0PR15Q2 =>
2015-03-17 14:37alostaleAssigned ToAugustoMauch => platform
2015-11-11 11:37alostalePriorityurgent => normal
2015-11-11 12:42alostalePrioritynormal => high
2016-12-01 13:24alostalePriorityhigh => normal
2016-12-01 14:03alostalePrioritynormal => high
2017-10-10 16:03alostaleNote Added: 0099855
2017-10-13 12:02alostaleRelationship addedrelated to 0020742
2017-10-13 12:13alostaleReview Assigned To => caristu
2017-10-13 12:13alostaleSummaryview generation: unneeded queries to ad_langue table => unneeded queries to ad_langue table
2017-10-13 12:13alostaleDescription Updatedbug_revision_view_page.php?rev_id=16070#r16070
2017-10-13 12:13alostaleSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=16072#r16072
2017-10-13 12:13alostaleProposed Solution updated
2017-10-13 12:13alostaleDescription Updatedbug_revision_view_page.php?rev_id=16073#r16073
2017-10-13 12:19hgbotCheckin
2017-10-13 12:19hgbotNote Added: 0099901
2017-10-13 12:19hgbotStatusacknowledged => resolved
2017-10-13 12:19hgbotResolutionopen => fixed
2017-10-13 12:19hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/3dcf5dc6325f829eef34ffb86a1832aad5502b14 [^]
2017-10-13 12:36alostaleAssigned Toplatform => alostale
2017-10-13 12:38alostaleRelationship addedrelated to 0037064
2017-10-20 15:31caristuNote Added: 0099979
2017-10-20 15:31caristuStatusresolved => closed
2017-10-20 15:31caristuFixed in Version => 3.0PR18Q1
2018-01-03 12:38hudsonbotCheckin
2018-01-03 12:38hudsonbotNote Added: 0101329

Notes
(0099855)
alostale   
2017-10-10 16:03   
See https://hibernate.atlassian.net/browse/HHH-11703 [^]
(0099901)
hgbot   
2017-10-13 12:19   
Repository: erp/devel/pi
Changeset: 3dcf5dc6325f829eef34ffb86a1832aad5502b14
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Oct 13 12:03:15 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/3dcf5dc6325f829eef34ffb86a1832aad5502b14 [^]

fixed bug 28479: unneeded queries to ad_langue

  Backports HHH-11703 [1] Hibernate issue that adds objects initialized by a
  unique contraint to 1st level cache. In this way, only one query per different
  language is executed per request (which typically is just one) instead one per
  record initialized.

  Find code for backport here [2].

---
  [1] https://hibernate.atlassian.net/browse/HHH-11703 [^]
  [2] https://github.com/alostale/hibernate-orm [^]

---
M lib/runtime/hibernate3-patched.jar
---
(0099979)
caristu   
2017-10-20 15:31   
Verified
(0101329)
hudsonbot   
2018-01-03 12:38   
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/c81e0d3cbab5 [^]
Maturity status: Test