Hi,
folgendes SQL-Statement bereitet mir erhebliche Kopfschmerzen. Unter Version 7.2 benötigt die Ausführung 5 sec unter 8.1 mehr als 2 min.
Ursache sind so weit ich dies erkennen kann die letzten drei Joins im Statement, aber wie kann ich dies umformulieren, das es unter 8.1 wieder in einer akzeptablen Antwortzeit läuft.
###########
SELECT SUBSTRING(T1.FA_MODELLCODE, 1, 3) AS MG_GRUPPENNUMMER, MG_BEZEICHNUNG, COUNT(*) AS ANZAHL,
SUM( COALESCE(P2009T511.HF_RUECKNAHMEPREIS, P2009T502.BK_RECHNUNGSSUMME, T1.FA_CIF) ) AS PREIS,
( CURRENT_DATE - DATE( COALESCE(p2009t511.hf_ruecknahmedatum, p2009t502.bk_rechnungsdatum, t1.create_date) ) - 1 ) / 30 AS MONATE
FROM P2009T509 t1
LEFT JOIN P2009T215 ON SUBSTRING(T1.FA_MODELLCODE, 1, 3) = P2009T215.MG_GRUPPENNUMMER
INNER JOIN P2009T504 ON P2009T504.FA_ID = T1.FA_ID AND P2009T504.KO_ART = '0'
LEFT JOIN ( SELECT Y510.* FROM P2009T510 Y510 INNER JOIN ( SELECT MAX(T510.FK_ID) AS FK_ID, T510.FA_ID FROM P2009T510 T510 GROUP BY T510.FA_ID ) Z510 ON Y510.FA_ID = Z510.FA_ID AND Y510.FK_ID = Z510.FK_ID ) P2009T510 ON P2009T510.FA_ID = T1.FA_ID
LEFT JOIN ( SELECT Y502.* FROM P2009T502 Y502 INNER JOIN ( SELECT MAX(T502.BK_ID) AS BK_ID, T502.FA_ID FROM P2009T502 T502 GROUP BY T502.FA_ID ) Z502 ON Y502.FA_ID = Z502.FA_ID AND Y502.BK_ID = Z502.BK_ID ) P2009T502 ON P2009T502.FA_ID = T1.FA_ID
LEFT JOIN ( SELECT Y511.* FROM P2009T511 Y511 INNER JOIN ( SELECT MAX(T511.HF_ID) AS HF_ID, T511.FA_ID FROM P2009T511 T511 GROUP BY T511.FA_ID ) Z511 ON Y511.FA_ID = Z511.FA_ID AND Y511.HF_ID = Z511.HF_ID ) P2009T511 ON P2009T511.FA_ID = T1.FA_ID
WHERE t1.fa_status <> 'BuyBack' AND t1.fa_status <> 'Verkauft'
AND (P2009T510.fk_status IS null OR P2009T510.fk_status <> '0')
AND P2009T502.bk_id is null
##########
Jede Hilfe ist recht!!!!
Im voraus vielen Dank
Kurt

