As Karol writes, introducing a descending index on the ID field ought to
make your query quick (Firebird indices - and keys - are unidirectional).
Firebird has never been good with IN(<subselect>), I was bitten around
1999, changed to using EXISTS and for the last 20 years I have never
missed IN(<subselect>) (and NOT IN is worse than IN) and it does take
time to consider 324 million tuples (18000*18000).
Your particular query I would have rewritten:
select ID
from TBL_TEST
order by ID desc
rows 3 to 10
but as you write, it is a simplified statement, so
select first 8 t1.ID
from TBL_TEST t1
join TBL_TEST t2 on t1.ID < t2.ID
join TBL_TEST t3 on t2.ID < t3.ID
order by t1.ID desc
or
select first 8 t1.ID
from TBL_TEST t1
where exists( select *
from TBL_TEST t2
join TBL_TEST t3 on t2.ID < t3.ID
where t1.ID < t2.ID )
order by t1.ID desc
or even
execute block returns( ID integer )
as
declare variable ID1 integer;
declare variable ID2 integer;
begin
for select first 2 ID
from TBL_TEST
order by ID desc
into :ID2 do
if ( ID1 is null ) then
ID1 = ID2;
for select ID
from TBL_TEST
where ID not in (:ID1, :ID2) -- not in is OK when used with
constants, just avoid subselects
order by ID desc
do
suspend;
end
may be more appropriate starting points for fixing your real query if
your not satisfied with creating a descending index.
I know nothing about Firebird 2.1.3, maybe that particular version
contains changes that speeds up simple cases like yours but with side
effects that made the developers remove this change later. I just know
that Firebird 0.9.4 convinced me to shun away from (NOT) IN (<subselect>).
HTH,
Set
Den 25.01.2020 07:59, skrev Matthias Winkler spmm...@gmail.com
[firebird-support]:
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 <mailto:liviusliv...@poczta.onet.pl>
[firebird-support] <firebird-support@yahoogroups.com
<mailto: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
