openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sharma, Amit" <Amit.Sha...@adp.com>
Subject Unnecessary Repeating Join across PARALLEL queries for EAGER fetch
Date Mon, 07 Jan 2013 13:57:57 GMT
Hi All

I have the following POJOs defined as Entities. I need to get all Customers from DB sorted
by one of the fields in CustomerDetails (say mobile) - additionally, I want to fetch Address
and CustomerReferral entities loaded EAGERLY for each Customer

Customer----(1..1)----CustomerDetails
Customer----(1..n)----CustomerAddress
Customer----(1..n)----CustomerReferral

In order to sort by CustomerDetails.mobile, I have a LEFT JOIN using JPA Criteria API as shown
below -

       qry.orderBy(cb.asc(aRoot.join("customerDetails", JoinType.LEFT).get("mobile")));

However, when I execute this query to get the result list, I see the LEFT JOIN to CustomerDetails
(t0.ID = t1.CUSTOMER_ID(+)) repeating across all the parallel fetches that JPA does to fetch
CustomerAddress and CustomerReferral entities - this is not required because a LEFT JOIN implies
we need not bother about the join in subsequent fetches - this is hurting me because my entity
being joined is a very heavy one and is impacting the performance of the system.

Is there a way to avoid the LEFT JOIN in the 2nd and 3rd queries below as they seem to not
add any value.

SELECT t0.ID, t2.ID, t2.HOME_PHONE, t2.MOBILE, t0.DOB, t0.NAME, t0.TELEPHONE,
        t1.MOBILE
    FROM CUSTOMER t0, CUSTOMER_DETAILS t1, CUSTOMER_DETAILS t2
    WHERE t0.ID = t1.CUSTOMER_ID(+) AND t0.ID = t2.CUSTOMER_ID(+)
    ORDER BY t1.MOBILE ASC

SELECT t0.ID, t2.ID, t2.CUSTOMER_ID, t2.NAME, t2.PHONE
    FROM CUSTOMER t0, CUSTOMER_DETAILS t1, CUSTOMER_REFERRAL t2
    WHERE t0.ID = t1.CUSTOMER_ID(+) AND t0.ID = t2.CUSTOMER_ID
    ORDER BY t0.ID ASC

SELECT t0.ID, t2.ID, t2.CITY, t2.CUSTOMER_ID, t2.STREET1, t2.STREET2,
        t2.ZIP_CODE
    FROM CUSTOMER t0, CUSTOMER_DETAILS t1, ADDRESS t2
    WHERE t0.ID = t1.CUSTOMER_ID(+) AND t0.ID = t2.CUSTOMER_ID
    ORDER BY t0.ID ASC


Thanks
Amit Sharma

----------------------------------------------------------------------
This message and any attachments are intended only for the use of the addressee and may contain
information that is privileged and confidential. If the reader of the message is not the intended
recipient or an authorized representative of the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited. If you have received
this communication in error, notify the sender immediately by return email and delete the
message and any attachments from your system.

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message