|
Datenbank: MYSQL • Version: 5.1 • Zugriff über: ZEOS
[SQL] Abfrage tunen
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:
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?
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 Viele Grüße Igotcha |
Re: [SQL] Abfrage tunen
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 |
Re: [SQL] Abfrage tunen
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 |
Re: [SQL] Abfrage tunen
So, habe mich auch mal dran versucht...
SQL-Code:
Ungetestet.
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 MfG Thorsten |
Re: [SQL] Abfrage tunen
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. |
Re: [SQL] Abfrage tunen
Zitat:
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:
Zitat:
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 :mrgreen: ?) auf dem Laufenden hält. Das ist -nun ja- nicht die reine Lehre, aber in der Praxis Gang und Gäbe. |
Re: [SQL] Abfrage tunen
Zitat:
Was man einmal testen könnte wäre die Abfragen mit dem IN [] zu ersetzen. Ich weiss nicht ob diese optimal genutz werden. |
Re: [SQL] Abfrage tunen
Zitat:
|
Re: [SQL] Abfrage tunen
Zitat:
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 |
Re: [SQL] Abfrage tunen
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... |
Re: [SQL] Abfrage tunen
Zitat:
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 |
Re: [SQL] Abfrage tunen
@omata
Ich bekomme einen Fehler, wenn ich versuche, aus Deiner Abfrage einen VIEW mit
SQL-Code:
zu erstellen.
CREATE OR REPLACE VIEW pim_pb_testbericht
AS ... "View's SELECT contains a subquery in the FROM clause". Kann man das noch irgendwie umgehen? Gruß Igotcha |
Re: [SQL] Abfrage tunen
Na, da fangen die Vorurteile ja doch wieder an... Mit MSSQL wäre das nicht passiert... Aber das ist ein anderes Thema.
|
Re: [SQL] Abfrage tunen
ja man kann natuerlich auch mit dem mysql tunen.
RTFM!  http://dev.mysql.com/doc/refman/5.0/en/query-speed.html |
Re: [SQL] Abfrage tunen
@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:
View_Left2
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
SQL-Code:
View_Main
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
SQL-Code:
So sollte das gehen.
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 Jetzt bin ich nur mal auf die Geschwindigkeit gespannt :gruebel: MfG Thorsten |
Re: [SQL] Abfrage tunen
@Igotcha: schade dass keine Rückmeldung kommt...
|
Re: [SQL] Abfrage tunen
Zitat:
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 |
Re: [SQL] Abfrage tunen
Dein letztes Kommantar war...
Zitat:
Zitat:
Das finde ich ziemlich bedauerlich. Naja, vielleicht sollte ich mich einfach nicht mehr um solche Dinge kümmern. |
Re: [SQL] Abfrage tunen
Zitat:
Zitat:
Viele Grüße Igotcha |
Re: [SQL] Abfrage tunen
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 |
| Alle Zeitangaben in WEZ +1. Es ist jetzt 01:49 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