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