EclipseLink generates cartesian plan instead of (inner) joins in SQL. Why?
eclipselink, jpa, orm, performance
Solution
Try using:
`@org.eclipse.persistence.annotations.JoinFetch(JoinFetchType.INNER)`
-- or --
`@org.eclipse.persistence.annotations.JoinFetch(JoinFetchType.OUTER)`
Outer joins are recommended for nullable columns.
I've found more information in this blog: http://vard-lokkur.blogspot.com/2010/09/jpa-demystified-episode-1-onetomany-and.html
Regards,
Victor Tortorello Neto
Problem
In the projects where Hibernate is my persistence provider, I can issue queries with 'join fetch' expressions, and Hibernate will generate SQL that mirrors that: SQL containing join expressions using valid paths of comparison. EclipseLink, however, issues SQL with ugly Cartesian plans, that hurt performance quite badly. While reading this article , it mentions that eagerly fetching might generate Cartesian plans, but it conveniently forgets that other providers (Hibernate) can optimize that. So, is it possible to instruct EclipseLink to optimize these queries? I believe that few relationships can be optimized through the usage of the @FetchJoin annotation, but I'm hoping to find something that does not include spreading ORM-specific annotations on the domain model. As an example, here's a (dynamic) query that I'm issuing as JPQL: ``` String query = "select w from WorkOrder w " + "inner join fetch w.type " + "inner join fetch w.details " + "inner join fetch w.project " + "inner join fetch w.priority " + "inner join fetch w.networkElement "; ``` And here's the EclipseLink output: ``` SELECT t1.ID, t1.CREATION, ... (the fetch fields here) FROM swa_network_element t5, swa_priorities t4, swa_dispatch_project t3, swa_work_order_detail t2, swa_work_orders t1, swa_work_order_type t0 WHERE ((t1.alpha_id LIKE '%TSK%') AND (((((t0.ID = t1.TYPE_ID) AND (t2.worder_id = t1.ID)) AND (t3.id = t1.project_id)) AND (t4.ID = t1.priority_id)) AND (t5.ID = t1.ne_id))) ORDER BY t1.CREATION DESC ``` Best regards, Rodrigo Hartmann