optimization time can definitely be a problem with derby, anything you
can do to make it easier on the optimizer probably helps your
application a lot.
I still don't understand the correlation of running full GC to the
optimizer issues.
Logically the optimizer is meant to self regulate, I think it might
set some max expected query time at the start of it's loop and then
replace it as it evaluates each query plan. It tries to only
run as long as it estimates the query is going to run. There have
definitely been problems in the past with many way joins where a
slightly bad estimate muliplied many times over can lead to optimizer
thinks the query is going to take way longer that it actually does and
thus leading it to spending more time in the optimize phase. The problem
is that those estimates have grown out of date with faster and faster
machine. the amount of work in the optimizer is something like N!
where N is the number of tables + indexes (including constraints,
primary keys, other indexes) on the tables in a query.
As you have said I think your application is in new area compared to
most derby applications. I would say most applications compile
queries once and execute them multiple times in the kind of high
utilization environment you are describing.
