db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brett Wooldridge <brett.wooldri...@gmail.com>
Subject Re: SQL performance with complex query
Date Sun, 18 Apr 2010 05:31:37 GMT
Please file a JIRA issue.  I don't think preparing a query plan should
consume that much memory, or generate that much garbage.

Brett

Sent from my iPhone

On Apr 18, 2010, at 1:41, Chris Wilson <chris@aptivate.org> wrote:

> 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.
> <query-plan.txt>

Mime
View raw message