Optimierung einer Abfrage

HiddenX · 02.09.2010, 10:55

Hallo allerseits,

nachdem mir das letzte Mal bei einem ähnlichen Thema ([Erledigt] Abfrage aus mehreren Tabellen) so großartig geholfen wurde, kann mir bei meinem neuen Problem bestimmt auch jemand helfen:

Es geht immer noch um das selbe Projekt, die Auswertung der Inventuren von über 100 Filialen über 4 Jahre.

Ich habe jetzt noch zwei Tabellen. Einmal die Inventuren und einmal die Umsätze. (Die Umsätze benötige ich, um eine Quote zu errechnen damit die Filialen verglichen werden können.)

Momentan lasse ich die DB mit PHP 4 (4 Jahre) * x-mal (so oft wie es Filialen gibt) durchsuchen, was natürlich extrem zeitaufwändig ist.
Vorher werden schon mal die Mittelwerte berechnet, um eine Ampel darstellen zu können die zeigt ob die Filiale besser oder schlechter als der Durchschnitt ist.

Ich habe momentan nur 20 Filialen in der DB und die Abfrage dauert schon 20 Sekunden!

Lässt sich das auch DB-seitig lösen? Meine Lösung erscheint mir selbst sehr kompliziert, aber meine Kenntnisse reichen (noch) nicht aus eine bessere zu finden.

Mein Script sieht momentan so aus:

PHP-Code:

      // Abfragen um vorab schon die Mittelwerte zu berechnen

    $jahr       =   2007;

    $jahr_end   =   2010;

    for ($jahr; $jahr <= $jahr_end; $jahr++) {

        // Gesamt-Summe der Differenzen in Wert pro Jahr ermitteln

        $sql                =   'SELECT SUM(`BewPreis3`) AS SUMME FROM inventuren

                                                                WHERE jahr='.$jahr.'';

        $result                    =    mysql_query($sql) OR die(mysql_error());

        $row                    =    mysql_fetch_assoc($result);

        $wert_diff[$jahr]       =    round($row['SUMME'],2);

        // Gesamt-Umsatz pro Jahr ermitteln

        $sql               =   'SELECT SUM(`umsatz`) AS umsatz FROM umsatz

                                                                WHERE jahr='.$jahr.'';

        $result                 =   mysql_query($sql) OR die(mysql_error());

        $row                    =   mysql_fetch_assoc($result);

        $umsatz[$jahr]          =   $row['umsatz']/1000;

        $quoteschnitt[$jahr]    =   round((abs($wert_diff[$jahr])/$umsatz[$jahr]),2);

    }

    // Abfrage1: Über alle Filialen aus Tabelle FILIALEN (beinhaltet Nr und Filiale)

    $sql_abf1       =     'SELECT nr,filiale FROM FILIALE'; 

    $result_abf1    =    mysql_query($sql_abf1) OR die(mysql_error());

    while($row_abf1 = mysql_fetch_assoc($result_abf1)) {

        $nr        =    $row_abf1['nr'];

        $filiale        =    $row_abf1['filiale'];

        // Schleife für Abfragen zwischen 2007 und 2010

        $jahr       =   2007; // Anfangsjahr der Schleife

        $jahr_end   =   2010; // Ende der Schleife

        $quotemax  =   0;     // Startwert: Höchste Quote der jeweiligen Filiale

        // Geht alle Jahre durch

        for ($jahr; $jahr <= $jahr_end; $jahr++) {

            // Summe der Differenzen in Stück und Wert

            $sql                 =   'SELECT SUM(`BewPreis3`)  AS SUMME1,

                                            SUM(`Menge-Diff`) AS SUMME2 FROM inventuren

                                        WHERE nr = "'.$nr.'"

                                        AND jahr='.$jahr.'';

            $result                 =    mysql_query($sql) OR die(mysql_error());

            $row                    =    mysql_fetch_assoc($result);

            $wert_diff[$jahr]    =    round($row['SUMME1'],0);

            $menge_diff[$jahr]  =    $row['SUMME2'];

            ...

            // Umsatz

            $sql                =   'SELECT jahr,nr,umsatz FROM umsatz

                                        WHERE nr= "'.$nr.'"

                                        AND jahr='.$jahr.'';

            $result             =   mysql_query($sql) OR die(mysql_error());

            $row                =   mysql_fetch_assoc($result);

            $umsatz[$jahr]      =   $row['umsatz'];

...

Vielen Dank schon mal
HiddenX

Asipak · 02.09.2010, 11:01

Das Problem ist, dass du Datenabfragen innerhalb einer Schleife ausführst. Das frisst natürlich Ressourcen.

Befasse dich mal mit JOINS: Einführung in Joins und eventuell kann dir auch noch GROUP BY weiterhelfen, um deine Ergebnisse nach dem Jahr zu gruppieren.

fab · 02.09.2010, 11:05

Das Problem sind natürlich die zig einzelnen Datenbank-Abfragen. Du musst versuchen, anstatt für jedes Jahr (oder für jede Filiale) eine einzelne Abfrage an die DB zu senden, diese zusammenzufassen, das Stichwort dazu lautet GROUP BY, für die erste Abfrage in deiner ersten Schleife würde das so aussehen:

Code:

SELECT jahr, SUM(`BewPreis3`) AS SUMME FROM inventuren GROUP BY jahr

Diese Abfrage musst du nur einmal ausführen und kannst dann die Ergebnisse in einer Schleife behandeln. Analog funktioniert das auch für deine anderen Queries.

Der nächste Schritt wäre dann die Überprüfung der Tabellen-Indizes, da gibt es auch oft noch Optimierungsmöglichkeiten.

HiddenX · 02.09.2010, 17:24

Das ist mit Sicherheit der richtige Weg.

Ich habe jetzt mal eure beiden Anregungen gejoint und meine Abfrage sieht jetzt so aus:

Code:

SELECT  inventuren.jahr,
        filialen.filiale,
        SUM('BewPreis3') AS SUMME1,
        SUM('Menge-Diff') AS SUMME2
FROM    filialen,
        inventuren
WHERE    filialen.nr = inventuren.nr
GROUP BY jahr, sapno

Das dauert keine Sekunde mehr, hat aber einen Haken:

Wenn nicht alle Filialen in jedem Jahr ein Inventurergebnis haben, werden die ohne Ergebnis nicht gruppiert:

Meine Filialtabelle umfasst alle Filialen. Inventuren habe ich zum Test jetzt mal erst nur 20 eingelesen. Die Gruppierung umfasst jetzt logischerweise nur diese 20 Filialen.

Ist das zu lösen, oder muss ich doch zumindest eine Schleife über alle Filialen machen und deren Inventuren auslesen um alle Filialen aufzulisten, auch die ohne Inventurergebnis?

(Vielleicht noch eine 3. Tabelle joinen, die mit den filialen und deren Nummern?)

KarlEgon · 02.09.2010, 17:27

Da dürfte dir ein LEFT JOIN weiterhelfen, dadurch erhälst du alle Filialen und wo keine Inventurdaten vorhanden sind, da bleiben die entsprechenden Felder frei

Wolla · 02.09.2010, 18:20

Mit

SELECT ...
FROM tabelle1,tabelle2

bildest du im Speicher ein karthesisches Produkt der Tabellen. Bei kleinen Tabellen macht das keine Probleme, aber wenn z.B. jede Tabelle 10.000 Zeilen hat, dann hast du 10.000 X 10.000 = 100.000.000 Zeilen im Ram.

Mach besser einen LEFT JOIN.

MySQL :: MySQL 5.1 Referenzhandbuch :: 13.2.7.1 JOIN

HiddenX · 02.09.2010, 19:43

Alles nicht so einfach :/

Meine Abfrage sieht jetzt so aus:

Code:

SELECT inventuren.jahr,
                            filialen.nr,
                            filialen.filiale,
                            SUM(`BewPreis3`)AS SUMME1,
                            SUM(`Menge-Diff`) AS SUMME2
                        FROM filialen
                        LEFT JOIN inventuren ON filialen.nr = inventuren.nr
                        GROUP BY jahr, nr

Das Ergebnis ist, dass alle Filialen aufgelistet werden- seltsamerweise aber erst ab der ersten Filiale die KEINE Inventurdaten hat.
Ausserdem sind die Felder Jahr, BewPreis3 und Meng-Diff leer.
Ich bekomme also nur Daten aus der linken Tabelle.

Ich beisse bald in die Tischkante!

lstegelitz · 03.09.2010, 01:14

Zitat:

Zitat von HiddenX

Das Ergebnis ist, dass alle Filialen aufgelistet werden- seltsamerweise aber erst ab der ersten Filiale die KEINE Inventurdaten hat.

Aber das wolltest du doch

Zitat:

Zitat von HiddenX

Ist das zu lösen, oder muss ich doch zumindest eine Schleife über alle Filialen machen und deren Inventuren auslesen um alle Filialen aufzulisten, auch die ohne Inventurergebnis?

HiddenX · 03.09.2010, 08:52

Zitat:

Zitat von lstegelitz

Aber das wolltest du doch

Nö.

Ich möchte gerne auch die Filialen, die Inventurdaten haben. Und natürlich auch die Inventurergebnisse, die mir momentan nicht geliefert werden.

Also ALLE Filialen aus der Tabelle Filialen und die Filialen der Tabelle Inventuren die befüllte Felder haben.

Genau das müsste mit LEFT JOIN gehen, so wie ich das gelesen habe.

Also macht mir das GROUP BY die Abfrage wieder "kaputt"?

HiddenX · 03.09.2010, 10:04

JOIN ist ja tückisch!

Ich habe den 'Fehler' gefunden!

Es lag am GROUP BY. Erstens war die Reihenfolge die falsche, zweitens (und das meine ich mit tückisch) hat sich Abfrage die Filialen aus der "falschen Tabelle" geholt, nämlich aus der Inventur-Tabelle.
Da aber alle Filialen aufgelistet sollen, muss ich natürlich explizit auf die Tabelle Filialen verweisen.

Die Richtige Abfrage ist also:

Code:

SELECT inventuren.jahr,
                            filialen.nr,
                            filialen.filiale,
                            SUM(`BewPreis3`)AS SUMME1,
                            SUM(`Menge-Diff`) AS SUMME2
                        FROM filialen
                        LEFT JOIN inventuren ON filialen.nr = inventuren.nr
                        GROUP BY filialen.nr, jahr
                        ORDER BY nr, jahr

Manchmal hilft es einfach nur in's Bett zu gehen und am nächsten Tag weiterzumachen

So. Jetzt das ganze nur noch in eine Schleife packen, auslesen und in einer Tabelle ausgeben.

Was meint ihr geht schneller: Die Umsätze auch noch dazujoinen (geht das überhaupt?)
Oder in der Schleife dann doch noch mal eine Abfrage jeweils nach dem Umsatz durchführen?

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
Abfrage von einer Abfrage		Datenbanken	5	27.01.2011 23:31
Problem mit Abfrage - join, inner join, distinct?	Gachet01	Datenbanken	2	27.01.2011 06:49
Optimierung einer Abfrage eines Datensatzes samt Vorgänger und Nachfolger	Sirke	Datenbanken	7	28.04.2010 17:11
result Variablen von SQL verbinden	vci	PHP Tipps 2010	15	05.02.2010 19:09
Scriptsuche [Erledigt] SQL- Abfrage über 2 tabellen	ejim	Scriptbörse	1	17.07.2009 21:10
SQL Abfrage ohne DESC langsam	Thisi	Datenbanken	5	07.01.2009 09:53
Wiemache ich eine Abfrage über 3 Tabellen???	djscaleo	Datenbanken	8	05.01.2009 10:46
MySQL - Fehler in einfacher Abfrage	oden	Datenbanken	11	03.01.2009 20:03
SELECT WHERE datetime abfrage - Optimierung	mrSpok	Datenbanken	15	23.03.2006 19:08
mysql abfrage über 4 Tabelle - bis 3 geht, bei der 4. habert		Datenbanken	2	08.09.2005 11:59
[Erledigt] IF() abfrage in variable packen		PHP Tipps 2005	14	01.04.2005 17:23
Abfrage von Char-Feldern		Datenbanken	9	04.02.2005 14:06
[Erledigt] Mysql Abfrage Problem!		PHP-Fortgeschrittene	5	27.11.2004 10:22
Abfrage funktioniert zwar, aber nicht korrekt		Datenbanken	2	16.08.2004 09:10
Abfrage aus DB noch mal ausgeben und Abfrage aus mehrern Tab		PHP Tipps 2004	4	12.07.2004 15:00

02.09.2010, 10:55
HiddenX Neuer Benutzer Registriert seit: 24.08.2010 Beiträge: 21 PHP-Kenntnisse: Anfänger	Optimierung einer Abfrage Hallo allerseits, nachdem mir das letzte Mal bei einem ähnlichen Thema ([Erledigt] Abfrage aus mehreren Tabellen) so großartig geholfen wurde, kann mir bei meinem neuen Problem bestimmt auch jemand helfen: Es geht immer noch um das selbe Projekt, die Auswertung der Inventuren von über 100 Filialen über 4 Jahre. Ich habe jetzt noch zwei Tabellen. Einmal die Inventuren und einmal die Umsätze. (Die Umsätze benötige ich, um eine Quote zu errechnen damit die Filialen verglichen werden können.) Momentan lasse ich die DB mit PHP 4 (4 Jahre) * x-mal (so oft wie es Filialen gibt) durchsuchen, was natürlich extrem zeitaufwändig ist. Vorher werden schon mal die Mittelwerte berechnet, um eine Ampel darstellen zu können die zeigt ob die Filiale besser oder schlechter als der Durchschnitt ist. Ich habe momentan nur 20 Filialen in der DB und die Abfrage dauert schon 20 Sekunden! Lässt sich das auch DB-seitig lösen? Meine Lösung erscheint mir selbst sehr kompliziert, aber meine Kenntnisse reichen (noch) nicht aus eine bessere zu finden. Mein Script sieht momentan so aus: PHP-Code: // Abfragen um vorab schon die Mittelwerte zu berechnen $jahr = 2007; $jahr_end = 2010; for ($jahr; $jahr <= $jahr_end; $jahr++) { // Gesamt-Summe der Differenzen in Wert pro Jahr ermitteln $sql = 'SELECT SUM(`BewPreis3`) AS SUMME FROM inventuren WHERE jahr='.$jahr.''; $result = mysql_query($sql) OR die(mysql_error()); $row = mysql_fetch_assoc($result); $wert_diff[$jahr] = round($row['SUMME'],2); // Gesamt-Umsatz pro Jahr ermitteln $sql = 'SELECT SUM(`umsatz`) AS umsatz FROM umsatz WHERE jahr='.$jahr.''; $result = mysql_query($sql) OR die(mysql_error()); $row = mysql_fetch_assoc($result); $umsatz[$jahr] = $row['umsatz']/1000; $quoteschnitt[$jahr] = round((abs($wert_diff[$jahr])/$umsatz[$jahr]),2); } // Abfrage1: Über alle Filialen aus Tabelle FILIALEN (beinhaltet Nr und Filiale) $sql_abf1 = 'SELECT nr,filiale FROM FILIALE'; $result_abf1 = mysql_query($sql_abf1) OR die(mysql_error()); while($row_abf1 = mysql_fetch_assoc($result_abf1)) { $nr = $row_abf1['nr']; $filiale = $row_abf1['filiale']; // Schleife für Abfragen zwischen 2007 und 2010 $jahr = 2007; // Anfangsjahr der Schleife $jahr_end = 2010; // Ende der Schleife $quotemax = 0; // Startwert: Höchste Quote der jeweiligen Filiale // Geht alle Jahre durch for ($jahr; $jahr <= $jahr_end; $jahr++) { // Summe der Differenzen in Stück und Wert $sql = 'SELECT SUM(`BewPreis3`) AS SUMME1, SUM(`Menge-Diff`) AS SUMME2 FROM inventuren WHERE nr = "'.$nr.'" AND jahr='.$jahr.''; $result = mysql_query($sql) OR die(mysql_error()); $row = mysql_fetch_assoc($result); $wert_diff[$jahr] = round($row['SUMME1'],0); $menge_diff[$jahr] = $row['SUMME2']; ... // Umsatz $sql = 'SELECT jahr,nr,umsatz FROM umsatz WHERE nr= "'.$nr.'" AND jahr='.$jahr.''; $result = mysql_query($sql) OR die(mysql_error()); $row = mysql_fetch_assoc($result); $umsatz[$jahr] = $row['umsatz']; ... Vielen Dank schon mal HiddenX


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

02.09.2010, 11:01
Asipak Moderator Registriert seit: 18.07.2005 Beiträge: 4.071	Das Problem ist, dass du Datenabfragen innerhalb einer Schleife ausführst. Das frisst natürlich Ressourcen. Befasse dich mal mit JOINS: Einführung in Joins und eventuell kann dir auch noch GROUP BY weiterhelfen, um deine Ergebnisse nach dem Jahr zu gruppieren. __________________ HalloPHP

02.09.2010, 11:05
fab Erfahrener Benutzer Registriert seit: 28.07.2010 Beiträge: 1.720 PHP-Kenntnisse: Fortgeschritten	Das Problem sind natürlich die zig einzelnen Datenbank-Abfragen. Du musst versuchen, anstatt für jedes Jahr (oder für jede Filiale) eine einzelne Abfrage an die DB zu senden, diese zusammenzufassen, das Stichwort dazu lautet GROUP BY, für die erste Abfrage in deiner ersten Schleife würde das so aussehen: Code: SELECT jahr, SUM(`BewPreis3`) AS SUMME FROM inventuren GROUP BY jahr Diese Abfrage musst du nur einmal ausführen und kannst dann die Ergebnisse in einer Schleife behandeln. Analog funktioniert das auch für deine anderen Queries. Der nächste Schritt wäre dann die Überprüfung der Tabellen-Indizes, da gibt es auch oft noch Optimierungsmöglichkeiten. __________________ PHP Blog + Library CSS Effects + Tools

02.09.2010, 17:24
HiddenX Neuer Benutzer Registriert seit: 24.08.2010 Beiträge: 21 PHP-Kenntnisse: Anfänger	Das ist mit Sicherheit der richtige Weg. Ich habe jetzt mal eure beiden Anregungen gejoint und meine Abfrage sieht jetzt so aus: Code: SELECT inventuren.jahr, filialen.filiale, SUM('BewPreis3') AS SUMME1, SUM('Menge-Diff') AS SUMME2 FROM filialen, inventuren WHERE filialen.nr = inventuren.nr GROUP BY jahr, sapno Das dauert keine Sekunde mehr, hat aber einen Haken: Wenn nicht alle Filialen in jedem Jahr ein Inventurergebnis haben, werden die ohne Ergebnis nicht gruppiert: Meine Filialtabelle umfasst alle Filialen. Inventuren habe ich zum Test jetzt mal erst nur 20 eingelesen. Die Gruppierung umfasst jetzt logischerweise nur diese 20 Filialen. Ist das zu lösen, oder muss ich doch zumindest eine Schleife über alle Filialen machen und deren Inventuren auslesen um alle Filialen aufzulisten, auch die ohne Inventurergebnis? (Vielleicht noch eine 3. Tabelle joinen, die mit den filialen und deren Nummern?)

02.09.2010, 17:27
KarlEgon Erfahrener Benutzer Registriert seit: 24.10.2008 Beiträge: 291 PHP-Kenntnisse: Fortgeschritten	Da dürfte dir ein LEFT JOIN weiterhelfen, dadurch erhälst du alle Filialen und wo keine Inventurdaten vorhanden sind, da bleiben die entsprechenden Felder frei

02.09.2010, 18:20
Wolla Moderator Registriert seit: 06.06.2008 Beiträge: 4.849 PHP-Kenntnisse: Fortgeschritten	Mit SELECT ... FROM tabelle1,tabelle2 bildest du im Speicher ein karthesisches Produkt der Tabellen. Bei kleinen Tabellen macht das keine Probleme, aber wenn z.B. jede Tabelle 10.000 Zeilen hat, dann hast du 10.000 X 10.000 = 100.000.000 Zeilen im Ram. Mach besser einen LEFT JOIN. MySQL :: MySQL 5.1 Referenzhandbuch :: 13.2.7.1 JOIN __________________ Warum denkt mein Hund eigentlich immer dann, wenn es an der Tür klingelt, es sei für ihn?

02.09.2010, 19:43
HiddenX Neuer Benutzer Registriert seit: 24.08.2010 Beiträge: 21 PHP-Kenntnisse: Anfänger	Alles nicht so einfach :/ Meine Abfrage sieht jetzt so aus: Code: SELECT inventuren.jahr, filialen.nr, filialen.filiale, SUM(`BewPreis3`)AS SUMME1, SUM(`Menge-Diff`) AS SUMME2 FROM filialen LEFT JOIN inventuren ON filialen.nr = inventuren.nr GROUP BY jahr, nr Das Ergebnis ist, dass alle Filialen aufgelistet werden- seltsamerweise aber erst ab der ersten Filiale die KEINE Inventurdaten hat. Ausserdem sind die Felder Jahr, BewPreis3 und Meng-Diff leer. Ich bekomme also nur Daten aus der linken Tabelle. Ich beisse bald in die Tischkante!

03.09.2010, 10:04
HiddenX Neuer Benutzer Registriert seit: 24.08.2010 Beiträge: 21 PHP-Kenntnisse: Anfänger	JOIN ist ja tückisch! Ich habe den 'Fehler' gefunden! Es lag am GROUP BY. Erstens war die Reihenfolge die falsche, zweitens (und das meine ich mit tückisch) hat sich Abfrage die Filialen aus der "falschen Tabelle" geholt, nämlich aus der Inventur-Tabelle. Da aber alle Filialen aufgelistet sollen, muss ich natürlich explizit auf die Tabelle Filialen verweisen. Die Richtige Abfrage ist also: Code: SELECT inventuren.jahr, filialen.nr, filialen.filiale, SUM(`BewPreis3`)AS SUMME1, SUM(`Menge-Diff`) AS SUMME2 FROM filialen LEFT JOIN inventuren ON filialen.nr = inventuren.nr GROUP BY filialen.nr, jahr ORDER BY nr, jahr Manchmal hilft es einfach nur in's Bett zu gehen und am nächsten Tag weiterzumachen So. Jetzt das ganze nur noch in eine Schleife packen, auslesen und in einer Tabelle ausgeben. Was meint ihr geht schneller: Die Umsätze auch noch dazujoinen (geht das überhaupt?) Oder in der Schleife dann doch noch mal eine Abfrage jeweils nach dem Umsatz durchführen?