Den 05.11.2015 12:12, skrev Josef Kokeš j.ko...@apatykaservis.cz [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
