Subject RE: [firebird-support] performance problem with subquery and "not in" in fb 2.0!!!!
Author Alan McDonald
> Hello all,
> the following simple query doesn't cause any performance problems in
> firebird 1.5 but in firebird 2.0.1 it takes a very long time to get a
> result set:
> select sd_id from service_data
> where sd_id not in
> (select sd_id from service_resource_contents)
> The column sd_id is primary key in table service_data and foreign key
> in service_resource_contents (service_data is rferenced).
> The execution plan in fb 1.5 shows an indexed access, in fb2.0 a
> natural join on table service_resource_contents!
> It seems to be a general problem with "NOT IN", because the usage
> of "IN" is fast!
> Can anybody help?
> thank, regards, Christoph

IN can be short circuited, but NOT IN has to be fully searched.
I suspect IN could also be slow in the odd cases where a full search is
needed to return the result.