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

[Samba <saasira@xxxxxxxxx>]

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