SQL beschleunigen

[Metallicwar]

Hallo zusammen
habe folgenden SQL:

Code:

   SELECT p.ID, p.PersonalNr, p.Name, p.Vorname, IFNULL(pz.Resturlaubgesamt, 0) as RUL, 
a.Bezeichnung as Abteilung, a.InterneNummer as AbtNr, f.Bezeichnung as Firma, f.FirmenNr,
mr.JAHR, mr.MONAT, IFNULL(ZMIF.MinToIHour(mr.SOLLSTUNDEN - mr.KRANKSTUNDENBEZAHLT - mr.URLAUBSSTUNDENBEZAHLT - mr.FEIERTAGSSTUNDEN),0) as SOLL,
IFNULL(ZMIF.MinToIHour(mr.MO_GLEIT),0) as MGLZ, IFNULL(ZMIF.MinToIHour(mr.AUSBEZAHLT),0) as Ausbezahlt, 
IFNULL(ZMIF.MinToIHour(mr.JAHRESGLEITZEIT),0) as JGLZ, IFNULL(mr.KAPPUNGSKMONATS_GL,0) as MKappGLZ,
IFNULL(mr.KAPPUNGSKJAHRES_GL,0) as JKappGLZ, IFNULL(mr.URLAUBSTAGEBEZAHLT,0)  as UL, 
IFNULL(mr.KRANKENTAGEBEZAHLT,0) as KGA, IFNULL(mr.KRANKENTAGEUNBEZAHLT,0)  as KGU, 
IFNULL(mr.DIENSTREISE,0) as SDR, IFNULL(mr.BERUFSSCHULE,0) as SBS, 
IFNULL(ZMIF.MinToIHour(mr.ISTSTUNDENBRUTTO),0) as Brutto, 
IFNULL(ZMIF.MinToIHour(mr.ISTSTUNDENNETTO),0) as Netto, 
IFNULL(mr.FEIER_TAGE,0) as FT,
IFNULL(ZMIF.MinToIHour(e_100.M),0) as Nachtzuschlag,
IFNULL(ZMIF.MinToIHour(e_110.M),0) as Samstagzuschlag,
IFNULL(ZMIF.MinToIHour(e_120.M),0) as Sonntagzuschlag,
IFNULL(ZMIF.MinToIHour(e_130.M),0) as Feiertagzuschlag,
ZMIF.MinToIHour(IFNULL(sum(b_AR.BSumme),0)) as AR,
ZMIF.MinToIHour(IFNULL(sum(b_PR.BSumme),0)) as PR,
ZMIF.MinToIHour(IFNULL(sum(b_AZK.BSumme),0)) as AZK,
ZMIF.MinToIHour(IFNULL(sum(b_HOM.BSumme),0)) as HOM,
Count(bd.Fehltag) as Fehltage
FROM
Personal p
LEFT OUTER JOIN Firma f ON p.ID_Firma = f.ID 
LEFT OUTER JOIN Abteilungen a ON p.ID_Abteilungen = a.ID 
LEFT OUTER JOIN Monatswerte_Report mr ON p.ID = mr.ID_PERSONAL 
LEFT OUTER JOIN PersonalZusatz pz ON p.ID = pz.ID_Personal
LEFT OUTER JOIN Ergebnis e_100 
                ON (mr.ID_Personal = e_100.ID_Personal AND e_100.Jahr = mr.Jahr 
               AND e_100.Monat = mr.Monat AND e_100.Kontonr = 100)
LEFT OUTER JOIN Ergebnis e_110 
                ON (mr.ID_Personal = e_110.ID_Personal AND e_110.Jahr = mr.Jahr 
            AND e_110.Monat = mr.Monat AND e_110.Kontonr = 110)
LEFT OUTER JOIN Ergebnis e_120 
                ON (mr.ID_Personal = e_120.ID_Personal AND e_120.Jahr = mr.Jahr 
            AND e_120.Monat = mr.Monat AND e_120.Kontonr = 120)
LEFT OUTER JOIN Ergebnis e_130 
                ON (mr.ID_Personal = e_130.ID_Personal AND e_130.Jahr = mr.Jahr 
            AND e_130.Monat = mr.Monat AND e_130.Kontonr = 130)
LEFT OUTER JOIN Buchungen b_AR 
                ON (mr.ID_Personal = b_AR.ID_Personal AND Year(b_AR.Datum) = mr.Jahr 
            AND Month(b_AR.Datum) = mr.Monat and b_AR.Buchungsart = 'AR')
LEFT OUTER JOIN Buchungen b_PR 
                ON (mr.ID_Personal = b_PR.ID_Personal AND Year(b_PR.Datum) = mr.Jahr 
            AND Month(b_PR.Datum) = mr.Monat and b_PR.Buchungsart = 'PR')
LEFT OUTER JOIN Buchungen b_AZK 
                ON (mr.ID_Personal = b_AZK.ID_Personal AND Year(b_AZK.Datum) = mr.Jahr 
            AND Month(b_AZK.Datum) = mr.Monat and b_AZK.Buchungsart = 'AZK')
LEFT OUTER JOIN Buchungen b_HOM 
                ON (mr.ID_Personal = b_HOM.ID_Personal AND Year(b_HOM.Datum) = mr.Jahr 
            AND Month(b_HOM.Datum) = mr.Monat and b_HOM.Buchungsart = 'HOM')
LEFT OUTER JOIN Buchungsdatei bd ON (mr.ID_Personal = bd.ID_Personal and Year(bd.Datum) = mr.Jahr 
            AND Month(bd.Datum) = mr.Monat and Fehltag <> '' and Fehltag <> 'UL' 
            and Fehltag <> 'FT' and Fehltag <> 'KGA' and Fehltag <> 'KGU')
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29

Der SQL dauert vieeeel zu lang, beim Testen im Data Architect stürzt mir dieser sogar immer ab.
Mir ist aufgefallen dass die LEFT JOINS der Buchungsdatei und vorallem die JOINS der Tabelle Buchungen den SQL extrem verlangsamen.
Lasse ich diese JOINS weg, wird mein SQL in 1ner Sekunde ausgeführt:

Code:

SELECT p.ID, p.PersonalNr, p.Name, p.Vorname, IFNULL(pz.Resturlaubgesamt, 0) as RUL, 
a.Bezeichnung as Abteilung, a.InterneNummer as AbtNr, f.Bezeichnung as Firma, f.FirmenNr,
mr.JAHR, mr.MONAT, IFNULL(ZMIF.MinToIHour(mr.SOLLSTUNDEN - mr.KRANKSTUNDENBEZAHLT - mr.URLAUBSSTUNDENBEZAHLT - mr.FEIERTAGSSTUNDEN),0) as SOLL,
IFNULL(ZMIF.MinToIHour(mr.MO_GLEIT),0) as MGLZ, IFNULL(ZMIF.MinToIHour(mr.AUSBEZAHLT),0) as Ausbezahlt, 
IFNULL(ZMIF.MinToIHour(mr.JAHRESGLEITZEIT),0) as JGLZ, IFNULL(mr.KAPPUNGSKMONATS_GL,0) as MKappGLZ,
IFNULL(mr.KAPPUNGSKJAHRES_GL,0) as JKappGLZ, IFNULL(mr.URLAUBSTAGEBEZAHLT,0)  as UL, 
IFNULL(mr.KRANKENTAGEBEZAHLT,0) as KGA, IFNULL(mr.KRANKENTAGEUNBEZAHLT,0)  as KGU, 
IFNULL(mr.DIENSTREISE,0) as SDR, IFNULL(mr.BERUFSSCHULE,0) as SBS, 
IFNULL(ZMIF.MinToIHour(mr.ISTSTUNDENBRUTTO),0) as Brutto, 
IFNULL(ZMIF.MinToIHour(mr.ISTSTUNDENNETTO),0) as Netto, 
IFNULL(mr.FEIER_TAGE,0) as FT,
IFNULL(ZMIF.MinToIHour(e_100.M),0) as Nachtzuschlag,
IFNULL(ZMIF.MinToIHour(e_110.M),0) as Samstagzuschlag,
IFNULL(ZMIF.MinToIHour(e_120.M),0) as Sonntagzuschlag,
IFNULL(ZMIF.MinToIHour(e_130.M),0) as Feiertagzuschlag
FROM
Personal p
LEFT OUTER JOIN Firma f ON p.ID_Firma = f.ID 
LEFT OUTER JOIN Abteilungen a ON p.ID_Abteilungen = a.ID 
LEFT OUTER JOIN Monatswerte_Report mr ON p.ID = mr.ID_PERSONAL 
LEFT OUTER JOIN PersonalZusatz pz ON p.ID = pz.ID_Personal
LEFT OUTER JOIN Ergebnis e_100 
                ON (mr.ID_Personal = e_100.ID_Personal AND e_100.Jahr = mr.Jahr 
               AND e_100.Monat = mr.Monat AND e_100.Kontonr = 100)
LEFT OUTER JOIN Ergebnis e_110 
                ON (mr.ID_Personal = e_110.ID_Personal AND e_110.Jahr = mr.Jahr 
            AND e_110.Monat = mr.Monat AND e_110.Kontonr = 110)
LEFT OUTER JOIN Ergebnis e_120 
                ON (mr.ID_Personal = e_120.ID_Personal AND e_120.Jahr = mr.Jahr 
            AND e_120.Monat = mr.Monat AND e_120.Kontonr = 120)
LEFT OUTER JOIN Ergebnis e_130 
                ON (mr.ID_Personal = e_130.ID_Personal AND e_130.Jahr = mr.Jahr 
            AND e_130.Monat = mr.Monat AND e_130.Kontonr = 130)
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29

Hab schon probiert, das ganze in einen View auszulagern und über folgenden Select Befehl meine Daten zu erfassen:

Code:

SELECT * FROM Vi_KD_MonatsberichtRB
###WHERE### 
ORDER BY 8 desc, 7, 6, 2, 3, 1

Hat jemand einen Tipp für mich, wie ich meinen SQL perfomanter machen kann?
Reihenfolge der JOINS, oder JOINS anderst aufbauen?

[Union]

Naja, zunächst solltest Du natürlich im Architect einen Execution Plan erstellen oder per SP abrufen. Und dann die roten Punkte abarbeiten, z.b. Indexe für die Joinfelder erstellen. Dann hilft es teilweise, intelligent geschachtelte Subqueries oder Views zu nutzen. Wenn alles nichts hilft das Aufteilen mit Temp-Tabellen.

[jobo]

Vorweg:
Ich hab ADS noch nie benutzt.

Ein View macht m.E. kein Unterschied zu einem Select.
Wenn die SuperDuper GUI abschmiert, das Ganze lieber in der Console probieren.

Zu den Buchungen:
Die 4 oder 5 Or Bedingungen im Statement kosten eine Menge Zeit. Du könntest versuchen -sofern die übrigen Kriterien identisch sind- mit einem Buchungsjoin auszukommen und die jeweiligen Where Kriterien in ein Case Statement umzuwandeln, das nur die abweichende Bedingung abfragt und in dem Fall einen berechneten Wert ausspuckt.

also für einen Strang bspw.

Code:

LEFT OUTER JOIN Buchungen b_AZK
                ON (mr.ID_Personal = b_AZK.ID_Personal AND Year(b_AZK.Datum) = mr.Jahr
            AND Month(b_AZK.Datum) = mr.Monat and b_AZK.Buchungsart = 'AZK')

raus und dafür im Select Teil des Statements ungefähr sowas:

Code:

Case .. When [Prüfung] then [expression] else 0 as AZK,

statt des alten

Code:

ZMIF.MinToIHour(IFNULL(sum(b_AZK.BSumme),0)) as AZK,

Die Vorschläge von Union sind eigentlich selbstverständlich, würde ich bis auf die Temptables zuerst versuchen. ( evtl. schwierig, wenn die Umgebung abschmiert)

[Union]

Mit der Temptable ist eigentlich einfach vorher das hier:

Code:

Select ID_PERSONAL,
       Year(Datum) as Jahr,
       Month(Datum) as Monat,
       count(*) as Fehltage
into #bdtemp
from Buchungsdatei
Where isnull(Fehltag, '') not in ('', 'FT', 'KGA', 'KGU')
group by 1,2,3

Dann die Referenz des Join ändern, vorher evtl. Indexe auf die Joins-Felder erstellen:

Code:

Left outer join #bdtemp bd on bd.ID_PERSONAL = mr.ID_PERSONAL and bd.Jahr = mr.Jahr and bd.Monat = mr.MONAT

[Metallicwar]

Super, besten Dank für die Antworten.
Probier ich alles gleich mal aus.
Ich melde mich wieder !!!

[Metallicwar]

@Union:
Hat super geklappt mit der temporären Tabelle. SQL wird in gut 2 sekunden ausgeführt ohne Setzen von Indexen ...


Jetzt probier ich gerade das mit dem Case, bekomms aber leider nicht so hin.

Code:

SELECT p.ID, p.PersonalNr, p.Name, p.Vorname, IFNULL(pz.Resturlaubgesamt, 0) as RUL, 
a.Bezeichnung as Abteilung, a.InterneNummer as AbtNr, f.Bezeichnung as Firma, f.FirmenNr,
mr.JAHR, mr.MONAT, IFNULL(ZMIF.MinToIHour(mr.SOLLSTUNDEN - mr.KRANKSTUNDENBEZAHLT - mr.URLAUBSSTUNDENBEZAHLT - mr.FEIERTAGSSTUNDEN),0) as SOLL,
IFNULL(ZMIF.MinToIHour(mr.MO_GLEIT),0) as MGLZ, IFNULL(ZMIF.MinToIHour(mr.AUSBEZAHLT),0) as Ausbezahlt, 
IFNULL(ZMIF.MinToIHour(mr.JAHRESGLEITZEIT),0) as JGLZ, IFNULL(mr.KAPPUNGSKMONATS_GL,0) as MKappGLZ,
IFNULL(mr.KAPPUNGSKJAHRES_GL,0) as JKappGLZ, IFNULL(mr.URLAUBSTAGEBEZAHLT,0)  as UL, 
IFNULL(mr.KRANKENTAGEBEZAHLT,0) as KGA, IFNULL(mr.KRANKENTAGEUNBEZAHLT,0)  as KGU, 
IFNULL(mr.DIENSTREISE,0) as SDR, IFNULL(mr.BERUFSSCHULE,0) as SBS, 
IFNULL(ZMIF.MinToIHour(mr.ISTSTUNDENBRUTTO),0) as Brutto, 
IFNULL(ZMIF.MinToIHour(mr.ISTSTUNDENNETTO),0) as Netto, 
IFNULL(mr.FEIER_TAGE,0) as FT,
IFNULL(ZMIF.MinToIHour(e_100.M),0) as Nachtzuschlag,
IFNULL(ZMIF.MinToIHour(e_110.M),0) as Samstagzuschlag,
IFNULL(ZMIF.MinToIHour(e_120.M),0) as Sonntagzuschlag,
IFNULL(ZMIF.MinToIHour(e_130.M),0) as Feiertagzuschlag,
IFNULL(bd.Fehltage,0) as Fehltage,
CASE
  when b.Buchungsart = 'AR' then sum(b.BSumme) as AR
END 
FROM
Personal p
LEFT OUTER JOIN Firma f ON p.ID_Firma = f.ID 
LEFT OUTER JOIN Abteilungen a ON p.ID_Abteilungen = a.ID 
LEFT OUTER JOIN Monatswerte_Report mr ON p.ID = mr.ID_PERSONAL 
LEFT OUTER JOIN PersonalZusatz pz ON p.ID = pz.ID_Personal
LEFT OUTER JOIN Ergebnis e_100 
                ON (mr.ID_Personal = e_100.ID_Personal AND e_100.Jahr = mr.Jahr 
               AND e_100.Monat = mr.Monat AND e_100.Kontonr = 100)
LEFT OUTER JOIN Ergebnis e_110 
                ON (mr.ID_Personal = e_110.ID_Personal AND e_110.Jahr = mr.Jahr 
            AND e_110.Monat = mr.Monat AND e_110.Kontonr = 110)
LEFT OUTER JOIN Ergebnis e_120 
                ON (mr.ID_Personal = e_120.ID_Personal AND e_120.Jahr = mr.Jahr 
            AND e_120.Monat = mr.Monat AND e_120.Kontonr = 120)
LEFT OUTER JOIN Ergebnis e_130 
                ON (mr.ID_Personal = e_130.ID_Personal AND e_130.Jahr = mr.Jahr 
            AND e_130.Monat = mr.Monat AND e_130.Kontonr = 130)
LEFT OUTER JOIN Buchungen b 
                ON (mr.ID_Personal = b.ID_Personal AND Year(b.Datum) = mr.Jahr 
            AND Month(b.Datum) = mr.Monat)
LEFT OUTER JOIN #bdtemp bd on bd.ID_PERSONAL = mr.ID_PERSONAL and bd.Jahr = mr.Jahr 
                and bd.Monat = mr.MONAT 
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

Zitat:

ERROR IN SCRIPT: poQuery: Error 7200: AQE Error: State = 42000; NativeError = 2115; [iAnywhere Solutions][Advantage SQL Engine]Expected lexical
element not found: WHEN | ELSE | END [Parsing Expression (column 31 in the SELECT clause)] -- Location of error in the SQL statement is: 1163 (line: 16
column: 34)

[jobo]

Da muss glaub ich noch ein "Else [Epression/Constante]" rein.

[Union]

Ja, und der Feldalias steht an der falschen Stelle, z.b.

Code:

CASE
  when b.Buchungsart = 'AR' then sum(b.BSumme) 
ELSE
  0
END AS Ar