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?