Return-Path: Delivered-To: apmail-incubator-open-jpa-dev-archive@locus.apache.org Received: (qmail 1723 invoked from network); 8 Feb 2007 01:48:26 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 8 Feb 2007 01:48:26 -0000 Received: (qmail 31391 invoked by uid 500); 8 Feb 2007 01:48:33 -0000 Delivered-To: apmail-incubator-open-jpa-dev-archive@incubator.apache.org Received: (qmail 31284 invoked by uid 500); 8 Feb 2007 01:48:33 -0000 Mailing-List: contact open-jpa-dev-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: open-jpa-dev@incubator.apache.org Delivered-To: mailing list open-jpa-dev@incubator.apache.org Received: (qmail 31275 invoked by uid 99); 8 Feb 2007 01:48:33 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 07 Feb 2007 17:48:33 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO brutus.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 07 Feb 2007 17:48:25 -0800 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id 8A6DF7142B5 for ; Wed, 7 Feb 2007 17:48:05 -0800 (PST) Message-ID: <14217825.1170899285562.JavaMail.jira@brutus> Date: Wed, 7 Feb 2007 17:48:05 -0800 (PST) From: "Catalina Wei (JIRA)" To: open-jpa-dev@incubator.apache.org Subject: [jira] Created: (OPENJPA-134) Extra unneeded SQL joins for OneToMany relationship with fetch type EAGER MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org Extra unneeded SQL joins for OneToMany relationship with fetch type EAGER ------------------------------------------------------------------------- Key: OPENJPA-134 URL: https://issues.apache.org/jira/browse/OPENJPA-134 Project: OpenJPA Issue Type: Bug Components: sql Reporter: Catalina Wei Running JPAConfiguration default setting for EagerFetchMode (FetchModeValue.EAGER_PARALLEL), the SQL generated is sub-optimal. Consider the following entities: lineitems( OneToMany ) Order <===========================> OrderItem order ( ManyToOne ) Case 1: why not combining 2 SQL to 1 SQL ? ================================================================================================= Order.lineitmes(EAGER): OrderItem.order(LAZY): Executing query: select o from Order o 2173 demo TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1299336562 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr FROM Order t0 2213 demo TRACE [main] openjpa.jdbc.SQL - [40 ms] spent 2223 demo TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1406424020 SELECT t0.oid, t1.lid, t1.version, t1.cost, t1.order_oid, t1.part_partno, t1.quantity FROM Order t0 INNER JOIN OrderItem t1 ON t0.oid = t1.order_oid ORDER BY t0.oid ASC Case 2: extra unneeded LEFT OUTER JOIN, if eliminated, the selection aliase t2 should change to t1: ============================================================================================= Order.lineitmes(EAGER): OrderItem.order(LAZY): Executing query: select o from Order o left join fetch o.lineitems 2403 demo TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1500797300 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr, t2.order_oid, t2.lid, t2.version, t2.cost, t2.part_partno, t2.quantity FROM Order t0 LEFT OUTER JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN OrderItem t2 ON t0.oid = t2.order_oid ORDER BY t2.order_oid ASC Case 3: why not generating 1 SQL ? ================================================================================================== Order.lineitmes(EAGER): OrderItem.order(EAGER): Executing query: select o from Order o 2343 demo TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 384833264 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr FROM Order t0 2383 demo TRACE [main] openjpa.jdbc.SQL - [40 ms] spent 2393 demo TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1722705582 SELECT t0.oid, t1.lid, t1.version, t1.cost, t2.oid, t2.version, t2.amount, t2.customer_countryCode, t2.customer_id, t2.delivered, t2.shipaddr, t1.part_partno, t1.quantity FROM Order t0 INNER JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN Order t2 ON t1.order_oid = t2.oid ORDER BY t0.oid ASC 2393 demo TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 3134 demo TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 950548648 SELECT t0.lid, t0.version, t0.cost, t1.oid, t1.version, t1.amount, t1.customer_countryCode, t1.customer_id, t1.delivered, t1.shipaddr, t0.part_partno, t0.quantity FROM OrderItem t0 LEFT OUTER JOIN Order t1 ON t0.order_oid = t1.oid WHERE t0.order_oid = ? [params=(int) 88] 3134 demo TRACE [main] openjpa.jdbc.SQL - [0 ms] spent Case 4: duplicate selections and redundant joins ================================================================================================== Order.lineitmes(EAGER): OrderItem.order(EAGER): Executing query: select o from Order o left join fetch o.lineitems 2273 demo TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1565154634 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr, t2.order_oid, t2.lid, t2.version, t2.cost, t3.oid, t3.version, t3.amount, t3.customer_countryCode, t3.customer_id, t3.delivered, t3.shipaddr, t2.part_partno, t2.quantity FROM Order t0 LEFT OUTER JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN OrderItem t2 ON t0.oid = t2.order_oid LEFT OUTER JOIN Order t3 ON t2.order_oid = t3.oid ORDER BY t2.order_oid ASC -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.