Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0031769Openbravo ERPA. Platformpublic2015-12-23 16:302016-06-17 19:37
Reporteralostale 
Assigned ToNaroaIriarte 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR16Q3Fixed in Version3.0PR16Q3 
Merge Request Status
Review Assigned Toalostale
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0031769: Unneeded left joins in DefaultJsonDataService
DescriptionIe. Product (by Price and Warehouse) selector executes multiple unneeded left joins.

The generated HQL in DefaultJsonDataService is:
 select e
   from ProductByPriceAndWarehouse as e
   left join e.product as join_0
   left join e.productPrice as join_1
   left join join_1.priceListVersion as join_2
   left join join_0.genericProduct as join_3
   left join e.warehouse as join_4
   left join join_2.priceList as join_5
   left join join_5.currency as join_6
   left join join_0.uOM as join_7
  where ( ( e.organization in ('0','E443A31992CB4635AFCAEABE7183CE85','B843C30461EA4501935CB1D125C9C25A','19404EAD144C49A0AF37D54377CF452D') )
    and (e.active='Y'
    and (AD_ISORGINCLUDED(e.orgwarehouse, :alias_0, :alias_1)<>-1 or (AD_ISORGINCLUDED( :alias_2, e.orgwarehouse, :alias_3)<>-1))
    AND e.productPrice.priceListVersion.active='Y'
    and e.productPrice.priceListVersion.priceList.salesPriceList = true
    AND e.productPrice.priceListVersion.priceList.currency.id = '102'
    and e.productPrice.priceListVersion.id = 'FDE536FE9D8C4B068C32CD6C3650B6B8' and e.warehouse.id = 'B2D40D8A5D644DD89E329DC297309055') )
    and e.client.id in ('23C59575B9CF467C9620760EB255B389', '0') and e.active='Y' order by join_0.name,e.id)

Note all "left joins" which are not needed, this results in suboptimal SQL query.
Steps To Reproduce-Go to Sales Order
-Create a new line
-Open product selector drop down
  -> see HQL query
Proposed SolutionIn this case AdvancedQueryBuilder shouldn't add left joins for additional properties.

Applying attached testing patch the HQL is:
 select e
    from ProductByPriceAndWarehouse as e
    left join e.product as join_0
    where ( ( e.organization in ('0','E443A31992CB4635AFCAEABE7183CE85','B843C30461EA4501935CB1D125C9C25A','19404EAD144C49A0AF37D54377CF452D') )
    and (e.active='Y'
    and (AD_ISORGINCLUDED(e.orgwarehouse, :alias_0, :alias_1)<>-1 or (AD_ISORGINCLUDED( :alias_2, e.orgwarehouse, :alias_3)<>-1))
    AND e.productPrice.priceListVersion.active='Y'
    and e.productPrice.priceListVersion.priceList.salesPriceList = true
    AND e.productPrice.priceListVersion.priceList.currency.id = '102'
    and e.productPrice.priceListVersion.id = 'FDE536FE9D8C4B068C32CD6C3650B6B8'
    and e.warehouse.id = 'B2D40D8A5D644DD89E329DC297309055') )
    and e.client.id in ('23C59575B9CF467C9620760EB255B389', '0')
    and e.active='Y' order by join_0.name,e.id

which has a much better execution plan.

Check whether/when these joins can be necessary and include them only in this cases.
Additional Information
TagsPerformance
Relationships
related to feature request 00207323.0MP13 closed mtaal Implement property/computed fields 
related to feature request 00208443.0MP13 closed mtaal Implement computed column/property 
related to feature request 0033015 closed caristu Performance Improvements in Product Selector 
related to defect 0033051 closed NaroaIriarte The security check is called twice when opening a window or refreshing it 
Attached Filesdiff testing-31769.diff (650) 2015-12-23 16:36
https://issues.openbravo.com/file_download.php?file_id=8848&type=bug
Issue History
Date ModifiedUsernameFieldChange
2015-12-23 16:30alostaleNew Issue
2015-12-23 16:30alostaleAssigned To => platform
2015-12-23 16:30alostaleModules => Core
2015-12-23 16:30alostaleTriggers an Emergency Pack => No
2015-12-23 16:30alostaleTag Attached: Performance
2015-12-23 16:30alostaleStatusnew => acknowledged
2015-12-23 16:31alostaleFile Added: testing-31769.diff
2015-12-23 16:36alostaleFile Deleted: testing-31769.diff
2015-12-23 16:36alostaleFile Added: testing-31769.diff
2015-12-23 16:37alostaleRelationship addedrelated to 0020732
2015-12-23 16:46alostaleTarget Version => 3.0PR16Q2
2016-03-22 10:20alostaleTarget Version3.0PR16Q2 => 3.0PR16Q3
2016-04-01 08:31alostaleRelationship addedrelated to 0020844
2016-04-01 10:01alostaleNote Added: 0085376
2016-05-23 10:36caristuRelationship addedrelated to 0033015
2016-05-23 12:21NaroaIriarteAssigned Toplatform => NaroaIriarte
2016-05-23 12:36NaroaIriarteNote Added: 0086645
2016-05-25 15:19hgbotCheckin
2016-05-25 15:19hgbotNote Added: 0086747
2016-05-25 15:19hgbotStatusacknowledged => resolved
2016-05-25 15:19hgbotResolutionopen => fixed
2016-05-25 15:19hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/9c959e0a766cfa3e17b2d9c03e4f30cd7b823722 [^]
2016-05-25 17:14alostaleRelationship addedrelated to 0033051
2016-05-31 08:13alostaleReview Assigned To => alostale
2016-05-31 08:13alostaleNote Added: 0086845
2016-05-31 08:13alostaleStatusresolved => closed
2016-05-31 08:13alostaleFixed in Version => 3.0PR16Q3
2016-06-17 19:37hudsonbotCheckin
2016-06-17 19:37hudsonbotNote Added: 0087570

Notes
(0085376)
alostale   
2016-04-01 10:01   
See further analysis: https://docs.google.com/a/openbravo.com/document/d/1tT9vTPSRkGkhXsS0nMH6D5LXZcsqTeyCHREmi57x32U/edit?usp=sharing [^]
(0086645)
NaroaIriarte   
2016-05-23 12:36   
The decision of removing the left joins have been taken after a research of the following two options:
1- First option; Let the left joins there but convert them into fetch joins.
2- Second option; remove the left joins.

The results obtained in the Product (Product by Price and Warehouse) selector have been deterministics for the decision of
removing the joins.

The fact of using a fetch join was much more inefficient because of the transfer of the data.
Every field was being transferred for each line, so the time penalization is big.

It penalizes more having a fetch join because of the data transfer than not having a fetch join and accessing the database afterwards.
(0086747)
hgbot   
2016-05-25 15:19   
Repository: erp/devel/pi
Changeset: 9c959e0a766cfa3e17b2d9c03e4f30cd7b823722
Author: Naroa Iriarte <naroa.iriarte <at> openbravo.com>
Date: Tue May 24 15:26:38 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/9c959e0a766cfa3e17b2d9c03e4f30cd7b823722 [^]

Fixed issue 31769: The unnecessary left joins have been deleted


There were a lot of unused left joins.
The decission of deleting the joins have been taken after the research
of two options:
1- Transforming the joins into fetch joins
2- Deleting the joins.

The results obtained in the Product (Product by Price and Warehouse) selector
have been deterministics for the decision of removing the joins.
It penalizes more having a fetch join because of the data transfer than not
having a fetch join and accessing the database afterwards.

---
M modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
---
(0086845)
alostale   
2016-05-31 08:13   
Code reviewed and tested
(0087570)
hudsonbot   
2016-06-17 19:37   
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/0dc7be081b1c [^]
Maturity status: Test