db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Noll <dan...@nuix.com>
Subject Nested query in conjunction with ordering - unreasonably slow
Date Sun, 03 Feb 2008 23:08:33 GMT
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

Mime
View raw message