TXVanguard <[email protected]> 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
