[SQL] Abfrage tunen

[Igotcha]

Zitat:

Zitat von 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...

Ich habe das Statement per Copy&Paste in den MySQL-Query-Browser übernommen und ausgeführt.

Wobei ich das Vorurteil gegen MySQL (5.1) eh nicht nachvollziehen kann. Gut, ich arbeite nicht mit Millionen von Datensätzen, sondern nur tausenden pro Tabelle und im aktuellen Projekt mit ~40 Tabellen, aber weder ich, noch einer der 60 Anwender hat sich bisher in irgendeiner Form über die Datenbank(performance) beschwert. Ich benutze darin Views, Stored Procedures, etc. Die Administration der DB ist kinderleicht und das ganze läuft auf einem PIII 650.

Andererseits versuche ich seit 2 Wochen eine Oracle 9i (die kostenlose Entwicklerversion von Oracle) bei mir zu Hause zu Testzwecken - zwecks Alternativeinsatz - bedienen und verstehen zu können ;-)

Gruß Igotcha

[Igotcha]

@omata

Ich bekomme einen Fehler, wenn ich versuche, aus Deiner Abfrage einen VIEW mit

SQL-Code:

CREATE OR REPLACE VIEW pim_pb_testbericht
AS
...

zu erstellen.

"View's SELECT contains a subquery in the FROM clause".

Kann man das noch irgendwie umgehen?

Gruß Igotcha

[alzaimar]

Na, da fangen die Vorurteile ja doch wieder an... Mit MSSQL wäre das nicht passiert... Aber das ist ein anderes Thema.

[generic]

ja man kann natuerlich auch mit dem mysql tunen.

RTFM!

http://dev.mysql.com/doc/refman/5.0/en/query-speed.html

[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 :zwinker:


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 :gruebel:

MfG
Thorsten

[omata]

@Igotcha: schade dass keine Rückmeldung kommt...

[Igotcha]

Zitat:

Zitat von omata

@Igotcha: schade dass keine Rückmeldung kommt...

Entschuldige bitte, dass ich nicht stündlich in die DP schaue - Deine PM fand ich auch etwas unangemessen.

Ich habe mich ausführlich hier für Deine Hilfe bedankt und gerade weil Deine Lösung so gut war von der Performance her, war es dann doch nicht mehr notwendig, daraus VIEWs zu erstellen.

Die Abfrage inklusive kompletter Berichtsgenerierung mit RAVE dauert jetzt ca. 1,5 Sekunden und kann somit von jedem Client "on-the-fly" durchgeführt werden.

Nochmals, vielen Dank!

Viele Grüße
Igotcha

[omata]

Dein letztes Kommantar war...

Zitat:

Zitat von Igotcha

Ich bekomme einen Fehler, wenn ich versuche, aus Deiner Abfrage einen VIEW mit ...

Kann man das noch irgendwie umgehen?

dann habe ich geantwortet und nichts mehr gehört.

Zitat:

Zitat von Igotcha

Entschuldige bitte, dass ich nicht stündlich in die DP schaue - Deine PM fand ich auch etwas unangemessen.

Ich habe öfters in dein Profil geschaut um nachzusehen, ob du überhaupt online warst. Und das warst du fast täglich. Heute, nach einer Woche habe ich dir dann mal eine Nachricht geschickt. Und das findest du dann unangemessen?

Das finde ich ziemlich bedauerlich.

Naja, vielleicht sollte ich mich einfach nicht mehr um solche Dinge kümmern.

[Igotcha]

Zitat:

Zitat von omata

Ich habe öfters in dein Profil geschaut um nachzusehen, ob du überhaupt online warst. Und das warst du fast täglich. Heute, nach einer Woche habe ich dir dann mal eine Nachricht geschickt. Und das findest du dann unangemessen?

Nein, aber das:

Zitat:

Von: omata
An: Igotcha
Verfasst am: 07.04.2006, 20:58
Titel: Keine Rückmeldung geben - echt scheisse

Bei der nächsten Frage werde ich mich bestimmt nicht nochmal reinhängen.

Musste ich mal loswerden.

Brauchst nicht antworten, Thema ist für mich erledigt.

Ich habe mich bereits mehrfach bedankt, das Problem ist gelöst, ich weiss wirklich nicht mehr, was ich noch tun könnte.

Viele Grüße
Igotcha

[omata]

Hallo Igotcha,

es kann sein, dass meine Mail nicht gerade nett rüberkam. Ich war ja auch wirklich sehr verärgert. Es ging mir nicht darum, dass du dich jetzt ständig bedanken sollst. Mir ging es um die Problemlösung. Ob es dir geholfen hat. Dein letztes Kommentar zu der Zeit war eine erneute Frage. In meiner Antwort hatte ich dann auch eine Frage gestellt und es kam keine Antwort. Ich hatte diese Optimierung aus dem Kopf gemacht, konnte also selber nichts testen. Deshalb war ich einfach auf eine Antwort gespannt. Und was musste ich dann Tag für Tag sehen -> nichts. Ein Blick in dein Profil zeigte mir dann, dass du fast täglich online warst. Dann hatte ich erstmal ein Posting verfasst. Da kam dann aber auch nichts. Heute, nach einer Woche platze mir dann der Kragen und deshalb hast du so eine Mail erhalten. Sorry, wenn das nicht so toll rüberkam, ich war aber wie erwahnt auch ziemlich sauer. Wie gesagt, mich interessierte auch die Lösung des Problems, es geht nicht um unendliche Dankesreden.

Ebenfalls Viele Grüsse
Thorsten