Thanks for all your replies: The major problem is, that with firebird 2.1.3 the query also completes within 3 seconds. Something seems to have changed with the optimizer, execution plan or whatever (~ sorry I dont know the terms here) related to sorting, or double sorting, or sorting within "sub"-queries.
BR Matthias On Fri, Jan 24, 2020 at 3:53 PM Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] < firebird-support@yahoogroups.com> wrote: > > > Hi > > > > „select first(8) ID from TBL_TEST > > where ID not in (select first(2) ID from TBL_TEST order by ID desc) > > order by ID desc” > > > > few things: > > > > Just hint but „first” is not function First(8) is same as First 8 😉 > > > > You should avoid NOT IN queries and use EXISTS/NOT EXISTS instead. > But if you really need it then such subquery should be well indexed. > > > >> „Is there a way to force limiting the result set before sorting it?” > > > > Create descending index on ID column, than this subquery will go throught > index without sorting. > > > > It will be instant i suppose > > > > Regards, > > Karol Bieniaszewski > > > > >
