db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dag.wan...@oracle.com (Dag H. Wanvik)
Subject Re: Speeding up Derby for very large queries?
Date Mon, 06 Aug 2012 16:39:11 GMT
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

Mime
View raw message