Helen Borrie wrote:

> 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
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>

Wow thanks all, I would've thought left joins to be slower though.

This code

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

runs now in 0.03 ;) . Thanks Paul! Infact all 3 run close to those
times so I would say they equal, atleast on 50000 records.

I see it now uses the same plan as what FB1.5 used, now that I have a
workaround what was I doing wrong with trying to force the plan? ie
stick to my original code just use the indexed plan?

Thank again
Roy