Subject Re: Slow IN operator
Author Svein Erling Tysvær
I'll start by answering question 2:

Once for every row in table1.

Well, I do not know whether this is completely correct, but the
general performance of using IN <subselect> has made me avoid it
completely. Generally, I use

where exists(select * from table2 where table2.field2 = table1.field1)

rather than

where table1.field1 in (select field2 from table2)

This avoids the need of selecting every record from table2 for each
record in table1.

Newer versions of Firebird are better at optimizing than version 1.0
was and may internally replace IN with EXISTS in many cases (and
hence, many queries will have identical performance), but I find using
EXISTS as simple and readable as using IN, so I won't switch back
unless it becomes a company policy.


--- In, "jasajona" wrote:
> Hello,
> I use something like this:
> ...
> Where field1 in (select field2 from table2)
> table2 has about 50 records. Why does it so dramaticly slow down
> query, even if I write all these values without doing subselect.
> Next question. If subselect is constant (like in my example), how
> many times firebird executes it?