From derby-user-return-8568-apmail-db-derby-user-archive=db.apache.org@db.apache.org Sun Feb 03 23:11:26 2008 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 17295 invoked from network); 3 Feb 2008 23:11:26 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 3 Feb 2008 23:11:26 -0000 Received: (qmail 70821 invoked by uid 500); 3 Feb 2008 23:11:17 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 70795 invoked by uid 500); 3 Feb 2008 23:11:17 -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 70784 invoked by uid 99); 3 Feb 2008 23:11:17 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 03 Feb 2008 15:11:17 -0800 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [203.217.22.128] (HELO file1.syd.nuix.com.au) (203.217.22.128) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 03 Feb 2008 23:10:50 +0000 Received: from host68.syd.nuix.com.au (host68.syd.nuix.com.au [192.168.222.68]) by file1.syd.nuix.com.au (Postfix) with ESMTP id 3CDD94A81AE for ; Mon, 4 Feb 2008 10:09:55 +1100 (EST) From: Daniel Noll Organization: Nuix Pty Ltd To: Derby Discussion Subject: Nested query in conjunction with ordering - unreasonably slow Date: Mon, 4 Feb 2008 10:08:33 +1100 User-Agent: KMail/1.9.6 (enterprise 0.20070907.709405) MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200802041008.33862.daniel@nuix.com> X-Virus-Checked: Checked by ClamAV on apache.org Hi all. I've been trying to determine the cause for a slowness in a certain query. I've managed to strip everything away and get to the core of what's making it slow, but I'd like to understand why. The query is of this form: SELECT jobs.id AS id, jobs.name AS name, (SELECT COUNT(1) FROM jobitems WHERE jobitems.jobid = jobs.id) AS count FROM jobs ORDER BY jobs.name This takes around 3000ms to complete if there are 200,000 rows in jobitems. If I remove the "count" subquery, then it runs in 1ms. If I remove the ORDER BY, it runs in 1ms. There is only one row in jobs, thus I would expect the same performance whether I order it or not. The PK on jobitems is (jobid,itemid); prior posts on this forum have led me to believe that this creates an implicit UNIQUE INDEX on (jobid,itemid) and also an implicit INDEX on (jobid). Whether this is the case or not, creating an additional INDEX on (jobid) itself doesn't speed things up. If I remove the count part and then execute it by itself, I get a total query time of 3ms for the two queries. No nested query should take longer than it takes to execute its parts separately. I discovered this issue on version 10.3.1.4 but it still performs the same way under version 10.3.2.1. The obvious workaround is not to order by name and sort the results on the Java side. But is there a way to rewrite this query that will make Derby happier? Daniel