Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Date: Mon, 04 Feb 2008 10:09:41 +0100
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
Subject: Re: Nested query in conjunction with ordering - unreasonably slow
In-reply-to: <200802041008.33862.daniel@nuix.com>
Sender: Knut.Hatlen@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <wjo87ihlulyi.fsf@khepri41.norway.sun.com>
Organization: Sun Microsystems
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
References: <200802041008.33862.daniel@nuix.com>
User-Agent: Gnus/5.110007 (No Gnus v0.7) Emacs/22.1 (usg-unix-v)

Daniel Noll <daniel@nuix.com> writes:

> 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.

It sounds like the optimizer is making a bad decision for some reason,
but I don't know why (perhaps the statistics are out of date? See
http://thread.gmane.org/gmane.comp.apache.db.derby.user/8100/focus=8101).
Since you seem to have a small reproducible test case for this problem,
it would be great if you could file a JIRA issue and upload one sql
script that creates the tables/indices and insert enough sample data to
show the problem, and one sql script that runs the different queries.

> 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?

I don't know if it makes Derby happier, but I think this query does the
same thing without the nested query:

SELECT jobs.id, jobs.name, count(*) FROM jobs, jobitems
    WHERE jobs.id = jobitems.jobid
    GROUP BY jobs.id, jobs.name
    ORDER BY jobs.name

-- 
Knut Anders