Thank you very much for your input on this.

Set, you now bring me to my next question. I have another query
which does a NOT IN using a subquery. The outer table has about 2000
rows and the inner table has about 32000.

As you can imagine my query runs dog slow and I was going to ask
what was the best way to optimise it. I tried using an inner join
with != and much as I though, that was a crazy idea and would lead
to the same if not longer execution time. I see you run a query like:

select TABLE_A.field1
from TABLE_A
where not exists (SELECT * FROM TABLE_B WHERE TABLE_A.joinfield =
TABLE_B.joinfield)

Is this the best way to run such a query in firebird?

Also, what are the plans for the next FB release and how high a
priority is plan optimisation?

Thanks again :-)

Giulio



--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> --- In firebird-support@yahoogroups.com, "Arno Brinkman" wrote:
> > Hi,
> >
> > > > 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.
> > >
> > > The optimizer could rewrite the first query into the second,

but

> > > doesn't. Arno might explain why ;-)
> >
> > Well, certainly in this case they are complete different queries?
> > Would not be good if engine would rewrite query1 into query2 ;-)
>
> Maybe we could introduce a new SQL extention:
>
> select TABLE_A.field1
> from TABLE_A
> where TABLE_A.joinfield not in
> (select distinct joinfield from TABLE_B)
> USING UPSCENE OPTIMIZATION [WITHOUT BAMSEMUMS]
>
> which created such funny results?
>
> On a more serious note, why does the optimizer care about DISTINCT
> when using subselects with [NOT] IN at all? Anyway, it is strange

SQL

> and I think I will continue using
>
> select TABLE_A.field1
> from TABLE_A
> where not exists (SELECT * FROM TABLE_B WHERE TABLE_A.joinfield =
> TABLE_B.joinfield)
>
> for such queries.
>
> Set