At 04:36 PM 18/05/2007, you wrote:

>I've having the same hassle, using FB 1.5 takes < 0.08 seconds on FB
>2.0 13+ minutes.
>
>SELECT *
>FROM "Inventory" i
>WHERE i.id NOT IN (SELECT "InventoryID" FROM "InventoryGroupsTL")
>
>Inventory has 50 000 records, InventoryGroupsTL has 49000. How would
>an EXIST sort this out?

EXISTS is an SQL predicate. Your correlation syntax isn't correct
for Fb 2.0.1, anyway; but Fb 2.0.1 now does not use an index for the
"or" testing for a NOT IN (<subquery>) predicate. This change
corrects a logic error in the old 1.5, i.e., that syntax did not
produce a correct result.

Here is how to use the EXISTS() predicate in Fb 2.0.1:

SELECT i.*
FROM "Inventory" i
WHERE NOT EXISTS (
SELECT 1 FROM FROM "InventoryGroupsTL" ig
WHERE ig."InventoryID" = i.id )

You should be aware that correlated subqueries are horribly
inefficient on big sets and "NOTting" them compounds that
inefficiency. If speed of returning the first output is important,
an excluding left join should work a lot faster, e.g.

select i.* from "Inventory" i
left join "InventoryGroupsTL" ig
on ig."InventoryID" = i.id
where ig."InventoryID" is null

./heLen