You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Christopher Chisholm <ch...@syamsoftware.com> on 2010/06/12 00:22:23 UTC

query works on 10.2, feezes with high CPU on 10.3 and above

Hello,

I've had derby 10.2.2.0 database backing a java application for some 
time now.  The following query has always worked with a fairly large 
database in about 6-8 seconds on my machine:

SELECT A.Name, A.Version, A.Vendor, count(*) from Applications A INNER 
JOIN ManagedSystems M ON M.ManagedSystem_ID = A.ManagedSystem_ID WHERE 
M.Active=1 AND M.ManagedSystem_ID  IN (SELECT DISTINCT(ManagedSystem_ID) 
FROM Applications WHERE upper(Name) LIKE '%MICROSOFT%') GROUP BY A.Name, 
A.Version, A.Vendor ORDER BY upper(A.Name), upper(A.Version)

I have recently upgraded to a derby 10.5.3.0.  This same query no longer 
completes.  Instead, it seems the CPU usage of the process that runs the 
query shoots up to 50-80%, and never completes (i've left it for 
hours).  Any ideas why?

I am using the same exact database, the same java version, and my code 
hasn't changed.  Nothing in the query has changed.

I have the same problem on 10.6.1.0.  I traced it back and found that 
even 10.3.3.0 has this problem. 

I've been scratching my head on this one for two days, any help would be 
greatly appreciated!

-chris

Re: query works on 10.2, feezes with high CPU on 10.3 and above

Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Christopher Chisholm <ch...@syamsoftware.com> writes:
> I have recently upgraded to a derby 10.5.3.0.  This same query no
> longer completes.  Instead, it seems the CPU usage of the process that
> runs the query shoots up to 50-80%, and never completes (i've left it
> for hours).  Any ideas why?

It could be that the optimizer has changed and is now picking a bad
query plan, so the query takes a very long time to complete.

If you can run with -Dderby.language.logQueryPlan=true and attach the
query plans you see (in derby.log) for 10.2 and 10.6, we could compare
and see what changed if anything. 
Try to make the slow query complete by letting run overnight ;-)

See also http://wiki.apache.org/db-derby/PerformanceDiagnosisTips

If you can produce a sample database which shows the slowdown and
enclose that to a Derby JIRA issue, that would be even better and
allow us to speed ut detection of the issue.

For how to file  a Derby bug report, cf.
http://db.apache.org/derby/DerbyBugGuidelines.html

Thanks,
Dag