[eclipselink-users] unnecessary join made in case of Many<-->Many relati

[Samba <saasira@xxxxxxxxx>]

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