EclipseLink generates cartesian plan instead of (inner) joins in SQL. Why?

Question

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

Answer 1

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