Mark, thanks for the clarification on this. Makes sense now. Appreciate all the help.



From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Friday, July 24, 2015 9:40 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Existence Predicates NOT IN Question

On 24-7-2015 13:25, 'stwizard' stwizard@... [firebird-support] wrote:
> Greetings All,
>
> In the Firebird 2 Migration & Installation guide
> http://www.firebirdsql.org/file/documentation/release_notes/Firebird-2_1_6-Installation.pdf
> on page 8 under the performance section it states the following:
>
> The following changes should be noted as possible sources of performance
> loss:
>
> Existence Predicates NOT IN and ALL May Be Slow
>
> Firebird and, before that, InterBase, have produced incorrect results
> for the logical existence predicates ALL and NOT IN for many years. That
> problem has been corrected in Firebird2.0, but the change means
> thatindexes on the inner tables cannot be used and performance may be
> slow compared to the same query's performance in V.1.5. “Inner tables”
> are the tables used in the subquery argument inside an ALL or NOT IN
> expression.
>
> So my questions:
>
> This only applies when there is a JOIN in the SQL statement correct?

No, everywhere you use NOT IN or ALL this applies. The documentation is
talking about the inner table materialized by the select inside NOT IN
(select ...). That condition can be replaced with an NOT EXISTS which -
usually - performs better.

Mark
--
Mark Rotteveel





[Non-text portions of this message have been removed]