Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0031769
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformmajorhave not tried2015-12-23 16:302016-06-17 19:37
ReporteralostaleView Statuspublic 
Assigned ToNaroaIriarte 
PrioritynormalResolutionfixedFixed in Version3.0PR16Q3
StatusclosedFix in branchFixed in SCM revision9c959e0a766c
ProjectionnoneETAnoneTarget Version3.0PR16Q3
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toalostale
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0031769: 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.
TagsPerformance
Attached Filesdiff file icon testing-31769.diff [^] (650 bytes) 2015-12-23 16:36 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to feature request 00207323.0MP13 closedmtaal Implement property/computed fields 
related to feature request 00208443.0MP13 closedmtaal Implement computed column/property 
related to feature request 0033015 closedcaristu Performance Improvements in Product Selector 
related to defect 0033051 closedNaroaIriarte The security check is called twice when opening a window or refreshing it 

-  Notes
(0085376)
alostale (manager)
2016-04-01 10:01

 See further analysis: https://docs.google.com/a/openbravo.com/document/d/1tT9vTPSRkGkhXsS0nMH6D5LXZcsqTeyCHREmi57x32U/edit?usp=sharing [^]
(0086645)
NaroaIriarte (developer)
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 (developer)
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 (manager)
2016-05-31 08:13

 Code reviewed and tested
(0087570)
hudsonbot (developer)
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

- Issue History
Date Modified Username Field Change
2015-12-23 16:30 alostale New Issue
2015-12-23 16:30 alostale Assigned To => platform
2015-12-23 16:30 alostale Modules => Core
2015-12-23 16:30 alostale Triggers an Emergency Pack => No
2015-12-23 16:30 alostale Tag Attached: Performance
2015-12-23 16:30 alostale Status new => acknowledged
2015-12-23 16:31 alostale File Added: testing-31769.diff
2015-12-23 16:36 alostale File Deleted: testing-31769.diff
2015-12-23 16:36 alostale File Added: testing-31769.diff
2015-12-23 16:37 alostale Relationship added related to 0020732
2015-12-23 16:46 alostale Target Version => 3.0PR16Q2
2016-03-22 10:20 alostale Target Version 3.0PR16Q2 => 3.0PR16Q3
2016-04-01 08:31 alostale Relationship added related to 0020844
2016-04-01 10:01 alostale Note Added: 0085376
2016-05-23 10:36 caristu Relationship added related to 0033015
2016-05-23 12:21 NaroaIriarte Assigned To platform => NaroaIriarte
2016-05-23 12:36 NaroaIriarte Note Added: 0086645
2016-05-25 15:19 hgbot Checkin
2016-05-25 15:19 hgbot Note Added: 0086747
2016-05-25 15:19 hgbot Status acknowledged => resolved
2016-05-25 15:19 hgbot Resolution open => fixed
2016-05-25 15:19 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/9c959e0a766cfa3e17b2d9c03e4f30cd7b823722 [^]
2016-05-25 17:14 alostale Relationship added related to 0033051
2016-05-31 08:13 alostale Review Assigned To => alostale
2016-05-31 08:13 alostale Note Added: 0086845
2016-05-31 08:13 alostale Status resolved => closed
2016-05-31 08:13 alostale Fixed in Version => 3.0PR16Q3
2016-06-17 19:37 hudsonbot Checkin
2016-06-17 19:37 hudsonbot Note Added: 0087570

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker