From derby-user-return-5569-apmail-db-derby-user-archive=db.apache.org@db.apache.org Sat Nov 11 19:46:33 2006 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 42180 invoked from network); 11 Nov 2006 19:46:33 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 11 Nov 2006 19:46:33 -0000 Received: (qmail 66683 invoked by uid 500); 11 Nov 2006 19:46:43 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 66287 invoked by uid 500); 11 Nov 2006 19:46:42 -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 Received: (qmail 66276 invoked by uid 99); 11 Nov 2006 19:46:42 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 11 Nov 2006 11:46:42 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: neutral (herse.apache.org: local policy) Received: from [63.82.107.6] (HELO red.amberpoint.com) (63.82.107.6) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 11 Nov 2006 11:46:29 -0800 Received: from [127.0.0.1] (bp-laptop.edgility.com [10.10.11.34]) by red.amberpoint.com (8.12.11/8.12.11) with ESMTP id kABJk6AJ002361 for ; Sat, 11 Nov 2006 11:46:06 -0800 (PST) Message-ID: <4556287A.20501@amberpoint.com> Date: Sat, 11 Nov 2006 11:46:02 -0800 From: Bryan Pendleton User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: Derby Discussion Subject: Re: slow subqueries References: <009d01c705c0$a228c8b0$3302a8c0@referentia.com> In-Reply-To: <009d01c705c0$a228c8b0$3302a8c0@referentia.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org > This is why a nested loop is not going to work here... 20,000 squared > operations is very expensive, let alone millions squared. For a query with > this profile, the inner query should only be executed once. Perhaps you can get the behavior you desire by explicitly creating a temporary table, selecting the data from your inner query into the temporary table, then re-writing your main query to join against the temporary table? thanks, bryan