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: domain of dag.wanvik@oracle.com
 designates 141.146.126.227 as permitted sender)
From: dag.wanvik@oracle.com (Dag H. Wanvik)
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: Speeding up Derby for very large queries?
References: <34261725.post@talk.nabble.com>
Date: Mon, 06 Aug 2012 18:39:11 +0200
In-Reply-To: <34261725.post@talk.nabble.com> (TXVanguard's message of "Mon, 6
	Aug 2012 08:16:34 -0700 (PDT)")
Message-ID: <x6obmokma8.fsf@oracle.com>
User-Agent: Gnus/5.110017 (No Gnus v0.17) Emacs/24.0.96 (usg-unix-v)
MIME-Version: 1.0
Content-Type: text/plain

TXVanguard <brett.denner@lmco.com> writes:

> Here is an example of part of a typical query (some names have been changed
> to protect the innocent) that I deal with:
>
> This query is actually only a sub-query that is joined by a UNION to an even
> larger sub-query to form a full query.
>
> The full query takes over a minute to run in Derby, but only a few tenths of
> a second in Access.   Is there any hope of speeding up these types of
> queries in Derby, or is Derby simple not sophisticated enough to perform the
> types of optimizations required to speed up complicated, multi-level
> queries? (Please note: this is not an attack on Derby.  Derby has
> applications where it is very useful, but I'm wondering if my particular
> application is simply too much for Derby to handle.)

Hard to say. In general the Derby optimized is cost based, and for the
optimizer to work correctly, the table statistics must be up to date, cf.

http://db.apache.org/derby/docs/10.9/tuning/ctunperfstatistics.html

Even so, sometimes, the chosen execution plan may not be optimal for
somequeries. It is also possible to look at the execution plan and
figure out why it isn't performing optimally. 

http://db.apache.org/derby/docs/10.9/tuning/ttundepth33391.html

If Derby is doing the wrong this, one can sometimes override the
optimizer's decisions:

http://db.apache.org/derby/docs/10.9/tuning/ctundepthoptover.html

Good luck!

Thanks,
Dag