Re: [eclipselink-users] unnecessary join made in case of Many<-->Many re

[James Sutherland <jamesssss@xxxxxxxxx>]

JPQL is defined in terms of objects, not data, so the reference to myRoles is
refering to the Roles object, and any of its data can be accessed, so the
join is to the object.

If you are just interested in querying the join table, you could define a
native SQL query, and use whatever SQL you desire.

It is odd that the extra join is taking so much time, have you indexed
everything correctly?  It seems like it is doing a table scan.  I suppose
that technically EclipseLink could parse the query and determine that only
the id on the Role was accessed, so optimize the query, but this would be
fairly complex, perhaps log an enhancement request.


saasira wrote:
> 
> Interestingly, I have observed one difference though...
> 
>       When I have the join table in a Many-Many relation mapped as an
> entity, and the mapping is like:
> 
>       MyUser{
> 
>            @OneToMany(mappedBy="userId")
>             Set<MyUserRoleAssociation> myUserRoleAssociation;
>       }
> 
>       MyRole{
> 
>            @OneToMany(mappedBy="roleId")
>             Set<MyUserRoleAssociation> myUserRoleAssociation;
>      }
> 
>      MyUserRoleAssociation{
>           @ManyToOne
>                      @JoinColumn(name="roleId", nullable=false,
> insertable=false, updatable=false)
>                      Long myRoleId;
>           @ManyToOne
>                      @JoinColumn(name="userId", nullable=false,
> insertable=false, updatable=false)
>                      Long myUserId;
>      }
> 
> 
>     then I could change my query to:
> 
>         Query query=em.createQuery( "select myuser from MyUser myuser join
> MyUserRoleAssociation
>                                myURA where myURA.myRoleId=:roleId");
>        query.setParameter("roleId",123)
>        query.getResultList();
> 
>    This is working as expected.
> 
> 
> Unfortunately, I inherit the entities from legacy code which I cannot
> change
> it immediately. Presently I do not have the Join table
> MyUserRoleAssociation
> as a mapped entity and the mapping is  a many to many on both sides of
> MyUser and MyRole.
> 
> Can't we get the query optimized with out having an entity for the join
> table?
> 
> Shouldn't the persistence provider be intelligent enough to make
> appropriate
> join in case of a many to many join?
> 
> Thanks and Regards,
> Samba
> 
> 
> On Mon, Jul 27, 2009 at 6:06 PM, Samba <saasira@xxxxxxxxx> wrote:
> 
>> Hi,
>>     I'm facing some performance problems when trying to fetch
>> associations
>> from a many-to-many relationship:
>>
>> I have   MyUser*<--->*MyRole  joined by MyUserRoleAssociation
>>
>> I'm trying to fetch all users who has a certain role.
>>
>> Below is the query I wrote:
>>
>>    Query query=em.createQuery( "select myuser from MyUser myuser join
>> myuser.myRoles myroles where myroles.id=:roleId");
>>    query.setParameter("roleId",123)
>>    query.getResultList();
>>
>> the query  is taking around 10 seconds to get the results
>>
>> where as when I run
>>
>> select * from MyUser myuser  join MyUserRoleAssociation  myura on
>> myura.userid=myuser.id where myura.roleId=123 ,
>>
>> the results are fetched in around 100 milli seconds.
>>
>>
>> I tried to simulate the query that is getting created from JPQL and this
>> is
>> what it looks like;
>>
>> select * from MyUser myuser  join MyUserRoleAssociation  myura on
>> myura.userid=myuser.id join MyRole myrole on myura.roleId=myrole.id where
>> myura.roleId==123
>>
>> And I could indeed reproduce the same amount of delay in fetching the
>> results with the above query.
>>
>>
>> So my question is: when I'm querying for a certain entity based on a
>> condition over a joined field, shouldn't the query be just making  a join
>> over the join table?
>>                             why is the join over other side of the
>> relation
>> ship made? Isn't it a performance overhead?
>>
>> Am I doing some thing wrong here?
>> Or is there any way I can fetch results with out the extra join over the
>> other side of the relationship in a many-to-many mapped entities?
>>
>> Thanks and Regards,
>> Samba
>>
> 
> 
> 


-----
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/unnecessary-join-made-in-case-of-Many%3C--%3EMany-relation-tp24679954p24703970.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.