In projects where Hibernate is my continuity provider, I can issue queries with join fetch expressions, and Hibernate will generate SQL that reflects this: SQL containing join expressions using valid comparison paths.
EclipseLink, however, produces SQL with ugly Cartesian plans, which greatly degrades performance. While reading this article, she mentions that looking forward can lead to Cartesian plans, but she just forgets that other providers (Hibernate) can optimize this.
So, is it possible to instruct EclipseLink to optimize these queries? I believe some relationships can be optimized using the @FetchJoin annotation, but I hope to find something that does not include the distribution of ORM-specific annotations in the domain model.
As an example, here is a (dynamic) query that I issue 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 is the output of EclipseLink:
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
Regards, Rodrigo Hartmann