[SQL] Abfrage tunen

[omata]

@Igotcha:
Schön das es dir geholfen hat.
Ich bin erstaunt, dass MySQL endlich Views kann - super!
Das eine etwas komplexere Abfrage nicht als View erstellt werden kann zeigt scheinbar die Wirklichkeit von MySQL-Views, schade. Alternative ist vielleicht eine Stored Procedure?
In MSSQL gibt es für solche Probleme Benutzerdefinierte Funktionen die kann man dann wie Tabellen/Views in einer Abfrage benutzen (nur mit noch komplizierterem Innenleben).

Ich benutzte als ersten Schritt gern LEFT JOINs zum Optimieren von Abfragen. Wenn das nicht reicht, kommen die von alzaimar angesprochenen temporäreren Tabellen (aber nur wenn es nicht anders geht, ich finde SQL-Abfragen schöner als serverseistige Prozeduren/Funktionen)

Bei MySQL besonders < 4.1 bieten sich LEFT JOINs sehr an, weil es dort keine Unterabfragen gibt. In LEFT JOINs werden die Tabelleninhalte unabhängig voneinander erstellt. Wenn man also mehr als einen LEFT JOIN hat, können diese wirklich parallel ausgeführt werden. Das bringt wirklich sehr schnelle Ergebnisse (wenn das DB-System Parallelarbeit unterstützt).
Viele werden das jetzt bestimmt wieder anders sehen. Diese Rumnörgler kann ich nur bedauern und sage einfach: Ok mach weiter mit deinen langsamen Abfragen oder unübersichtlichen Stored Procedures.

Die CASE-Verteiler sind der eigentliche Trick. Du holst dir die gesamte Tabelle und schaltest die Inhalte der Spalten, die addiert werden sollen nur dann frei, wenn das Ergebnis in die Zählung einfliessen soll. Wenn nicht, wird einfach eine Nul dazuaddiert. Das geht wesentlich schneller und es ist nur ein Abfrage.

Und noch eins: Bei MySQL < 4.1 kann man mit LEFT JOINs sehr schön Unterabfragen simullieren.
Warum < 4.1? naja, wird noch oft benutzt und ist noch super schnell (im Vergleich zu MySQL >= 4.1)

@alzaimar:
Sehe ich genauso, es geht nichts über MSSQL, die Datenbank ist einfach nur genial.
(diese Geschwindigkeit ist einfach ein Traum)

Allerdings muss ich sagen habe ich auch schon den MSSQL-Server an seine Grenzen geführt.
Fehler: Sie haben mehr als 256 Tabellen im Zugriff - Anweisung beendet.
Ups, das war nicht nett. Aber ich habe es trotzdem gelöst


EDIT: @Igotcha

so habe mich nochmal damit beschäftigt und mir MySQL5 installiert.
Die Unterabfragen in den LEFT JOINs mag MySQL nicht in einem View. Lösung: zerlege meine Abfrage und mach aus den Unterabfragen auch Views...

View_Left1

SQL-Code:

CREATE ... VIEW view_left1 AS 
SELECT monat, jahr, projektid,
       SUM(plan) AS plank,     
       SUM(prognose) AS progk 
FROM pim_pb_kue
WHERE ident = 'KOST'
GROUP BY monat, jahr, projektid

View_Left2

SQL-Code:

CREATE ... VIEW view_left2 AS 
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

View_Main

SQL-Code:

CREATE ... VIEW view_main AS 
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 view_left1 k
  ON     b.monat = k.monat
     AND b.jahr = k.jahr
     AND b.projektid = k.projektid
LEFT JOIN view_left2 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

So sollte das gehen.
Jetzt bin ich nur mal auf die Geschwindigkeit gespannt

MfG
Thorsten