ORDER in Unterselect sehr langsam

[Gruber_Hans_12345]

also das

SQL-Code:

select p.id
  , (select tb.auftragid from terminal_buchungen tb
     where tb.personalid = p.id and
           tb.buchung = (select max(tbs.buchung) from terminal_buchungen tbs where tbs.personalid = p.id)
     ) as auftragid
  from personal p

verwendet

Code:

PLAN (TBS ORDER TERMINAL_BUCHUNGEN_BUCHUNG INDEX (TB_PERSONALID))
PLAN (TB INDEX (TB_BUCHUNG))
PLAN (P NATURAL)

also verwendet alle Indexe, braucht aber nun fast 9 sekunden


dieses Command

SQL-Code:

select p1.id, TB1.auftragid
from personal p1
join TERMINAL_BUCHUNGEN TB1 on TB1.personalid=p1.id
where
tb1.buchung in
  (select max(tb2.buchung) from TERMINAL_BUCHUNGEN tb2
   where (tb2.personalid=p1.id) and (tb2.auftragid=tb1.auftragid));

verwendet PLAN

Code:

PLAN (TB2 ORDER TERMINAL_BUCHUNGEN_BUCHUNG INDEX (TB_PERSONALID, TB_AUFTRAGID))
PLAN JOIN (P1 NATURAL, TB1 INDEX (TB_PERSONALID))

braucht aber fast 14 Minuten ...



ich habe mal folgendes probiert

SQL-Code:

set term #;

execute block
returns (ID INTEGER, MBID INTEGER)
as
declare variable ID1 INTEGER;
begin
  for execute statement
    'select ID FROM PERSONAL' into ID1
  DO begin
    ID = ID1;
    MBID = null;
    execute statement 'SELECT FIRST 1 TB.AUFTRAGID FROM TERMINAL_BUCHUNGEN TB WHERE TB.PERSONALID = ' || ID || ' ORDER BY TB.BUCHUNG DESC' INTO MBID;
    suspend;
  end
end

#
set term ;#

das braucht "nur" mehr 1.5 sekunden, also immer noch nicht wirklich schnell.

Code:

select count(*) from personal -> 268
select count(*) from Terminal_Buchungen -> 321937

Kann es sein, das es nicht mehr schneller geht diese Abfrage?