Ankündigung

Einklappen
Keine Ankündigung bisher.

Optimierung einer Abfrage

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • Optimierung einer Abfrage

    Hallo allerseits,

    nachdem mir das letzte Mal bei einem ähnlichen Thema 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

  • #2
    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.
    [URL]http://hallophp.de[/URL]

    Kommentar


    • #3
      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.
      [IMG]https://g.twimg.com/twitter-bird-16x16.png[/IMG][URL="https://twitter.com/fschmengler"]@fschmengler[/URL] - [IMG]https://i.stack.imgur.com/qh235.png[/IMG][URL="https://stackoverflow.com/users/664108/fschmengler"]@fschmengler[/URL] - [IMG]http://i.imgur.com/ZEqflLv.png[/IMG] [URL="https://github.com/schmengler/"]@schmengler[/URL]
      [URL="http://www.schmengler-se.de/"]PHP Blog[/URL] - [URL="http://www.schmengler-se.de/magento-entwicklung/"]Magento Entwicklung[/URL] - [URL="http://www.css3d.net/"]CSS Ribbon Generator[/URL]

      Kommentar


      • #4
        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?)

        Kommentar


        • #5
          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

          Kommentar


          • #6
            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
            [PHP]if ($var != 0) {
            $var = 0;
            }[/PHP]

            Kommentar


            • #7
              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!

              Kommentar


              • #8
                Zitat von HiddenX Beitrag anzeigen
                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 von HiddenX Beitrag anzeigen
                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?
                Über 90% aller Gewaltverbrechen passieren innerhalb von 24 Stunden nach dem Konsum von Brot.

                Kommentar


                • #9
                  Zitat von lstegelitz Beitrag anzeigen
                  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"?

                  Kommentar


                  • #10
                    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?

                    Kommentar


                    • #11
                      Ich schon wieder

                      Also rein logich betrachtet muss es mit dem JOIN über 3 Tabellen schneller gehen. Eine Abfrage und danach eine Schleife MUSS schneller sein als eine Schleife mit vielen DB-Abfragen drin.

                      Also habe ich jetzt mal versucht:

                      Code:
                      SELECT inventuren.jahr,
                        filialen.nr,
                        filialen.filiale,
                        SUM(`BewPreis3`)AS SUMME1,
                        SUM(`Menge-Diff`) AS SUMME2,
                        umsatz  
                      FROM filialen
                      LEFT JOIN umsatz ON filialen.nr = umsatz.nr
                      LEFT JOIN inventuren ON filialen.nr = inventuren.nr
                      group by filialen.nr, jahr
                      order by nr, jahr
                      Ergebnis:
                      inventuren.jahr - OK, es werden alle Jahre aufgelistet, von 2007 bis 2010
                      filialen.nr - OK, alle Filialnummern sind da, immer 4* (2007-2010)
                      filialen.filiale - OK, entsprechend der nr
                      SUMME1 - Hier fängt das Elend an: Der ausgegebene Wert ist im ersten Block, also bei den ersten 2007-2010, 3*Wert des ersten Datensatzes (sooft es bei der Filiale umsätze gibt), beim zweiten Mal korrekt.
                      SUMME2 - Analog zu Summe2

                      Beim zweiten Block, also beim zweiten Mal 2007-2010 stimmt alles. Vermutlich aber nur deshalb, weil ich bei der zweiten Filiale testhalber nur einen Umsatz drin habe. Ich vermute, dass bei zwei Umsätzen Summe1 der doppelte Wert des ersten Datensatz wäre.

                      Ich habe testweise mal den dritten JOIN rausgenommen, also nur Filialen und deren Umsätze- das geht.

                      Hat jemand einen Tipp für mich?

                      Kommentar


                      • #12
                        Irgendwie ist dein Tabellen Design komisch
                        PHP-Code:
                        LEFT JOIN umsatz ON filialen.nr umsatz.nr
                        LEFT JOIN inventuren ON filialen
                        .nr inventuren.nr 
                        sowas finde ich nicht logisch.

                        so in der Richtung fände ich das viel logischer
                        PHP-Code:
                        LEFT JOIN umsatz ON filialen.nr umsatz.filialen_nr
                        LEFT JOIN inventuren ON filialen
                        .nr inventuren.filialen_nr 

                        Kommentar


                        • #13
                          Das sieht in der Tat übersichtlicher aus, ändert aber nichts am Grundproblem

                          Kommentar


                          • #14
                            SUMME1 - Hier fängt das Elend an: Der ausgegebene Wert ist im ersten Block, also bei den ersten 2007-2010, 3*Wert des ersten Datensatzes (sooft es bei der Filiale umsätze gibt), beim zweiten Mal korrekt.
                            Ok...der Datensatz wird 3mal ausgegeben, also muss er in Verbindung mit deinen join´s ja mehrfach gezählt werden.
                            Ich habe testweise mal den dritten JOIN rausgenommen, also nur Filialen und deren Umsätze- das
                            Das würde dann meine These bestätigen.
                            Code:
                            group by filialen.nr, jahr
                            order by nr, jahr
                            Dann versuch´ mal an der Stelle noch eine weitere Gruppierung hinzuzufügen --> inventuren.nr.

                            Wenn ich gerade völlig daneben liege, entschuldige ich mich bereits jetzt
                            Delirius

                            PHP - Grundlagen / PDO - Tutorial / PDO - Dokumentation

                            Kommentar


                            • #15
                              Zitat von Wolla Beitrag anzeigen
                              SELECT ...
                              FROM tabelle1,tabelle2

                              bildest du im Speicher ein karthesisches Produkt der Tabellen.
                              Das ist Quatsch... mach ein EXPLAIN, schaus dir im QueryAnalyzer, SET PLANONLY oder k.a.. Jedes gängige RDBMS ist in der lage solche JOINs mithilfe der WHERE Bedingung sinnvoll zu lösen.

                              Zitat von HiddenX Beitrag anzeigen
                              Code:
                              SELECT inventuren.jahr,
                                filialen.nr,
                                filialen.filiale,
                                SUM(`BewPreis3`)AS SUMME1,
                                SUM(`Menge-Diff`) AS SUMME2,
                                umsatz  
                              FROM filialen
                              LEFT JOIN umsatz ON filialen.nr = umsatz.nr
                              LEFT JOIN inventuren ON filialen.nr = inventuren.nr
                              group by filialen.nr, jahr
                              order by nr, jahr
                              Hat jemand einen Tipp für mich?
                              Die Tabelle umsatz und inventuren sind voneinander ZEITLICH abhängig. Diese Abhängigkeit muss in die JOIN Bedingung.


                              z.B.:

                              Code:
                              SELECT inventuren.jahr,
                                filialen.nr,
                                filialen.filiale,
                                SUM(`BewPreis3`)AS SUMME1,
                                SUM(`Menge-Diff`) AS SUMME2,
                                umsatz  
                              FROM filialen
                              LEFT JOIN umsatz ON filialen.nr = umsatz.nr
                              LEFT JOIN inventuren ON filialen.nr = inventuren.nr AND umsatz.zeitraum = inventuren.zeitraum
                              group by filialen.nr, jahr
                              order by nr, jahr

                              Kommentar

                              Lädt...
                              X