donjules2k wrote:

>With firebird, why is that this:
>
>select TABLE_A.field1
>from TABLE_A
>where TABLE_A.joinfield not in (select distinct joinfield from TABLE_B)
>
>runs much much much slower than
>
>SELECT TABLE_A.field1
>from TABLE_A
>inner JOIN TABLE_A on TABLE_A.joinfield = TABLE_B.joinfield
>
>which is lightning quick. What I think is happening is that the sub
>query is being run for every row in the outer query, hence making it
>slow. But if that is true, why does it not just run the subquery once
>and then apply the results?
>
>Any ideas why this might be so?
>
>Thanks
>
>Giulio
>
>

Giulio,

This is a know issue in FB.

This kind of query is "transformed" internally in this:

select
TABLE_A.field1
from
TABLE_A
where
not exists (select distinct joinfield from TABLE_B where TABLE_B.joinfield = TABLE_A.joinfield)

The best way to is to transform it in a join as you said...

You could remove the distinct from the sub-query, the distinct you force an order on that table to get only distinct values, as far it is transformed into an exists, it will stop at the first value that matchs the criteria, so removing distinct you speed it up, but the fastest way is to use the join.

if TABLE_a has a lot of rows, than the "exists" will be slower than a "join" since it will be evaluated for each row of table_A.

see you !



--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br