AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren

SQL beschleunigen

Ein Thema von Metallicwar · begonnen am 8. Nov 2012 · letzter Beitrag vom 8. Nov 2012
Antwort Antwort
Metallicwar

Registriert seit: 5. Feb 2010
Ort: Bad Kissingen
293 Beiträge
 
Delphi XE Architect
 
#1

SQL beschleunigen

  Alt 8. Nov 2012, 15:14
Datenbank: ADS • Version: 10 • Zugriff über: Delphi
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?
  Mit Zitat antworten Zitat
Benutzerbild von Union
Union

Registriert seit: 18. Mär 2004
Ort: Luxembourg
3.487 Beiträge
 
Delphi 7 Enterprise
 
#2

AW: SQL beschleunigen

  Alt 8. Nov 2012, 15:48
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.
Ibi fas ubi proxima merces
sudo /Developer/Library/uninstall-devtools --mode=all
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#3

AW: SQL beschleunigen

  Alt 8. Nov 2012, 15:54
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)
Gruß, Jo

Geändert von jobo ( 8. Nov 2012 um 15:58 Uhr) Grund: präzisiert
  Mit Zitat antworten Zitat
Benutzerbild von Union
Union

Registriert seit: 18. Mär 2004
Ort: Luxembourg
3.487 Beiträge
 
Delphi 7 Enterprise
 
#4

AW: SQL beschleunigen

  Alt 8. Nov 2012, 16:16
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
Ibi fas ubi proxima merces
sudo /Developer/Library/uninstall-devtools --mode=all
  Mit Zitat antworten Zitat
Metallicwar

Registriert seit: 5. Feb 2010
Ort: Bad Kissingen
293 Beiträge
 
Delphi XE Architect
 
#5

AW: SQL beschleunigen

  Alt 8. Nov 2012, 16:22
Super, besten Dank für die Antworten.
Probier ich alles gleich mal aus.
Ich melde mich wieder !!!
  Mit Zitat antworten Zitat
Metallicwar

Registriert seit: 5. Feb 2010
Ort: Bad Kissingen
293 Beiträge
 
Delphi XE Architect
 
#6

AW: SQL beschleunigen

  Alt 8. Nov 2012, 16:46
@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)
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#7

AW: SQL beschleunigen

  Alt 8. Nov 2012, 17:01
Da muss glaub ich noch ein "Else [Epression/Constante]" rein.
Gruß, Jo
  Mit Zitat antworten Zitat
Benutzerbild von Union
Union

Registriert seit: 18. Mär 2004
Ort: Luxembourg
3.487 Beiträge
 
Delphi 7 Enterprise
 
#8

AW: SQL beschleunigen

  Alt 8. Nov 2012, 17:08
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
Ibi fas ubi proxima merces
sudo /Developer/Library/uninstall-devtools --mode=all
  Mit Zitat antworten Zitat
Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 09:18 Uhr.
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz