extraneous joins OPENJPA-134

[David Wisneski]

Hi Patrick or Abe,

I was wondering if any of you have noticed the following problem.  (
this is written up as OPENJPA-134).  This shows up as a performance
problem in applications when we compare with  Hibernate.

If I have a M:1 relationship and I make both sides of the relationship
EAGER in the mapping, OR  I do a  join fetch of the multi side
relationship,  we see an extra join in the generated sql.

Example:  I have a Dept -> Emp with inverse Emp->Dept.
 If both sides are EAGER in mapping I get the sql

  select ..  from dept t0 join emp t1 on(...) join dept t2 on (...)

 If both sides are LAZY and I do EJB query
  select  d from Dept d left join fetch d.emps

the generated sql is
  select ...  from dept t0 left join emp t1 on(...) join dept t2 on(...)

The extra  "join dept t2 on(...)" seems to be extraneous and has a
serious impact on performance.   Since I already joined Dept with Emp,
there is no need to join it back to the Dept because the Dept->Emp and
Emp->Dept relationships are inverses of the same foreign key.

Have you noticed this same problem?  Might you have a quick fix that
you can?  Otherwise we will start analyzing to see what might be
causing it.

[Patrick Linskey]

What if just one side is eager? Also, is the @OneToMany's mappedBy
attribute set up correctly?

-Patrick

--
Patrick Linskey
BEA Systems, Inc.

_______________________________________________________________________
Notice:  This email message, together with any attachments, may contain
information  of  BEA Systems,  Inc.,  its subsidiaries  and  affiliated
entities,  that may be confidential,  proprietary,  copyrighted  and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.

> -----Original Message-----
> From: David Wisneski [mailto:[hidden email]]
> Sent: Wednesday, February 14, 2007 3:27 PM
> To: open-jpa-dev
> Subject: extraneous joins OPENJPA-134
>
> Hi Patrick or Abe,
>
> I was wondering if any of you have noticed the following problem.  (
> this is written up as OPENJPA-134).  This shows up as a performance
> problem in applications when we compare with  Hibernate.
>
> If I have a M:1 relationship and I make both sides of the relationship
> EAGER in the mapping, OR  I do a  join fetch of the multi side
> relationship,  we see an extra join in the generated sql.
>
> Example:  I have a Dept -> Emp with inverse Emp->Dept.
>  If both sides are EAGER in mapping I get the sql
>
>   select ..  from dept t0 join emp t1 on(...) join dept t2 on (...)
>
>  If both sides are LAZY and I do EJB query
>   select  d from Dept d left join fetch d.emps
>
> the generated sql is
>   select ...  from dept t0 left join emp t1 on(...) join dept
> t2 on(...)
>
> The extra  "join dept t2 on(...)" seems to be extraneous and has a
> serious impact on performance.   Since I already joined Dept with Emp,
> there is no need to join it back to the Dept because the Dept->Emp and
> Emp->Dept relationships are inverses of the same foreign key.
>
> Have you noticed this same problem?  Might you have a quick fix that
> you can?  Otherwise we will start analyzing to see what might be
> causing it.
>

[David Wisneski]

I've looked at the code and it looks correct to me  and tried various
things like making the inverse LAZY.    But it always generating the
extraneous join.  I've attached the entity source code.

The relationship causing problem is

   @ManyToOne
    @JoinColumn(name="ACCOUNT_ACCOUNTID")
    private Accountejb2 account2;

and the inverse is


    @OneToMany(mappedBy = "account2", fetch=FetchType.EAGER)
    private Set<Holdingejb2> holdings2;

The sql generated is
 select ... from accountejb t0 join holdingejb t1 on(...)  join
account t2 on(...)

The sql is valid sql, but the second join is extranenous.  Any help
would be appreciated.

On 2/14/07, Patrick Linskey <[hidden email]> wrote:

> What if just one side is eager? Also, is the @OneToMany's mappedBy
> attribute set up correctly?
>
> -Patrick
>
> --
> Patrick Linskey
> BEA Systems, Inc.
>
> _______________________________________________________________________
> Notice:  This email message, together with any attachments, may contain
> information  of  BEA Systems,  Inc.,  its subsidiaries  and  affiliated
> entities,  that may be confidential,  proprietary,  copyrighted  and/or
> legally privileged, and is intended solely for the use of the individual
> or entity named in this message. If you are not the intended recipient,
> and have received this message in error, please immediately return this
> by email and then delete it.
>
> > -----Original Message-----
> > From: David Wisneski [mailto:[hidden email]]
> > Sent: Wednesday, February 14, 2007 3:27 PM
> > To: open-jpa-dev
> > Subject: extraneous joins OPENJPA-134
> >
> > Hi Patrick or Abe,
> >
> > I was wondering if any of you have noticed the following problem.  (
> > this is written up as OPENJPA-134).  This shows up as a performance
> > problem in applications when we compare with  Hibernate.
> >
> > If I have a M:1 relationship and I make both sides of the relationship
> > EAGER in the mapping, OR  I do a  join fetch of the multi side
> > relationship,  we see an extra join in the generated sql.
> >
> > Example:  I have a Dept -> Emp with inverse Emp->Dept.
> >  If both sides are EAGER in mapping I get the sql
> >
> >   select ..  from dept t0 join emp t1 on(...) join dept t2 on (...)
> >
> >  If both sides are LAZY and I do EJB query
> >   select  d from Dept d left join fetch d.emps
> >
> > the generated sql is
> >   select ...  from dept t0 left join emp t1 on(...) join dept
> > t2 on(...)
> >
> > The extra  "join dept t2 on(...)" seems to be extraneous and has a
> > serious impact on performance.   Since I already joined Dept with Emp,
> > there is no need to join it back to the Dept because the Dept->Emp and
> > Emp->Dept relationships are inverses of the same foreign key.
> >
> > Have you noticed this same problem?  Might you have a quick fix that
> > you can?  Otherwise we will start analyzing to see what might be
> > causing it.
> >
>

[Abe White]

> I've looked at the code and it looks correct to me  and tried various
> things like making the inverse LAZY.    But it always generating the
> extraneous join.  I've attached the entity source code.
>
> The relationship causing problem is
>
>   @ManyToOne
>    @JoinColumn(name="ACCOUNT_ACCOUNTID")
>    private Accountejb2 account2;

ManyToOne is eager by default, so right now both sides are eager.
 
_______________________________________________________________________
Notice:  This email message, together with any attachments, may contain
information  of  BEA Systems,  Inc.,  its subsidiaries  and  affiliated
entities,  that may be confidential,  proprietary,  copyrighted  and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.

[catalina shaw]

Regardless the ManyToOne is EAGER by default, when its
inverse relationship if
1. set to EAGER  (select a from a a;  a has the OneToManyRelation property)
or
2. set to LAZY but Eagerly Fetched by query (select ... from a a join fetch
a.OneToManyRelation )

We see unneeded joins being generated.

The issue is why generating the extraneous joins that can be optimized away.


>> I've looked at the code and it looks correct to me and tried various

>> things like making the inverse LAZY. But it always generating the

>> extraneous join. I've attached the entity source code.

>>

>> The relationship causing problem is

>>

>> @ManyToOne

>> @JoinColumn(name="ACCOUNT_ACCOUNTID")

>> private Accountejb2 account2;
>ManyToOne is eager by default, so right now both sides are eager.

[David Wisneski]

When both sides of a relationhship are EAGER this should result in
only one join (not two).

Account <-> Holidng is  One Account has Many holdings. Both roles of
the relationship are marked EAGER.

If I join
   Account t0 join Holding t1 on(t1.account=t0.accountId)
there should not be a need to do
   Account t0 join Holding t1 on(t1.account=t0.accountId)  join
Account t2 on(t2.accountid=t1.account)

t2 would be the same values as t0.   Putting in the extraneous join is
not needed and we see performance degradation compared to other OR
products (e.g.  Hibernate).

I think we can just not generate the extra join -- unless someone sees
something that I don't see.

On 2/15/07, catalina shaw <[hidden email]> wrote:

> Regardless the ManyToOne is EAGER by default, when its
> inverse relationship if
> 1. set to EAGER  (select a from a a;  a has the OneToManyRelation property)
> or
> 2. set to LAZY but Eagerly Fetched by query (select ... from a a join fetch
> a.OneToManyRelation )
>
> We see unneeded joins being generated.
>
> The issue is why generating the extraneous joins that can be optimized away.
>
>
> >> I've looked at the code and it looks correct to me and tried various
>
> >> things like making the inverse LAZY. But it always generating the
>
> >> extraneous join. I've attached the entity source code.
>
> >>
>
> >> The relationship causing problem is
>
> >>
>
> >> @ManyToOne
>
> >> @JoinColumn(name="ACCOUNT_ACCOUNTID")
>
> >> private Accountejb2 account2;
> >ManyToOne is eager by default, so right now both sides are eager.
>