Subject Re: [firebird-support] Re: Optimizing a subquery
Author Arno Brinkman

>> > select CustomerId, Sales, (select sum(Sales) from Customers) as Total
>> > from Customers
>> >
>> > It works fine, but for each row, Firebird executes the subquery
>> > "select sum(Sales) from Customers".
>> Are you sure? At one time, I'm quite sure that the compiler/optimizer
>> were good enough to remove loop invariants.
> It possibly has been improved since, but 1.5.3 does not appear to
> optimise it:

You can't see at the PLAN if those queries are invariant or not, but if the sub-query is not depending on outer
relations it should only be run once.
Although this seems not to be the case for sub-queries in the select-list (don't have an idea why at the moment).
You can check the reads from statistics if the query is run only once or for every record.

Arno Brinkman

General database developer support:

Firebird open source database (based on IB-OE) with many SQL-99 features:

Support list for Interbase and Firebird users:

Nederlandse firebird nieuwsgroep: