SELECT *
FROM "Inventory" i
WHERE i.id IS NOT NULL
AND NOT EXISTS(SELECT * FROM "InventoryGroupsTL"
WHERE i.id = "InventoryGroupsTL"."InventoryID")

It should be considerably quicker than 13 minutes (though it may not beat 0.08 seconds). In theory, you may also have to add something like 'AND NOT EXISTS(SELECT * FROM "InventoryGroupsTL" WHERE "InventoryGroupsTL"."InventoryID" IS NULL)' for it to be completely identical to your original NOT IN statement, but you only want this if you want an empty result set in case of a record having "InventoryGroupsTL"."InventoryID" being NULL.

Another way to write this (not taking into account the 'theoretical addition' above) would be

SELECT i.*
FROM "Inventory" i
left join "InventoryGroupsTL" ig on ig."InventoryID" = i.id
WHERE ig."InventoryID" IS NULL

Just so that you have a few alternatives...

HTH,
Set

rymarkham 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?
>
> When I try force the plan I get an error
> "Token unknown - line 5, column 1.
> PLAN."
>
> SELECT *
> FROM "Inventory" I
> WHERE I.id NOT IN (SELECT "InventoryID" FROM "InventoryGroupsTL")
> PLAN ("InventoryGroupsTL" INDEX ("PK_InventoryGroupsTL"))
> PLAN (I NATURAL)
>
>
> Thanks
> Roy

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