Ankündigung

Einklappen
Keine Ankündigung bisher.

Wie bekomme ich diese Abfrage performanter?

Einklappen

Neue Werbung 2019

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

  • Wie bekomme ich diese Abfrage performanter?

    Hi,

    ich habe eine recht komplexe Abfrage welche aber eigentlich nicht so langsam sein sollte wie sie gerade ist. Die Abfrage benötigt 3 Tabellen

    ge_tool (278 Datensätze)

    tool_id INT AUTOINC PRIMARY KEY
    tool_number VARCHAR[50]
    tool_status INT

    ge_order (2400 Dateinsätze)

    order_id INT AUTOINC PRIMARY KEY
    order_startdate DATETIME
    order_enddate DATETIME
    order_jobnumber VARCHAR[50]
    order_deleted INT
    order_type INT

    ge_ordertool (2900 Datensätze)

    ordertool_order INT KEY
    ordertool_tool INT KEY
    ordertool_delivered DATETIME
    ordertool_returned DATETIME

    Folgendes Ergebnis soll die Abfrage haben:

    6 Spalten mit den Werten

    order_type
    order_startdate
    order_enddate,
    order_jobnumber,
    order_tools (alle für diesen Auftrag benötigten tool_number. Können beliebig viele sein)
    order_status (Geplant, in Ausführung, Beendet, Gelöscht)

    aller Aufträge innerhalb der letzten 12 Monate.

    Im Moment dauert die Abfrage die ich habe bei der angegebenen Anzahl Datensätze ca 3-4 Sekunden. Das finde ich ziemlich lange angesichts der kleinen Menge an Datensätzen.
    Was ich heraus finden konnte ist, dass der GROUP BY das Ganze so langsam macht. Nehme ich den raus ist es wieder im Bruchteil einer Sekunde fertig.

    Was kann ich also tun? Kann ich das durch weitere Keys verbessern? Oder eventuell auch was an den mySQL Einstellungen ändern (MEhr Speicher etc?)

    Code:
            $sql = "SELECT DISTINCT(ge_order.order_id) AS order_id,
                        ge_order.order_startdate AS order_startdate,
                        ge_order.order_enddate AS order_enddate,
                        ge_order.order_jobnumber AS order_jobnumber,
                        ge_tool.tool_status AS tool_status,
                        GROUP_CONCAT(ge_tool.tool_number SEPARATOR '<br>') AS order_tools,
                        IF(ge_order.order_deleted = 1,'".$orderStatusList['deleted']."',
                            IF(
                                (
                                    SELECT COUNT(ge_ordertool.ordertool_order)
                                    FROM ge_ordertool
                                    WHERE ge_ordertool.ordertool_order = ge_order.order_id AND ge_ordertool.ordertool_delivered IS NOT NULL
                                ) = 0
                            ,
                            '".$orderStatusList['planned']."',
                                IF(
                                    (
                                        SELECT COUNT(ge_ordertool.ordertool_order)
                                        FROM ge_ordertool
                                        WHERE ge_ordertool.ordertool_order = ge_order.order_id AND ge_ordertool.ordertool_returned IS NULL
                                    ) <> 0
                                ,
                                '".$orderStatusList['executing']."','".$orderStatusList['done']."')))
                                    AS order_status,
    
                        CASE ge_order.order_type ";
            $i=0;
            foreach($orderTypeStringList as $ordertype)
            {
                $sql.="WHEN ".$i." THEN '".$ordertype."' ";
                $i++;
            }
            $sql.= "   END AS ordertype_name
    
                    FROM ge_ordertool
                    LEFT JOIN ge_order
                        ON (ge_order.order_id=ge_ordertool.ordertool_order)
                    LEFT JOIN ge_tool
                        ON (ge_tool.tool_id=ge_ordertool.ordertool_tool)
                    WHERE ge_order.order_enddate > DATE_SUB(CURDATE(), INTERVAL 1 YEAR) 
                    GROUP BY ge_ordertool.ordertool_order
                    ";
    Danke

    Claus
    Pre-Coffee-Posts sind mit Vorsicht zu geniessen!

  • #2
    Hast du einen Execution-Plan dazu zur Hand? Einmal EXPLAIN [ganzes Statement] und das Resultat hier posten.
    [URL="https://github.com/chrisandchris"]GitHub.com - ChrisAndChris[/URL] - [URL="https://github.com/chrisandchris/symfony-rowmapper"]RowMapper und QueryBuilder für MySQL-Datenbanken[/URL]

    Kommentar


    • #3
      Zitat von Thallius Beitrag anzeigen
      Was ich heraus finden konnte ist, dass der GROUP BY das Ganze so langsam macht. Nehme ich den raus ist es wieder im Bruchteil einer Sekunde fertig.
      Lass das LIMIT weg. Es ist nicht das GROUP BY das den Query langsam macht. Es sind die Subqueries. Ich behaupte mal du schaust dir den Query in irgendein Tool an was automatisch ein LIMIT setzt. Damit werden ohne GROUP BY die Subqueries nur für N Datensätze ausgeführt. Mit GROUP BY müssen aber zwangsläufig alle Subqueries ausgeführt werden.
      Ich hab jetzt aber auch keine Lust mich da rein zu denken. Allgemein gilt Subqueries möglichst ins FROM. Damit werden sie nur einmal ausgeführt. Im SELECT nur wenn mit wenigen Datensätzen gerechnet wird (z.B. mit LIMIT begrenzt). Im WHERE auch nur wenn nicht dependent oder mit wenigen Datensätzen zu rechnen ist (LIMIT hilft hier nicht).

      Kommentar


      • #4
        Zitat von erc Beitrag anzeigen
        Lass das LIMIT weg. Es ist nicht das GROUP BY das den Query langsam macht. Es sind die Subqueries. Ich behaupte mal du schaust dir den Query in irgendein Tool an was automatisch ein LIMIT setzt. Damit werden ohne GROUP BY die Subqueries nur für N Datensätze ausgeführt. Mit GROUP BY müssen aber zwangsläufig alle Subqueries ausgeführt werden.
        Ich hab jetzt aber auch keine Lust mich da rein zu denken. Allgemein gilt Subqueries möglichst ins FROM. Damit werden sie nur einmal ausgeführt. Im SELECT nur wenn mit wenigen Datensätzen gerechnet wird (z.B. mit LIMIT begrenzt). Im WHERE auch nur wenn nicht dependent oder mit wenigen Datensätzen zu rechnen ist (LIMIT hilft hier nicht).
        Welches LIMIT?

        Gruß

        Claus
        Pre-Coffee-Posts sind mit Vorsicht zu geniessen!

        Kommentar


        • #5
          Zitat von ChristianK Beitrag anzeigen
          Hast du einen Execution-Plan dazu zur Hand? Einmal EXPLAIN [ganzes Statement] und das Resultat hier posten.
          Danke das werde ich später machen. Bin gerade unterwegs bei einem Kunden.

          Gruß

          Claus
          Pre-Coffee-Posts sind mit Vorsicht zu geniessen!

          Kommentar


          • #6
            Hi Christian,

            hier das Ergebnis des EXPLAIN. Ich muss zugeben mir sagt das aber nicht viel. Hoffe Dir hilft es:

            +----+--------------------+--------------+--------+-------------------+---------+---------+-----------------------------------+------+----------------------------------------------+
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
            +----+--------------------+--------------+--------+-------------------+---------+---------+-----------------------------------+------+----------------------------------------------+
            | 1 | PRIMARY | ge_order | ALL | enddate | NULL | NULL | NULL | 1919 | Using where; Using temporary; Using filesort |
            | 1 | PRIMARY | ge_ordertool | ref | PRIMARY | PRIMARY | 4 | itool.ge_order.order_id | 1 | Using index |
            | 1 | PRIMARY | ge_tool | eq_ref | PRIMARY | PRIMARY | 4 | itool.ge_ordertool.ordertool_tool | 1 | |
            | 3 | DEPENDENT SUBQUERY | ge_ordertool | ref | PRIMARY,returned | PRIMARY | 4 | func | 1 | Using where |
            | 2 | DEPENDENT SUBQUERY | ge_ordertool | ref | PRIMARY,delivered | PRIMARY | 4 | func | 1 | Using where |
            +----+--------------------+--------------+--------+-------------------+---------+---------+-----------------------------------+------+----------------------------------------------+
            Pre-Coffee-Posts sind mit Vorsicht zu geniessen!

            Kommentar


            • #7
              Zitat von Thallius Beitrag anzeigen
              Welches LIMIT?
              Ich bin mir ziemlich sicher das da ein LIMIT im Spiel ist. GROUP BY hat nicht ein derartigen einfluss auf die Performance, es ist u.U. sogar tendenziel umgedreht. Die einzig andere Möglichkeit wäre dass das GROUP BY eine Optimierung bei den Subqueries aushebelt. Halte ich aber für unwahrscheinlich.
              Auch die falsche join Reihnfolge hat bei der Handvoll Datensätze keine nennenswerten Auswirkung und würde den Query gleich beeinflussen, egal ob GROUP BY oder nicht.
              Ich denke das sinnvollste was du machen kannst ist order_status im voraus zu berechnen und nicht zur Laufzeit bestimmen. (z.B. Denormalisierung + Trigger)

              PS: natürlich kann es sein das ich mich damit zuweit aus dem Fenster lehne. Ich hab eine ziemlich genau Vorstellung wie Mysql Queries ausführt. Aber das Problem mit Wissen und eigentlich nix Wissen und der Überzeugung ist immer so ein Ding.

              Kommentar


              • #8
                Zitat von erc Beitrag anzeigen
                Ich bin mir ziemlich sicher das da ein LIMIT im Spiel ist. GROUP BY hat nicht ein derartigen einfluss auf die Performance, es ist u.U. sogar tendenziel umgedreht. Die einzig andere Möglichkeit wäre dass das GROUP BY eine Optimierung bei den Subqueries aushebelt. Halte ich aber für unwahrscheinlich.
                Auch die falsche join Reihnfolge hat bei der Handvoll Datensätze keine nennenswerten Auswirkung und würde den Query gleich beeinflussen, egal ob GROUP BY oder nicht.
                Ich denke das sinnvollste was du machen kannst ist order_status im voraus zu berechnen und nicht zur Laufzeit bestimmen. (z.B. Denormalisierung + Trigger)

                PS: natürlich kann es sein das ich mich damit zuweit aus dem Fenster lehne. Ich hab eine ziemlich genau Vorstellung wie Mysql Queries ausführt. Aber das Problem mit Wissen und eigentlich nix Wissen und der Überzeugung ist immer so ein Ding.
                Ich kann Dir ja nur sagen wie ich es gemacht habe.
                Der Query ist 1:1 so wie er in Post #1 steht. Dort gibt er keinen LIMIT. Das wäre auch kontra produktiv da ich alle Einträge haben will.

                Wenn ich nur das GROUP BY weglasse ist der Quesry auf einmal in weniger als 1/10 sekunde fertig. Wenn ich den If oder den CASE weglasse bleibt er gleich langsam. Das habe ich ja alles schon einzeln ausprobiert.

                Gruß

                Claus
                Pre-Coffee-Posts sind mit Vorsicht zu geniessen!

                Kommentar


                • #9
                  Was mir jetzt auffällt ist, das der äusserste SELECT keinen Index benutzen kann und eine temporäre Tabelle nutzt. Das Problem ist, dass er das Enddate wirklich für alle ausrechnen muss.

                  Die Sub-Queries sind natürlich suboptimal, insbesondere auf hohe Anzahl Zeilen. Wobei bei 2000 sehe ich hier nicht so ein gewaltiges Problem.

                  So auf die schnelle kann ich leider nicht sagen, wo das Problem liegt. Evt. könnte dir Workbench noch helfen, der Performance-Report lässt dort ein visuelles EXPLAIN zu. Ich konnte es leider selber noch nie probieren, soll aber gut sein. http://dev.mysql.com/doc/workbench/e...e-explain.html

                  Ein LIMIT sehe ich im Query auch gerade keines?
                  [URL="https://github.com/chrisandchris"]GitHub.com - ChrisAndChris[/URL] - [URL="https://github.com/chrisandchris/symfony-rowmapper"]RowMapper und QueryBuilder für MySQL-Datenbanken[/URL]

                  Kommentar


                  • #10
                    Zitat von ChristianK Beitrag anzeigen
                    Was mir jetzt auffällt ist, das der äusserste SELECT keinen Index benutzen kann und eine temporäre Tabelle nutzt. Das Problem ist, dass er das Enddate wirklich für alle ausrechnen muss.
                    Es könnte helfen, das DATE_SUB(CURDATE(), INTERVAL 1 YEAR) als statischen Wert zu nehmen.


                    Die Sub-Queries sind natürlich suboptimal, insbesondere auf hohe Anzahl Zeilen. Wobei bei 2000 sehe ich hier nicht so ein gewaltiges Problem.
                    Das mag begrenzt stimmen. Begrenzt auf MySQL.
                    PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                    Kommentar


                    • #11
                      Danke Arne

                      durch deinen Tipp bin ich zumindest einen Schritt weiter.

                      Ersetze ich das DATE_SUB() einfach mal durch NOW(), dann ist es wieder rasend schnell. Erzeuge ich eine PHP Variable

                      $date = date('Y-m-d');

                      und setzte diese direct ins query ist es wieder sau langsam....

                      Kann da jemand was mit anfangen?

                      Gruß

                      Claus
                      Pre-Coffee-Posts sind mit Vorsicht zu geniessen!

                      Kommentar


                      • #12
                        Zitat von Thallius Beitrag anzeigen

                        Ersetze ich das DATE_SUB() einfach mal durch NOW(), dann ist es wieder rasend schnell. Erzeuge ich eine PHP Variable

                        $date = date('Y-m-d');

                        und setzte diese direct ins query ist es wieder sau langsam....

                        möglicherweise weil er es casten muß.
                        PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                        Kommentar


                        • #13
                          Wenn ich in den query direkt

                          > '2015-01-21'

                          Schreibe ist es auch nicht schneller...
                          Pre-Coffee-Posts sind mit Vorsicht zu geniessen!

                          Kommentar


                          • #14
                            Zitat von Thallius Beitrag anzeigen
                            Wenn ich in den query direkt

                            > '2015-01-21'

                            Schreibe ist es auch nicht schneller...
                            In einem PG-explain könnte man sehen, ob da erst gecastet wird. Vielleicht willst Du ihm ja auf die Sprünge helfen...
                            PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                            Kommentar


                            • #15
                              Zitat von erc Beitrag anzeigen
                              Allgemein gilt Subqueries möglichst ins FROM. Damit werden sie nur einmal ausgeführt. Im SELECT nur wenn mit wenigen Datensätzen gerechnet wird (z.B. mit LIMIT begrenzt). Im WHERE auch nur wenn nicht dependent oder mit wenigen Datensätzen zu rechnen ist (LIMIT hilft hier nicht).
                              Was denkt ihr passiert wenn in das Ergebnis die Datensätze des letzten Jahres einfließen oder nur die Datensätze die > NOW() sind?! Ja, es macht ein Unterscheid ob 2 Subqueries jeweils für 2000 Datensätze ausgeführt werden müssen oder doch nur für 0 Datensätze.
                              Und wie kommt man auf den Trichter das 2000 casts irgendein spürbaren einfluss auf die Performance hätten? Selbe Logik wie "Wir casten leiber n mal zum Datentyp des statischen Wertes", als "Wir casten den statischen Wert einmal in den Datentyp der n Werte"?

                              Fakt ist die Performance geht durch die Subqueries in den Keller. Da kann Thallius noch soviele "Messungen" hier posten. Natürlich könnte man jetzt noch versuchen rauszufinden warum die Ergebnisse aussehen wie aus dem Würfelbecher. Zur Lösung wird das aber nix beitragen. Ich für meinen Teil habe eine Vorstellung warum...
                              Ein Lösungsansatz habe ich bereits gepostet.

                              Zitat von ChristianK Beitrag anzeigen
                              Was mir jetzt auffällt ist, das der äusserste SELECT keinen Index benutzen kann und eine temporäre Tabelle nutzt. Das Problem ist, dass er das Enddate wirklich für alle ausrechnen muss.
                              Das ist so in Ordnung. Wenn der Key mehr als x Prozent der Tabelle trifft ist ein Table Scan billiger. Das springt ab einen gewissen Punkt automatisch um. Enddate wird auch nicht berechnet, das ist ein Vergleich mit einem statischen Wert. Würdes du DATE_ADD(order_enddate, INTERVAL 1 YEAR) > NOW() machen würde der Wert für jeden Datensatz berechnet werden. Dann würde der Key aber auch nicht als Kandidat aufgeführt werden.

                              Kommentar

                              Lädt...
                              X