EL eclipselink-1.1.1.v20090430-r4097
two entities
XCPTN and LABEL
realtionship
XCPTN 0..M ---------- 0..M LABEL
DB Schema
XCPTN 1..1 ------------ 0..M XCPTN_LABEL 0..M -------------- 1..1 LABEL
Xcptn.java
-----------------------------------------------------------------
...
@ManyToMany
@JoinTable(name = "XCPTN_LABEL",
joinColumns = @JoinColumn(name = "XCPTN_ID"),
inverseJoinColumns = @JoinColumn(name = "LABEL_ID"))
public Collection<Label> getLabels() {
return labels;
}
--------------------------------------------------------------------
JPA-QL
select distinct x from Xcptn x left join fetch x.labels where
x.id < 1000050
Generated SQL:
---------------------------------------------------
SELECT DISTINCT
t1.ID,
t1.MKT_CLS,
t0.ID,
t0.NAME
FROM
XCPTN t1
LEFT OUTER JOIN (XCPTN_LABEL t2 JOIN LABEL t0 ON (t0.ID = t2.LABEL_ID)) ON (t2.XCPTN_ID = t1.ID)
WHERE
(t1.ID < 1000050)
-----------------------------------------------------------
I use Derby DB, Xcptn table has 200K records, Labels has 3 records. XCPTN_LABELS has about 140K records.
The SQL query above runs very very slow (72 sec) (BTW it returns only 4 rows.)
The query generated by Hibernate or OpenJPA is a bit different and runs much much faster (951 ms)
HB:
--------------------------------------------------
select
distinct xcptn0_.ID as ID4_0_,
label2_.ID as ID3_1_,
xcptn0_.MKT_CLS as MKT2_4_0_,
label2_.NAME as NAME3_1_,
labels1_.XCPTN_ID as XCPTN1_0__,
labels1_.LABEL_ID as LABEL2_0__
from
XCPTN xcptn0_
left outer join
XCPTN_LABEL labels1_
on xcptn0_.ID=labels1_.XCPTN_ID
left outer join
LABEL label2_
on labels1_.LABEL_ID=label2_.ID
where
xcptn0_.ID<1000050
--------------------------------------------------
OpenJPA:
--------------------------------------------------
SELECT DISTINCT
t0.ID,
t0.MKT_CLS,
t1.XCPTN_ID,
t2.ID,
t2.NAME
FROM
XCPTN t0
LEFT OUTER JOIN XCPTN_LABEL t1 ON t0.ID = t1.XCPTN_ID
LEFT OUTER JOIN LABEL t2 ON t1.LABEL_ID = t2.ID
WHERE (t0.ID < ?)
[params=(long) 1000050]
-----------------------
So, the problem is in nested Join
LEFT OUTER JOIN (XCPTN_LABEL t2 JOIN LABEL t0 ON (t0.ID = t2.LABEL_ID)) ON (t2.XCPTN_ID = t1.ID)
HB and OpenJPA SQL version with two joins work faster in this case.
left outer join
XCPTN_LABEL labels1_
on xcptn0_.ID=labels1_.XCPTN_ID
left outer join
LABEL label2_
on labels1_.LABEL_ID=label2_.ID
--------------------------------------
Is there any way to tell EL to use two simple joins instead of nested joins?
Thank you
Alex
