Ankündigung

Einklappen
Keine Ankündigung bisher.

Update-Performance bei Tabellen mit vielen Einträgen

Einklappen

Neue Werbung 2019

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

  • #31
    Also nach Rücksprache mit meinem Webhosting-Anbieter (all-inkl) steht aktuell noch keine MySQL Version 8 auf deren Servern zur Verfügung. Daher kann ich bisher noch nicht mit den With-Statements arbeiten. Ab wann dies dort möglich ist, konnte mir vom Support nicht gesagt werden.
    Gibt es eine Alternative für eine performantere Anwendung?

    Kommentar


    • #32
      With macht das Statement hübsch und übersichtlich, du kannst das Statement aber auch mit Joins schreiben.
      Relax, you're doing fine.
      RTFM | php.de Wissenssammlung | Datenbankindizes | Dateien in der DB?

      Kommentar


      • #33
        Zunächst einmal auch danke an hausl für den Tipp mit der Berechnung des Alters. Funktioniert einwandfrei und erspart mir einige Zeilen Code in PHP.
        Ich verstehe noch nicht, inwieweit ich in einem SQL Statement eine komplette PHP Berechnung von Effekten & Auswirkungen eines Trainingstages umsetzen soll.

        Folgende Daten brauche ich dafür aus der Datenbank:

        1. Hole alle Vereinsteams
        PHP-Code:
        SELECT teamid FROM teams WHERE active '1' AND nationalteam '2' 
        2. Hole Trainingseinheiten des Tages
        PHP-Code:
        SELECT trainingunit FROM teamtrainings WHERE team_id 'x' AND year 'x' AND month 'x' AND day 'x' 
        3. Hole Trainingsauswirkungen der Trainingseinheiten
        PHP-Code:
        SELECT ability_ideffect FROM trainingeffects WHERE trainingunit_id 'x' 
        4. Hole Spieler des Teams sowie deren Informationen
        PHP-Code:
        SELECT players.playeridplayers.talentplayers.fitnessplayers.freshnessplayers.training_intensity FROM playerteams INNER JOIN players ON playerteams.player_id players.playerid WHERE playerteams.team_id 'x' AND (playerteams.begin IS NOT Null AND playerteams.end IS Null
        5. Hole aktuellen Fähigkeitsstand dieser Spieler
        PHP-Code:
        SELECT playerabilities.player_idplayerabilities.ability_idplayerabilities.valueplayerabilities.training_level FROM playerabilities INNER JOIN playerteams ON playerabilities.player_id playerteams.player_id WHERE playerteams.team_id 'x' AND (playerteams.begin IS NOT Null AND playerteams.end IS Null
        Das sind eigentlich alle SELECTS, die eine Rolle spielen. Allerdings habe ich absolut keine Ahnung wie ich diese zusammenfassen kann. Außerdem muss dies ja für JEDES Team passieren, was im 1. Select-Statement gefunden wird.

        Kommentar


        • #34
          Außerdem muss dies ja für JEDES Team passieren, was im 1. Select-Statement gefunden wird.
          Das ist genau das, was ein JOIN macht.

          Kommentar


          • #35
            Dann müsste es ja folgender Select sein, oder?

            PHP-Code:
            SELECT t.teamidtt.trainingunitte.ability_idte.effectp.playeridTIMESTAMPDIFF(YEARp.birthdateCURDATE()) AS agep.talentp.fitnessp.freshnessp.training_intensitypa.ability_idpa.valuepa.training_level
            FROM teams t
            LEFT JOIN teamtrainings tt ON t
            .teamid tt.team_id
            LEFT JOIN trainingeffects te ON tt
            .trainingunit te.trainingunit_id
            LEFT JOIN playerteams pt ON t
            .teamid pt.team_id
            LEFT JOIN players p ON pt
            .player_id p.playerid
            LEFT JOIN playerabilities pa ON p
            .playerid pa.player_id
            WHERE t
            .active '1'
                
            AND t.nationalteam '2'
                
            AND tt.year '2018'
                
            AND tt.month '07'
                
            AND tt.day '01'
                
            AND (pt.begin IS NOT Null AND pt.end IS Null
            Aber wie führe ich dann hier bereits die ganzen Berechnungen sowie das Update durch?

            Kommentar


            • #36
              Dann müsste es ja folgender Select sein, oder?
              Ich kenne deine Tabellen nicht, daher kann ich nichts dazu sagen, außer dass ein INNER JOIN wahrscheinlich angebrachter ist.

              Aber wie führe ich dann hier bereits die ganzen Berechnungen sowie das Update durch?
              Mit den mathematischen Funktionen von MySQL in einem UPDATE Statement.

              Kommentar


              • #37
                Unabhängig davon, ob ich es mit LEFT JOIN oder INNER JOIN mache, bekomme ich für 1 Team ein Ergebnis von 4080 Zeilen.
                Ich stelle mal Ergebnis sowie erforderliche Tabellen zur Verfügung. Ich würde mich freuen, wenn mir dann jemand hier helfen könnte.

                https://www.file-upload.net/download...sults.zip.html

                Kommentar


                • #38
                  Ich hab jetzt nicht die Beiträge alle gelesen bzw. steige ich nicht mehr durch. Das Thema ist hier immer noch ein schnelles Update aus Arrays? Wenn nicht, das nachfolgende vergessen.
                  Updates über Schleifen sind extrem langsam (Die Tabellen stammen aus einem Test der gegen eine echte MySQL-DB läuft)
                  .
                  update tab2 with for 145 for($i=1; $i<=1024; $i++){
                  $sql = "UPDATE tab2 SET val=".($i+1)." WHERE id=$i";
                  $result = $db->exec($sql);
                  }
                  $t->check($result, $result !== false);
                  [7550.2 ms]
                  1
                  Ok
                  .
                  Prepared Statements bringen auch keine Punkte
                  .
                  update with prepared statements 152 $sql = "UPDATE tab2 SET val=:val WHERE id=:id";
                  $stmt = $db->prepare($sql);
                  for($i=1; $i<=1024; $i++){
                  $param = array(
                  "val" => $i+2,
                  "id" => $i,
                  );
                  $result = $stmt->execute($param);
                  }
                  $t->check($result, $result !== false);
                  [9115.1 ms]
                  true
                  Ok
                  .
                  Das Multi-Inserts sehr schnell sind, darauf habe ich schon hingewiesen. Und das Updates aus einer anderen (Update)Tabelle schnell sind ist ja auch bekannt. Warum nicht beides kombinieren?
                  .
                  create update-Table 164 $sql = "CREATE TABLE `updatetab2` (
                  `id` int(11) NOT NULL,
                  `val` DOUBLE NOT NULL DEFAULT '0',
                  PRIMARY KEY (`id`)
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test'";
                  $result = $db->execSQL($sql);
                  $t->check($result, $result !== false);
                  [3.9 ms]
                  0
                  Ok
                  insert update data from array 173 $rowCount = 1024;
                  $data = array();
                  for($i=1; $i<=$rowCount; $i++){
                  $data[] = array(
                  $i,
                  (float)($i+10.5)
                  );
                  }
                  $assign = array(
                  0 => 'id',
                  1 => 'val'
                  );
                  $result = $db->insertArray('updatetab2',$assign,$data);
                  $t->checkEqual($result, $rowCount);
                  [24.1 ms]
                  1024
                  Ok
                  update tab2 from update-Table 189 $sql = "UPDATE tab2, updatetab2
                  SET tab2.val = updatetab2.val
                  WHERE tab2.id = updatetab2.id";
                  $result = $db->execSQL($sql);
                  $t->check($result, $result !== false);
                  [14.2 ms]
                  1024
                  Ok
                  show first row 196 $sql = "SELECT id, val FROM tab2 WHERE id=1";
                  $row = $db->getSingleValue($sql);
                  $expected = array('id' => '1', 'val' => '11.5');
                  $t->checkEqual($row, $expected);
                  [0.7 ms]
                  array ( 'id' => '1', 'val' => '11.5', )
                  Ok
                  .
                  Rechne ich die Zeiten zusammen komme ich auf knapp. 50ms im Vergleich zu 7500ms beim Einzelupdate. Der kritische Punkt ist der Multi-Insert. Bei sehr großen Arrays (habe ich noch nicht getestet) muß eventuell gesplittet werden.

                  LG jspit

                  Edit: Die Methode insertArray erzeugt hier ein SQL wie
                  Code:
                  INSERT INTO `updatetab2` ( `id`,`val` ) VALUES (1,'11.5'),(2,'12.5') ...
                  Edit2: Wenn die Daten für ein Update aus anderen Tabellen stammen kann die (gedachte) Updatetabelle natürlich auch daraus erstellt werden.
                  PHP-Klassen auf github

                  Kommentar


                  • #39
                    Zitat von jspit Beitrag anzeigen
                    I
                    Rechne ich die Zeiten zusammen komme ich auf knapp. 50ms im Vergleich zu 7500ms beim Einzelupdate. Der kritische Punkt ist der Multi-Insert. Bei sehr großen Arrays (habe ich noch nicht getestet) muß eventuell gesplittet werden.
                    Der Kritische Punkt ist nicht das Multi Insert, dieses ist unlimitiert, Grenzen gibt e aber beim Speicher(RAM) und paket size(bei MySQL 1 MByte). Paket Size kann man aber hoch setzen siehe https://dev.mysql.com/doc/refman/8.0...too-large.html
                    Bei 100000 Datensätzen hat es mit autoincrement id 2.359375 Sekunden gedauert ggü. 50000 DS mit 1.9375 Sekunden (Mit Windows lokal getestet, keine Optimierungen vorgenommen)
                    Die Grösse spielt hier also fast eine Nebenrolle.
                    Weitere Indexe sollte man aber nicht haben und erst am Ende neu erzeugen, da sonst die Zeiten signifikant schlechter werden.


                    Kommentar


                    • #40
                      jspit Ich habe es gerade mal getestet und komme auf 4,16 Sekunden. In die Update-Tabelle werden in diesem Beispiel 35.744 Zeilen geschrieben (kann variieren, je nach Trainingseinheiten durch unterschiedlich viele Effekte dieser).
                      Ich gehe davon aus, dass die Abweichung der Zeit durch meine vorherige Berechnung in PHP passiert. Jetzt wäre ich daher noch sehr dankbar, wenn mir hier bei der Optimierung noch geholfen werden könnte.

                      PHP-Code:
                      mysqli_query($connection,"CREATE TABLE updateTable (id int(10) NOT NULL, playerid int(10) DEFAULT NULL, abilityid int(10) DEFAULT NULL, value int(10) DEFAULT NULL, training_level int(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8");
                      mysqli_query($connection,"ALTER TABLE updateTable ADD PRIMARY KEY (id)");
                      mysqli_query($connection,"ALTER TABLE updateTable MODIFY id int(10) NOT NULL AUTO_INCREMENT");

                      if(
                      is_array($arUpdatePlayerabilities)):
                          
                      $i 0;

                          foreach(
                      $arUpdatePlayerabilities AS $playerid => $value):
                              foreach(
                      $value AS $abilityid => $key):
                                  if(
                      $i == 0):
                                      
                      $string .= "(".$playerid.", ".$abilityid.", ".$key["value"].", ".$key["training_level"].")";
                                  else:
                                      
                      $string .= ", (".$playerid.", ".$abilityid.", ".$key["value"].", ".$key["training_level"].")";
                                  endif;

                                  
                      $i++;
                              endforeach;
                          endforeach;
                      endif;

                      mysqli_query($connection,"INSERT INTO updateTable (playerid, abilityid, value, training_level) VALUES ".$string);

                      mysqli_query($connection,"UPDATE playerabilities_test, updateTable SET playerabilities_test.value = updateTable.value, playerabilities_test.training_level = updateTable.training_level WHERE playerabilities_test.player_id = updateTable.playerid AND playerabilities_test.ability_id = updateTable.abilityid");

                      mysqli_query($connection,"DROP TABLE updateTable"); 

                      Kommentar


                      • #41
                        Zitat von protestix Beitrag anzeigen
                        Der Kritische Punkt ist nicht das Multi Insert, dieses ist unlimitiert, Grenzen gibt e aber beim Speicher(RAM) und paket size(bei MySQL 1 MByte). Paket Size kann man aber hoch setzen siehe https://dev.mysql.com/doc/refman/8.0...too-large.html
                        Ja, bei mir kommt da schon beim Erstellen von 30.000 Testdaten ein "MySQL server has gone away". Der Speicherbedarf des Test ist auch nicht ohne: Memory: 17.5M (128M).

                        Niko310391 : Die genannte Zeit von 4 Sekunden ist doch gar nicht so schlecht zu den gefühlten 30 Sekunden im Startbeitrag. Für die Feinoptimierung setzte doch erstmal Zeitmarken (microtime(true) -> array) um zu ergründen wo genau die Zeit bleibt. Kannst dich dafür auch gerne der Klassen debug und oder phpcheck bedienen.
                        Ich zähle mich nicht zu den DB-Spezis und habe zudem von MySQLi null Ahnung (Nutze nur PDO).

                        PHP-Klassen auf github

                        Kommentar


                        • #42
                          Zitat von jspit Beitrag anzeigen
                          Ja, bei mir kommt da schon beim Erstellen von 30.000 Testdaten ein "MySQL server has gone away". Der Speicherbedarf des Test ist auch nicht ohne: Memory: 17.5M (128M).
                          Das erstaunt mich, bei 30.000 kann man ja noch nicht wirklich von "viel" reden...

                          Kommentar


                          • #43
                            Zitat von kaminbausatz Beitrag anzeigen
                            Das erstaunt mich, bei 30.000 kann man ja noch nicht wirklich von "viel" reden...
                            Das ist genau die 1 MB Grenze von der protestix #39 gesprochen hat. 25.000 Datensätze für ein Insert mit ca. 960KByte Umfang passt bei mir grad noch.

                            Edit: Dich hat "MySQL server has gone away" erstaunt. Der MySQL-Server ist da nicht down, nur die Verbindung zum Client.
                            PHP-Klassen auf github

                            Kommentar


                            • #44
                              Zitat von jspit Beitrag anzeigen
                              Die genannte Zeit von 4 Sekunden ist doch gar nicht so schlecht zu den gefühlten 30 Sekunden im Startbeitrag. Für die Feinoptimierung setzte doch erstmal Zeitmarken (microtime(true) -> array) um zu ergründen wo genau die Zeit bleibt. Kannst dich dafür auch gerne der Klassen debug und oder phpcheck bedienen.
                              Das werde ich mal versuchen. Aber rein vom Gefühl her würde ich behaupten, dass die Zeit bei der Ermittlung der Effekte draufgeht.

                              Kommentar


                              • #45
                                Ich möchte einfach, rein aus Interesse nochmal fragen...

                                Müssen diese Berechnungen denn ausgeführt werden, wenn ein User aktiv eine Seite aufruft?
                                Auf dem ersten Blick wäre meine Vermutung, dass das einfach ein Trainingsscript ist, dass auch regemäßig um 00:01 Uhr per CronJob aufgerufen werden kann und dann im Hintergrund seinen Job erledigt.
                                Relax, you're doing fine.
                                RTFM | php.de Wissenssammlung | Datenbankindizes | Dateien in der DB?

                                Kommentar

                                Lädt...
                                X