Yes because aggregate functions are usually expensive, they could be collected to separate array, and each different function would be executed only once, and the result could be used in select expressions. In fact I implemented this into a database system that I once wrote. It didn't have a common subexpression evaluation, but only this. A general common subexpression evaluation would be even better.
It seems that there is quite a lot to do in optimizations and elsewhere in Derby. I'm still impressed about it, I like Derby much more than HSQL. It would be great to get into developing Derby, but of course the learning curse must be steep.
--
Harri
Harri Pesonen wrote:
> Thanks for you comments and for creating a JIRA item. I think that the
> first case is a separate thing, because the following is slow as well:
>
> SELECT MIN(Id), MIN(Id) + 1 FROM Customer
>
> This case could be optimized to do only one MIN-traversal. I think
> that this is a more fundamental thing, perhaps requiring more thought,
> and some kind of global optimizations.
This goes into another optimization that Derby could use... Common
_expression_ evaluation, which would evaluate common parts of expressions
only once. Yet another enhancement request? :-)
For your original problem with min() and max() in one query, you may be
able to use the following query as a work-around.
Select min(myid), max(myid) from (select min(id) from Customer union all
select max(id) from Customer) myTab(myid);
Something like this could also be used for this new query... making
subquery evaluate the min() once and outer query could select both min()
and min()+1 output. Instead of Derby recognizing this common _expression_
evaluation, you are doing it by hand. Yes, I agree, Derby should be able
to do this... reasonably easily.
Satheesh
