[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] @ManyToMany -- EL generates not optimal query (with nested joins)
|
Please log a bug for this issue, include the SQL that EclipseLink generates,
and the SQL that you believe is optimal, and we will investigate the issue.
Have you tried other databases, or other queries, is one SQL always more
optimal than the other, or does it depend on the usage?
As a workaround you could use native SQL. You may also be able to customize
your DerbyPlatform to change the syntax that is generated to some degree.
See also, SQLSelectStatement
Alex Pivovarov wrote:
>
> 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
>
>
-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
EclipseLink , http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.nabble.com/EclipseLink-f26430.html EclipseLink
Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
--
View this message in context: http://www.nabble.com/%40ManyToMany----EL-generates-not-optimal-query-%28with-nested-joins%29-tp23809284p23963396.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
