[SQL] Abfrage tunen

[Igotcha]

Hallo zusammen,

für einen Bericht muss ich die folgenden Daten aus mehreren Tabellen auslesen / berechnen. Momentan benutze ich im "ersten Schuß" folgende Abfrage, um einen VIEW zu erstellen, in dem anschließend die Daten "berichtskonform" ausgewertet werden (zweite SQL-Abfrage mit Berechnungen aus den Daten des Views, die dann so im Bericht verwendet werden):

SQL-Code:

SELECT b.projektid, p.projektname, 
# Umsatz
(SELECT SUM(ki.vorjahr+ki.jan) FROM pim_p_ist ki WHERE ki.zeile 
IN (61500,61532,61536,61547,61543,61526,61524,61512,61515) AND ki.jahr=b.jahr 
AND ki.pagid=b.projektid
GROUP BY ki.pagid) AS umsatz,
b.prsituation, b.cbzuag, b.cbengag, 
b.cbrifa, b.cbriman, 
# Plankosten
(SELECT SUM(k.plan) FROM pim_pb_kue k WHERE k.ident='KOST' AND k.monat=b.monat AND k.jahr=b.jahr 
AND k.projektid=b.projektid
GROUP BY projektid) AS plank,
# prognosekosten
(SELECT SUM(k.prognose) FROM pim_pb_kue k WHERE k.ident='KOST' AND k.monat=b.monat AND k.jahr=b.jahr 
AND k.projektid=b.projektid
GROUP BY projektid) AS progk,
# Istkosten
(SELECT SUM(ki.vorjahr+ki.jan) FROM pim_p_ist ki WHERE ki.zeile 
IN (23112,23132,23135,23162,23165,23170,23183,24753,34512,34832,45012,23182) AND ki.jahr=b.jahr 
AND ki.pagid=b.projektid
GROUP BY ki.pagid) AS istk,
# GAAP-Kosten(stunden)
(SELECT SUM(ki.vorjahr+ki.jan)*73 AS ikststd FROM pim_p_ist ki WHERE ki.zeile=14000 AND ki.jahr=b.jahr 
AND ki.pagid=b.projektid
GROUP BY ki.pagid) AS istkstd,
p.ende AS planende, b.ende AS progende
FROM pim_pb_berichte b
JOIN pim_p_projekteneu p ON b.projektid = p.projektid
WHERE p.projektstatus = -1 AND b.jahr=2006 AND b.monat=1
ORDER BY p.projektname ASC

Diese Abfrage, bzw. das Erstellen des Views, dauert ca. 13 Sekunden auf meinem Testrechner. Da ich nicht der SQL-Crack bin, möchte ich fragen, ob hier noch Optimierungspotential (aus SQL-logischer Sicht) besteht?

Viele Grüße
Igotcha

[Emilio]

Hi Igotcha,

mein erster Gedanke : Was Wunder!
mein zweiter: wie oft brauchst Du diese Abfrage? Wenn's 1 mal im Monat wäre, würde ich mich freuen, dass es überhaupt funktioniert und es so belassen. Brauchst Du den Bericht deutlich häufiger, würde ich prüfen, ob ich mehrere "kleinere" Datasets verwenden kann, welche im Hintergrund per timer oder nach table.append-Ereignissen aktualisiert werden (i.b. die rechenintensiven Abfragen) und die Teilergebnisse für die "Mammutabfrage" quasi schon vorliegen. Das sollte Dir ein paar Sekündchen bringen.

Ich hatte vor Jahren ein ähnliches Problem in einer Access-DB. Dort hat die Ausführung einer Abfrage 45sec gedauert. Ich habe das damals mit einem Kontenabgleich gelöst, allerdings verbunden mit dem Nachteil, dass die abgeglichenen Datensätze nicht mehr geändert werden konnten/durften.

VG
Emilio

[Igotcha]

Hallo Emilio,

na wie das so ist, soll die Auswertung wann immer und wo immer abgerufen werden können

Insgesamt werden diese Abfrage bzw. diesen Bericht 6 Leute aufrufen, aber das i.d.R. nicht zeitgleich und nicht jeden Tag, so dass man mit den 13+ Sekunden schon leben kann.

Mir gings mehr um die Technik, ob da ein "oh Gott, was hast Du getan?" drin steckt und ob man das Ganze noch rein syntaxtechnisch noch optimieren könnte.

Viele Grüße
Igotcha

[omata]

So, habe mich auch mal dran versucht...

SQL-Code:

SELECT b.projektid, p.projektname,
       ki.umsatz, 
       b.prsituation, b.cbzuag, b.cbengag,
       b.cbrifa, b.cbriman,
       k.plank, 
       k.progk,          
       ki.istk,
       ki.istkstd,
       p.ende AS planende, b.ende AS progende
FROM pim_pb_berichte b
INNER JOIN pim_p_projekteneu p 
  ON b.projektid = p.projektid
LEFT JOIN (SELECT monat, jahr, projektid, 
                  SUM(plan) AS plank,     
                  SUM(prognose) AS progk  
           FROM pim_pb_kue
           WHERE ident = 'KOST'
           GROUP BY monat, jahr, projektid) k
  ON     b.monat = k.monat 
     AND b.jahr = k.jahr
     AND b.projektid = k.projektid
LEFT JOIN (SELECT jahr, pagid,
                  SUM(CASE WHEN zeile IN (61500, 61532, 61536,
                                          61547, 61543, 61526,
                                          61524, 61512, 61515)
                        THEN vorjahr + jan
                        ELSE 0 
                      END) AS umsatz,
                  SUM(CASE WHEN zeile IN (23112, 23132, 23135,
                                          23162, 23165, 23170,
                                          23183, 24753, 34512,
                                          34832, 45012, 23182) 
                        THEN vorjahr + jan 
                        ELSE 0 
                      END) AS istk,
                  SUM(CASE WHEN zeile = 14000 
                        THEN vorjahr + jan 
                        ELSE 0 
                      END) * 73 AS istkstd
           FROM pim_p_ist  
           GROUP BY jahr, pagid) ki
  ON     b.jahr = ki.jahr
     AND b.projektid = ki.pagid
WHERE p.projektstatus = -1 
  AND b.jahr = 2006 
  AND b.monat = 1
ORDER BY p.projektname ASC

Ungetestet.

MfG
Thorsten

[generic]

ihr seit lustig. 13 sekunden zu lange ?

ich glaube nicht das es wert ist da sich noch stunden mit zu beschaeftigen.
aber wenn du unbedingt willst, dann lass dir die ausfuehrungsplaene anzeigen und optimiere dann anhand des plans die indizes.

btw. wenn ich statistiken ueber unsere webserver fahre (diese loggen in den mssql-server) dauert die auswerten 45-60 minuten.

[alzaimar]

Zitat von generic:

ihr seit lustig. 13 sekunden zu lange ?

Ja! Reports sollen gefälligst schnell gehen. Und Alles, was nicht optimal ist, ist Schrott.
Es kommt doch immer auf die Menge der zu durchsuchenden Daten an. 13 Sekunden für ein Aggregat über 1.000.000 Records und diversen Joins ist ok, Aber wenn es nur ein paar Tausend Datensätze sind, dann stimmt da was nicht.

Zitat von generic:

ich glaube nicht das es wert ist da sich noch stunden mit zu beschaeftigen.

Wenn man keine Ansprüche hat, nicht. Sonst schon.

Zitat von generic:

aber wenn du unbedingt willst, dann lass dir die ausfuehrungsplaene anzeigen und optimiere dann anhand des plans die indizes.
btw. wenn ich statistiken ueber unsere webserver fahre (diese loggen in den mssql-server) dauert die auswerten 45-60 minuten.

Hat MySQL einen Optimizer? Kann man sich mit MySQL den Execution Plan anschauen?

Erstmal: Sind die Indexe korrekt angelegt?

Ansonsten könnte man mit temporären Tabellen arbeiten. Das geht meistens schneller. Also im Grunde genommen das o.g. Konstrukt nehmen, die einzelnen Subselects in temporäre Tabellen packen und zum Schluss verwursten und die Temp-Dinger wieder wegschmeissen. Ich weiss nicht wieso, aber bei einigen DBMS bringt das was (Vor Allen Dingen MSSQL).

Ansonsten hilft nur eine redundante Summierungstabelle, die über Trigger (kann das MySQL ?) auf dem Laufenden hält. Das ist -nun ja- nicht die reine Lehre, aber in der Praxis Gang und Gäbe.

[Sharky]

Zitat von alzaimar:

... die über Trigger (kann das MySQL ?) ...

Warum sollte er das nicht können?

Was man einmal testen könnte wäre die Abfragen mit dem IN [] zu ersetzen. Ich weiss nicht ob diese optimal genutz werden.

[alzaimar]

Zitat von Sharky:

Zitat von alzaimar:

... die über Trigger (kann das MySQL ?) ...

Warum sollte er das nicht können?

Weil MySQL eigentlich gar nichts kann/konnte, was ein echtes DBMS ausmacht (Stored Procedures, Views, Trigger)... Aber ich läster nur...

[Igotcha]

Zitat von omata:

So, habe mich auch mal dran versucht...

Was soll ich sagen? Wow und danke

0.15 Sekunden Ausführungszeit - das ist ja der Hammer. Muß ich mir mal in Ruhe anschauen, was Du da gezaubert hast. Aber könntest Du vorab mal bitte sagen, was der Hauptgrund sein könnte, die "CASES" in den Aggregation oder die "LEFT"-und "INNER" Joins?

Vielen Dank und Grüße,
Igotcha

[alzaimar]

Eine Frage:
Ist der Code von omata wirklich MySQL? Sieht so nach MSSQL-Dialekt aus... Wenn das wirklich MySQL 5.1 ist, dann würde ich meine Meinung über mySQL durchaus ändern...