Try this:

select d.sd_id from service_data d
where not exists
(select 1 from service_resource_contents r where d. sd_id=r. sd_id)



Regards,

Paul



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of crizoo4712
Sent: Tuesday, June 26, 2007 12:43 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] performance problem with subquery and "not in"
in fb 2.0!!!!



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





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