Hi,

>> > 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:
>
> PLAN (CUSTOMERS NATURAL)
> PLAN (CUSTOMERS NATURAL)

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.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
http://www.firebirdsql.org
http://www.firebirdsql.info

Support list for Interbase and Firebird users:
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep:
news://newsgroups.firebirdsql.info