> >> 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.

I have never seen that to be the case.

As I recall, there is a long standing request to have the engine perform that transformation, but that has not done as of yet.


Sean