Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Date: Fri, 27 Jun 2008 17:10:36 +0200
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
Subject: Re: Pathologically poor query performance - advice required
In-reply-to: <4864374B.9040803@papercut.com>
Sender: Knut.Hatlen@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <wjo8bq1mq5g3.fsf@khepri41.norway.sun.com>
Organization: Sun Microsystems
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
References: <4861D93B.9060900@papercut.com> <48626EEF.5060709@gmail.com>
 <4862F8AC.80406@papercut.com> <4863B41B.10208@sbcglobal.net>
 <4864374B.9040803@papercut.com>
User-Agent: Gnus/5.110011 (No Gnus v0.11) Emacs/22.2 (usg-unix-v)

Matt Doran <matt.doran@papercut.com> writes:

>> To get feedback from people it may also help to post the ddl of the
>> tables in the query, including the indexes/constraints.  I know you
>> provided the db, so with work it is available - but the more up front
>> info you provide the more help possible.
> I've attached the database creation script if anyone is interested.
>>
>> One other suggestion might be to try the query once with no optimizer
>> timeout to see if it would have found a better plan:
>> http://wiki.apache.org/db-derby/PerformanceDiagnosisTips
>>
> I tried disabling the optimizer timeout, it had not effect.  It was
> the maxMemoryPerTable that had the impact here.

Another thing that may affect which plan the optimizer chooses, I think,
is the size of the Java heap. Also, when the optimizer checks how much
memory it has available, it looks at the current size of the Java heap,
not at how much the heap is allowed to grow. So say you've allowed the
heap to grow to 512MB by specifying -Xmx512M on the command line, Derby
doesn't see that it has that amount of memory available until the heap
has actually grown to that size. And the heap doesn't normally grow
until garbage collection cannot free enough memory. To ensure that Derby
always sees how much memory it has available, also set the minimum heap
size with -Xms. Not sure if it helps in your case, but it might be worth
a try.

-- 
Knut Anders