>> Conclusion: using select 1 ... instead of select * ... in the sub-select doesn't
>> improve anything, while using select first(1) ... even slows things down a bit.
>> IMHO, that proves FB handles the sub-selects in an efficient manner, i.e.
>> doesn't retrieve irrelevant data and exits the sub-select as soon as the first
>> record arrives.

> Your test has only proved that the EXISTS predicate yields consistent performance
> regardless of whether SELECT 1 or SELECT * is used.

> Your test has NOT proved that FB handles sub-selects in an efficient manner. If
> you were to use the IN() predicate (as many first time FB users do and some ER
> frameworks) instead of EXISTS() you would find that a very different story.

Actually, Sean, I think Firebird in many situations turns IN() into EXISTS. However, if you e.g. use EXISTS(SELECT DISTINCT 1...) things start to run slow (for me it took half a minute as compared to a fraction of a second without DISTINCT - and that was only about one million rows).

Set