Den 05.11.2015 12:12, skrev Josef Kokeš j.kokes@...
[firebird-support]:

> Hi!
>
> I wonder: Was there any change in FB 2.5.4 which would slow down
> processing of WHERE field IN (subselect) a lot, compared to FB 2.5.3? I
> have been performing tests because a user of mine complained about slow
> speed of my database, and found out that my query in the form of:
>
> SELECT COUNT(*)
> FROM a_complex_view
> WHERE field1=1 AND field2<>2
> AND key_field IN (SELECT key_field FROM key_field_list)
>
> went from 2 seconds in FB 2.5.0-2.5.3 to 4 minutes in FB 2.5.4. After
> rewriting the last condition to EXISTS:
>
> SELECT COUNT(*)
> FROM a_complex_view
> WHERE field1=1 AND field2<>2
> AND EXISTS (SELECT key_field FROM key_field_list WHERE
> key_field=a_complex_view.key_field)
>
> the speed returned to 2 seconds even in FB 2.5.4. But what could be the
> cause? I went over all changes described in the 2.5.4 Release Notes and
> couldn't find anything which would seem relevant.
>
> Thanks,
>
> Josef

I generally stopped using IN <subselect> shortly after I started using
Firebird, version 0.9.4. Sure, the handling of IN <subselect> have
improved considerably since then, but knowing that it will at best be as
efficient as EXISTS and at worst considerably worse (like your case or
even worse), I've never seen a good reason for using IN <subselect>.

Nevertheless, I am surprised about the difference between 2.5.3 and
2.5.4 that you report. Your subselect is not correlated to the main
select (i.e. you have no reference to a_complex_view inside your
subselect), so in theory I'd expect it to be possible for the optimizer
to change it to an EXISTS as part of its optimization (or isn't
key_field_list a normal table?).

Set