db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Wilson <ch...@aptivate.org>
Subject Re: SQL performance with complex query
Date Sat, 17 Apr 2010 16:41:57 GMT
Hi Bryan and all,

On Sat, 17 Apr 2010, Bryan Pendleton wrote:

> > I think this could be a case where the query optimizer could use some work?
> > Is it useful for me to file a bug report somewhere, e.g. in JIRA? 
> 
> Yes, it would be particularly helpful if you could provide a complete
> standalone test case which demonstrates the problem.

I believe I have done so; the link that I provided in my email to the 
sources of the test case from Subversion should run on any Unix system 
with Bash, Sun Java and the Derby libraries available. It has four source 
files (a shell script and three SQL scripts) and a copy of the Derby 
database to save you developers the time of reloading the fixture data 
used in the test.

> If that's not possible, then perhaps you could gather query plan output 
> and post that; perhaps the Derby optimizer is choosing a particularly 
> poor query plan.

OK, this is interesting. With the default JVM settings, it crashes with 
OOM before outputting the query plan. However, with -Xmx1024m, it 
finishes in reasonable time, and does output the plan, which I've 
attached.

I guess that means that the optimiser is just taking a lot of memory to 
optimise the query, and it spends forever in GC before finally hitting OOM 
and giving up when using the default settings? Does this bear looking 
into? Should I file a JIRA issue?

Also the recommended page at 
(http://wiki.apache.org/db-derby/PerformanceDiagnosisTips) contains a 
broken link to "Working with Derby properties" 
(http://db.apache.org/derby/docs/dev/tuning/ctunsetprop34818.html) which 
is currently 404.

Cheers, Chris.
-- 
Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES

Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.
Mime
View raw message