From derby-user-return-11876-apmail-db-derby-user-archive=db.apache.org@db.apache.org Tue Nov 10 04:40:41 2009 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 16235 invoked from network); 10 Nov 2009 04:40:41 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 10 Nov 2009 04:40:41 -0000 Received: (qmail 3139 invoked by uid 500); 10 Nov 2009 04:40:41 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 3030 invoked by uid 500); 10 Nov 2009 04:40:39 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Delivered-To: moderator for derby-user@db.apache.org Received: (qmail 70507 invoked by uid 99); 10 Nov 2009 03:50:49 -0000 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of bluesbreaker@gmail.com designates 209.85.218.218 as permitted sender) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:content-type; bh=GsA5lFYzy9U/sRuH5n2P3fI6tx2w0Xv6ilMERBnwlPs=; b=uQjqi7JSt29LmZsq0Wypq7gM75tBfKhTozE7Jfgi0knoXolNrDxc+bgdN47J2yEquM Jm8CsbCokDg2jHC/G0ZibEU3WAPdq6yoh+g9k2oQ9CX7Gb6+nmp2MLVmrU+srP/TiiGp NdYgH7QHCi1XIsVDsupTBYgwIzJ50Xs+6+aaA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=C/+jxblg78lsw2+tlRsl7N7UOKvWBpHsCmvVUwaf13eDOhORb66TmBqqy6B+DuCFPt dxZU3cDNzBzSHX2gRtE8t6i5AIx7/WhHtO/wIUgbCrnkZbfKB+nnoXWZZxzH7NbXM5nT ichDDcuHIpSZWo3xTmYId2rGtrCPQTnjLl2ZE= MIME-Version: 1.0 Date: Mon, 9 Nov 2009 22:50:20 -0500 Message-ID: <3bef7aa40911091950p799f123bk964ce696d908e314@mail.gmail.com> Subject: Derby 10.5.3: subquery performance using "IN" From: Andrew Alsup To: derby-user@db.apache.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Checked: Checked by ClamAV on apache.org I am experiencing a dramatic performance hit with a subquery utilizing a "IN" predicate. The query involves a total of 4 tables. If I run the query (with subquery) it takes ~4.5sec to complete. If I execute the subquery by itself, it takes ~0.03sec to complete. If I then copy the results of the subquery directly into the "IN" clause of the parent query, it takes only ~0.02sec to run. So, I would expect the combined query (with subquery) to take ~0.5sec (give or take a few millis). I'm experiencing an 800% slowdown by performing the subquery. Is there anything I can do to restructure my query? Thanks for any help you can provide. More information provided below. FWIW, the performance problem was originally identified with an UPDATE statement (with subquery), but I've rewritten it as a SELECT here. -- Andy The 4 tables are: end_item_meter: 461,617 rows meter_reading: 2,776,955 rows end_item_event: 1,704,171 rows end_Item_inventory: 233,057 rows -------------------------------------------- -- This is the real query (with subquery) -------------------------------------------- SELECT em.end_item_meter_id FROM end_item_meter em WHERE em.end_Item_meter_id IN ( SELECT mr.end_item_meter_id FROM Meter_Reading mr INNER JOIN End_Item_Event eie ON eie.end_Item_Event_Id = mr.end_Item_event_Id INNER JOIN End_Item_Inventory eii ON eii.end_Item_inventory_Id = eie.end_Item_inventory_id WHERE eii.End_Item_Inventory_Id = 6061799 ); ================================================================================================= Query 1 of 1 elapsed time (seconds) - Total: 4.458, SQL query: 0.025, Building output: 4.433 ================================================================================================= END_ITEM_METER_ID ----------------- 6162998 6162999 6163002 6163003 -------------------------------------------- -- Run subquery by itself -------------------------------------------- SELECT mr.end_item_meter_id FROM Meter_Reading mr INNER JOIN End_Item_Event eie ON eie.end_Item_Event_Id = mr.end_Item_event_Id INNER JOIN End_Item_Inventory eii ON eii.end_Item_inventory_Id = eie.end_Item_inventory_id WHERE eii.End_Item_Inventory_Id = 6061799; ================================================================================================= Query 1 of 1 elapsed time (seconds) - Total: 0.031, SQL query: 0.022, Building output: 0.00 ================================================================================================= END_ITEM_METER_ID ----------------- 6163002 6163003 6162999 6162998 6162999 6162998 6162999 6162998 6162999 6162998 -------------------------------------------- -- Run outer query with explicit values returned by inner query -------------------------------------------- SELECT em.end_item_meter_id FROM end_item_meter em WHERE em.end_Item_meter_id IN ( 6163002, 6163003, 6162999, 6162998, 6162999, 6162998, 6162999, 6162998, 6162999, 6162998 ); ================================================================================================= Query 1 of 1 elapsed time (seconds) - Total: 0.021, SQL query: 0.013, Building output: 0.008 ================================================================================================= END_ITEM_METER_ID ----------------- 6162998 6162999 6163002 6163003