unnecessary join made in case of Many<-->Many relation

[saasira]

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




_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[saasira]

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@...> 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

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[James Sutherland]

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 <[e-mail]> 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
>