Ankündigung

Einklappen
Keine Ankündigung bisher.

Update-Performance bei Tabellen mit vielen Einträgen

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

  • Update-Performance bei Tabellen mit vielen Einträgen

    Hallo,

    im Rahmen eines Codes bilde ich bei mir zwei "Update-Arrays", die jeweils rund 20.000 Einträge nach folgendem Schema besitzen:

    $array1[$id]["wert1"] = 100
    $array1[$id]["wert2"] = 100

    $array2[$id][$id2]["wert"] = 100
    $array2[$id][$id3]["wert"] = 100

    Für diese Arrays habe ich am Ende meines Codes einen Update-Befehl in meine MySQL Datenbank, welcher aber extrem lange dauert (ca. 20 Sekunden bestimmt). Das Ganze läuft hier über eine foreach-Schleife mit mysqli-Update Befehlen:

    PHP-Code:
    foreach($arUpdatePlayers AS $playerid => $key):
        
    mysqli_query($connection,"UPDATE players SET fitness = '".$key["fitness"]."', freshness = '".$key["freshness"]."' WHERE playerid = '$playerid'");
    endforeach;

    foreach(
    $arUpdatePlayerabilities AS $playerid => $value):
        foreach(
    $value AS $abilityid => $key):
            
    mysqli_query($connection,"UPDATE playerabilities SET value = '".$key["value"]."', training_level = '".$key["training_level"]."' WHERE player_id = '$playerid' AND ability_id = '$abilityid'");
        endforeach;
    endforeach; 
    Diese lange Update-Zeit ist nicht wünschenswert und auch nicht tragbar. Gibt es eine Möglichkeit die Performance des Updates zu verbessern?

    Infos zur Datenbank

    Meine Datenbank-Tabellen haben als Engine InnoDB.
    In der Tabelle players gibt es den Primärschlüssel "playerid" und einen Index auf der Spalte "position_id"..
    In der Tabelle playerabilities gibt es den Primärschlüssel "playerabilityid" und einen Index auf den Spalten "player_id" und "ability_id".


  • #2
    Gibt es eine Möglichkeit die Performance des Updates zu verbessern?
    Prepared Statements.

    Kommentar


    • #3
      Du hast abgesehen davon eine SQL-Injection Lücke / Fehlerquelle in deiner Query. Wenn du wie oben erwähnt PS nutzt, dann hast du damit beides erledigt.
      Debugging: Finde DEINE Fehler selbst! | Gegen Probleme beim E-Mail-Versand | Sicheres Passwort-Hashing | Includes niemals ohne __DIR__
      PHP.de Wissenssammlung | Kein Support per PN

      Kommentar


      • #4
        Ich habe das Ganze jetzt mal als Prepared Statement (prozedural) umgeschrieben, allerdings funktioniert es nun nicht wie gewünscht (der Wert bei "freshness" wird völlig falsch aktualisiert) und zweitens noch nicht schnell genug (ca. 12 Sekunden):

        PHP-Code:
        $stmt1 mysqli_stmt_init($connection);
        if(
        mysqli_stmt_prepare($stmt1"UPDATE players SET fitness = ?, freshness = ? WHERE playerid = ?")):
            foreach(
        $arUpdatePlayers AS $playerid => $key):
                
        mysqli_stmt_bind_param($stmt1"iii"$key["fitness"], $key["freshness"], $playerid);
                
        mysqli_stmt_execute($stmt1);
            endforeach;
        endif;
        mysqli_stmt_close($stmt1);

        $stmt2 mysqli_stmt_init($connection);
        if(
        mysqli_stmt_prepare($stmt2"UPDATE playerabilities SET value = ?, training_level = ? WHERE player_id = ? AND ability_id = ?")):
            foreach(
        $arUpdatePlayerabilities AS $playerid => $value):
                foreach(
        $value AS $abilityid => $key):
                    
        mysqli_stmt_bind_param($stmt2"iiii"$key["value"], $key["training_level"], $playerid$abilityid);
                    
        mysqli_stmt_execute($stmt2);
                endforeach;
            endforeach;
        endif;
        mysqli_stmt_close($stmt2); 

        Kommentar


        • #5
          Queries in Schleifen sollte man sowieso vermeiden.

          im Rahmen eines Codes bilde ich bei mir zwei "Update-Arrays", die jeweils rund 20.000 Einträge nach folgendem Schema besitzen:
          Hast du schonmal geprüft ob es nicht ohne der Arrays geht? Kannst du das nicht direkt auf der DB machen bspw.? Woher kommen die Daten? Die DB kann ja auch rechnen.
          Debugging: Finde DEINE Fehler selbst! | Gegen Probleme beim E-Mail-Versand | Sicheres Passwort-Hashing | Includes niemals ohne __DIR__
          PHP.de Wissenssammlung | Kein Support per PN

          Kommentar


          • #6
            Ich hänge mal das komplette Skript an, dann sieht man auch, wie ich die Werte berechne. Meines Erachtens nach kann die Berechnung nicht in der Datenbank durchgeführt werden.
            Also auf die Arrays werde ich wohl angewiesen sein.


            MOD: Script aus Anhang hier eingefügt

            PHP-Code:
            <?php
            include("../../../config.php");

            $query mysqli_query($connection,"SELECT team_id, trainingunits FROM teamtrainings WHERE year = '".date("Y"$date)."' AND month = '".date("m"$date)."' AND day = '".date("d"$date)."'");
            while(
            $db $query->fetch_object()):
                
            $arTT[$db->team_id]["trainingunits"] = $db->trainingunits;
            endwhile;

            $query mysqli_query($connection,"SELECT teamid FROM teams WHERE nationalteam = '2' AND active = '1'");
            while(
            $db $query->fetch_object()):
                
            $arPA         = array();
                
            $arPlayer     = array();

                
            ### Hole Spielerfähigkeiten für dieses Team ###
                
            $query_playerabilities mysqli_query($connection,"SELECT playerabilities.player_id, playerabilities.ability_id, playerabilities.value, playerabilities.training_level FROM playerabilities INNER JOIN playerteams ON playerabilities.player_id = playerteams.player_id WHERE playerteams.team_id = '$db->teamid' AND (playerteams.begin IS NOT Null AND playerteams.end IS Null)");
                while(
            $db_playerabilities $query_playerabilities->fetch_object()):
                    
            $arPA[$db_playerabilities->player_id][$db_playerabilities->ability_id]["value"]             = $db_playerabilities->value;
                    
            $arPA[$db_playerabilities->player_id][$db_playerabilities->ability_id]["training_level"]     = $db_playerabilities->training_level;
                endwhile;

                
            ### Hole Spieler für dieses Team ###
                
            $query_players mysqli_query($connection,"SELECT players.playerid, players.birthdate, players.talent, players.fitness, players.freshness, players.training_intensity FROM players INNER JOIN playerteams ON players.playerid = playerteams.player_id WHERE playerteams.team_id = '$db->teamid' AND (playerteams.begin IS NOT Null AND playerteams.end IS Null)");
                while(
            $db_players $query_players->fetch_object()):
                    
            $arPlayer[$db_players->playerid]["talent"]                 = $db_players->talent;
                    
            $arPlayer[$db_players->playerid]["fitness"]             = $db_players->fitness;
                    
            $arPlayer[$db_players->playerid]["freshness"]             = $db_players->freshness;
                    
            $arPlayer[$db_players->playerid]["training_intensity"]     = $db_players->training_intensity;

                    
            ### Berechnung Alter ###
                    
            $date1 date("d.m.Y"$db_players->birthdate);
                    
            $gebtag explode (".",$date1);     

                    
            $year     date("Y",$date);  
                    
            $month     date("n",$date);
                    
            $day     date("d",$date);

                    
            $age $year $gebtag[2];  
                    if(
            $month <= $gebtag[1]):
                        if(
            $month == $gebtag[1]):
                            if(
            $day $gebtag[0]):
                                
            $age $age 1;
                            endif;
                        else:
                            
            $age $age 1;
                        endif;
                    endif;

                    
            $arPlayer[$db_players->playerid]["age"]                 = $age;
                endwhile;

                
            ### kein konfigurierter Trainingsplan vorhanden ###
                
            if(!isset($arTT[$db->teamid])):
                    
            ### bei eigenem Team wird dann kein Training durchgeführt ###
                    
            if($db->teamid == $teamid):
                        
            $effect_fitness     = -8;
                        
            $effect_freshness     28;

                        foreach(
            $arPlayer AS $playerid => $key):
                            
            ### Fitness darf nicht unter 0 sinken ###
                            
            if(($key["fitness"] + $effect_fitness) < 0):
                                
            $fitness 0;
                            else:
                                
            $fitness $key["fitness"] + $effect_fitness;
                            endif;

                            
            ### Frische darf nicht über 100 steigen ###
                            
            if($key["freshness"] + $effect_freshness 100):
                                
            $freshness 100;
                            else:
                                
            $freshness $key["freshness"] + $effect_freshness;
                            endif;

                            
            $arUpdatePlayers[$playerid]["fitness"]         = $fitness;
                            
            $arUpdatePlayers[$playerid]["freshness"]     = $freshness;
                        endforeach;
                    
            ### KI-Teams bekommen den Default Trainingsplan ###
                    
            else:
                        
            $exp                 explode(",",$arDTP[date("m"$date)][date("d"$date)]["trainingunits"]);
                        
            $effect_fitness     0;
                        
            $effect_freshness     0;

                        
            $arUnits = array($exp[0],$exp[1],$exp[2],$exp[3]);

                        foreach(
            $arUnits AS $trainingunitid):
                            
            $effect_fitness     += $arTrainingunits[$trainingunitid]["effect_fitness"];
                            
            $effect_freshness     += $arTrainingunits[$trainingunitid]["effect_freshness"];

                            if(
            is_array($arTE[$trainingunitid])):
                                foreach(
            $arTE[$trainingunitid] AS $ability_id => $key):
                                    
            $arEffect[$ability_id]["effect"] = $key["effect"];
                                endforeach;
                            endif;
                        endforeach;

                        if(
            is_array($arPlayer)):
                            foreach(
            $arPlayer AS $playerid => $key):
                                
            ### berücksichtigen der Trainingsintensität des Spielers ###
                                
            $effect_fitness     round($effect_fitness * ($key["training_intensity"] / 100),0);
                                
            $effect_freshness     round($effect_freshness * ($key["training_intensity"] / 100) * (100 $key["fitness"]),0);

                                
            ### Fitness darf nicht unter 0 oder über 100 gehen ###
                                
            if(($key["fitness"] + $effect_fitness) > 100):
                                    
            $fitness 100;
                                elseif((
            $key["fitness"] + $effect_fitness) < 0):
                                    
            $fitness 0;
                                else:
                                    
            $fitness $key["fitness"] + $effect_fitness;
                                endif;

                                
            ### Frische darf nicht unter 0 oder über 100 gehen ###
                                
            if($key["freshness"] + $effect_freshness 100):
                                    
            $freshness 100;
                                elseif(
            $key["freshness"] + $effect_freshness 0):
                                    
            $freshness 0;
                                else:
                                    
            $freshness $key["freshness"] + $effect_freshness;
                                endif;

                                
            $arUpdatePlayers[$playerid]["fitness"]         = $fitness;
                                
            $arUpdatePlayers[$playerid]["freshness"]     = $freshness;

                                if(
            is_array($arEffect)):
                                    foreach(
            $arEffect AS $ability_id => $key2):
                                        
            $mult_age                $arGrowthAge[$key["age"]];
                                        
            $mult_talent            $arGrowthTalent[$key["talent"]];
                                        
            $mult_trainingintensity $key["training_intensity"] / 100;
                                        
            $mult_fitness             $key["fitness"] / 100;

                                        
            $effect_ability round($key2["effect"] * ($mult_age $mult_talent $mult_trainingintensity $mult_fitness),0);

                                        
            ### wenn Trainingslevel über 100 geht, dann steigere Fähigkeit ###
                                        
            if(($arPA[$playerid][$ability_id]["training_level"] + $effect_ability) > 100):
                                            
            $value_ability $arPA[$playerid][$ability_id]["value"] + 1;
                                            
            $level_ability $effect_ability - (100 $arPA[$playerid][$ability_id]["training_level"]);
                                        else:
                                            
            $value_ability $arPA[$playerid][$ability_id]["value"];
                                            
            $level_ability $arPA[$playerid][$ability_id]["training_level"] + $effect_ability;
                                        endif;

                                        
            $arUpdatePlayerabilities[$playerid][$ability_id]["value"]             = $value_ability;
                                        
            $arUpdatePlayerabilities[$playerid][$ability_id]["training_level"]     = $level_ability;
                                    endforeach;
                                endif;
                            endforeach;
                        endif;
                    endif;
                
            ### durchführen des Trainings auf Basis des eingestellten Trainings ###
                
            else:
                    
            $exp                 explode(",",$arTT[$db->teamid]["trainingunits"]);
                    
            $effect_fitness     0;
                    
            $effect_freshness     0;

                    
            $arUnits = array($exp[0],$exp[1],$exp[2],$exp[3]);

                    foreach(
            $arUnits AS $trainingunitid):
                        
            $effect_fitness     += $arTrainingunits[$trainingunitid]["effect_fitness"];
                        
            $effect_freshness     += $arTrainingunits[$trainingunitid]["effect_freshness"];

                        if(
            is_array($arTE[$trainingunitid])):
                            foreach(
            $arTE[$trainingunitid] AS $ability_id => $key):
                                
            $arEffect[$ability_id]["effect"] = $key["effect"];
                            endforeach;
                        endif;
                    endforeach;

                    if(
            is_array($arPlayer)):
                        foreach(
            $arPlayer AS $playerid => $key):
                            
            ### berücksichtigen der Trainingsintensität des Spielers ###
                            
            $effect_fitness     round($effect_fitness * ($key["training_intensity"] / 100),0);
                            
            $effect_freshness     round($effect_freshness * ($key["training_intensity"] / 100) * (100 $key["fitness"]),0);

                            
            ### Fitness darf nicht unter 0 oder über 100 gehen ###
                            
            if(($key["fitness"] + $effect_fitness) > 100):
                                
            $fitness 100;
                            elseif((
            $key["fitness"] + $effect_fitness) < 0):
                                
            $fitness 0;
                            else:
                                
            $fitness $key["fitness"] + $effect_fitness;
                            endif;

                            
            ### Frische darf nicht unter 0 oder über 100 gehen ###
                            
            if($key["freshness"] + $effect_freshness 100):
                                
            $freshness 100;
                            elseif(
            $key["freshness"] + $effect_freshness 0):
                                
            $freshness 0;
                            else:
                                
            $freshness $key["freshness"] + $effect_freshness;
                            endif;

                            
            $arUpdatePlayers[$playerid]["fitness"]         = $fitness;
                            
            $arUpdatePlayers[$playerid]["freshness"]     = $freshness;

                            if(
            is_array($arEffect)):
                                foreach(
            $arEffect AS $ability_id => $key2):
                                    
            $mult_age                $arGrowthAge[$key["age"]];
                                    
            $mult_talent            $arGrowthTalent[$key["talent"]];
                                    
            $mult_trainingintensity $key["training_intensity"] / 100;
                                    
            $mult_fitness             $key["fitness"] / 100;

                                    
            $effect_ability round($key2["effect"] * ($mult_age $mult_talent $mult_trainingintensity $mult_fitness),0);

                                    
            ### wenn Trainingslevel über 100 geht, dann steigere Fähigkeit ###
                                    
            if(($arPA[$playerid][$ability_id]["training_level"] + $effect_ability) > 100):
                                        
            $value_ability $arPA[$playerid][$ability_id]["value"] + 1;
                                        
            $level_ability $effect_ability - (100 $arPA[$playerid][$ability_id]["training_level"]);
                                    else:
                                        
            $value_ability $arPA[$playerid][$ability_id]["value"];
                                        
            $level_ability $arPA[$playerid][$ability_id]["training_level"] + $effect_ability;
                                    endif;

                                    
            $arUpdatePlayerabilities[$playerid][$ability_id]["value"]             = $value_ability;
                                    
            $arUpdatePlayerabilities[$playerid][$ability_id]["training_level"]     = $level_ability;
                                endforeach;
                            endif;
                        endforeach;
                    endif;
                endif;
            endwhile;

            $stmt1 mysqli_stmt_init($connection);
            if(
            mysqli_stmt_prepare($stmt1"UPDATE players SET fitness = ?, freshness = ? WHERE playerid = ?")):
                foreach(
            $arUpdatePlayers AS $playerid => $key):
                    
            mysqli_stmt_bind_param($stmt1"iii"$key["fitness"], $key["freshness"], $playerid);
                    
            mysqli_stmt_execute($stmt1);
                    
            mysqli_stmt_reset($stmt1);
                endforeach;
            endif;

            /*$stmt2 = mysqli_stmt_init($connection);
            if(mysqli_stmt_prepare($stmt2, "UPDATE playerabilities SET value = ?, training_level = ? WHERE player_id = ? AND ability_id = ?")):
                foreach($arUpdatePlayerabilities AS $playerid => $value):
                    foreach($value AS $abilityid => $key):
                        mysqli_stmt_bind_param($stmt2, "iiii", $key["value"], $key["training_level"], $playerid, $abilityid);
                        mysqli_stmt_execute($stmt2);
                    endforeach;
                endforeach;
            endif;
            mysqli_stmt_close($stmt2);*/


            /*foreach($arUpdatePlayers AS $playerid => $key):
                mysqli_query($connection,"UPDATE players SET fitness = '".$key["fitness"]."', freshness = '".$key["freshness"]."' WHERE playerid = '$playerid'");
            endforeach;

            foreach($arUpdatePlayerabilities AS $playerid => $value):
                foreach($value AS $abilityid => $key):
                    mysqli_query($connection,"UPDATE playerabilities SET value = '".$key["value"]."', training_level = '".$key["training_level"]."' WHERE player_id = '$playerid' AND ability_id = '$abilityid'");
                endforeach;
            endforeach;*/
            ?>

            Kommentar


            • #7
              Also ich zumindest werde jetzt nicht reverse versuchen zu ergründen, was Du da treibst. Aber Berechnungen kann man definitiv in der DB machen. Im Falle von z.B. PostgreSQL sogar in mehreren möglichen Programmiersprachen.
              Das hätte en Charm, das es ERHEBLICH schneller geht, als die Daten da raus zu klaubeln, damit rumzumachen und dann wieder zurück zu schreiben ...
              PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

              Kommentar


              • #8
                @Niko310391

                MOD: Bitte Scripts hier direkt mit den Code-Tags einfügen und keine Anhänge. Danke. Ich hab das oben mal gemacht für dich.
                Debugging: Finde DEINE Fehler selbst! | Gegen Probleme beim E-Mail-Versand | Sicheres Passwort-Hashing | Includes niemals ohne __DIR__
                PHP.de Wissenssammlung | Kein Support per PN

                Kommentar


                • #9
                  Das Thema ist schon mehrfach hier im Forum behandelt worden.
                  Zusammenfassung:
                  Prepared Statemnts bringen nicht die Masse an Geschwindigkeit, sog. Multi-Inserts schon mehr.
                  Wen konkrete Zeiten interessieren, hier ist ein Test der in Echtzeit läuft (Comment insert many rows from array ,Line 341, 1000 Inserts in 30ms ) zu sehen.
                  PHP-Klassen auf github

                  Kommentar


                  • #10
                    Zitat von jspit Beitrag anzeigen
                    Prepared Statemnts bringen nicht die Masse an Geschwindigkeit, sog. Multi-Inserts schon mehr.
                    Noch mehr bringt es, die Updates da nicht Zeile für Zeile zu machen, sondern direkt Updates auf die Tabelle zu fahren. Der Fragesteller möge niederschreiben, was wie und warum zu ändern ist, daraus kann man dann auch passende Update-Befehle erkennen.
                    PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                    Kommentar


                    • #11
                      jspit ich führe aber keine Inserts aus, sondern die Daten stehen bereits in der Datenbank und müssen lediglich aktualisiert werden
                      akretschmer Wie meinst du das denn? Also das Skript führt zunächst auf Basis diverser Parameter eine Berechnung von Trainingseffekten aus. Diese Effekte schreibe ich während der Berechnung in Arrays, welche ich dann am Ende des Skripts in einer foreach-Schleife in die Datenbank bringen möchte (via der Update-Befehle). Lasse ich die Arrays weg und schreibe direkt während der Berechnung in die Datenbank, bringt mir das keine Performance-Vorteile.

                      Kommentar


                      • #12
                        das hier:

                        Code:
                        $effect_fitness     = round($effect_fitness * ($key["training_intensity"] / 100),0);
                        $effect_freshness     = round($effect_freshness * ($key["training_intensity"] / 100) * (100 / $key["fitness"]),0);
                        
                        ### Fitness darf nicht unter 0 oder über 100 gehen ###
                        if(($key["fitness"] + $effect_fitness) > 100):
                        $fitness = 100;
                                            elseif(($key["fitness"] + $effect_fitness) < 0):
                        $fitness = 0;
                                            else:
                        $fitness = $key["fitness"] + $effect_fitness;
                                            endif;
                        kannst Du mit einem Update-Befehl auch in der DB ausführen. Für alle Datensätze. Mit einem Befehl.
                        PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                        Kommentar


                        • #13
                          akretschmer Magst du mir das vielleicht mal genauer erläutern, bzw. eventuell auch einmal zeigen und erklären?

                          Kommentar


                          • #14
                            Du kannst im Update selbst Berechnungen mit Spalten aus der Tabelle (oder anderen Tabellen) ausführen.

                            Code:
                            test=*# select * from foo;
                             a | b | c  | d
                            ---+---+----+---
                             5 | 6 | 10 | 0
                             7 | 8 | 10 | 0
                             1 | 2 |  3 | 0
                            (3 rows)
                            
                            test=*# update foo set c = case when a+b < 10 then a+b else 10 end, d=a*b;
                            UPDATE 3
                            test=*# select * from foo;
                             a | b | c  | d  
                            ---+---+----+----
                             5 | 6 | 10 | 30
                             7 | 8 | 10 | 56
                             1 | 2 |  3 |  2
                            (3 rows)
                            Das an Deine Bedürfnisse anzupassen überlasse ich Dir zur Übung.
                            PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                            Kommentar


                            • #15
                              Müsste dann ja anhand deines Beispiels wie folgt aussehen, richtig?

                              PHP-Code:
                              UPDATE players 
                              SET 
                              fitness 
                              = CASE WHEN fitness $effect_fitness 0 THEN 0 WHEN fitness $effect_fitness 100 THEN 100 ELSE fitness $effect_fitness END,
                              freshness = CASE WHEN freshness $effect_freshness 0 THEN 0 WHEN freshness $effect_freshness 100 THEN 100 ELSE freshness $effect_freshness END 
                              Zwei Fragen bleiben dann aber noch:

                              1. An welcher Stelle muss ich den Befehlt dann überhaupt ausführen? Ich befinde mich ja in einer while-Schleife, wo die Effekte sich ja in Abhängigkeit vom Spieler immer verändern? Dann müsste ich hier erneut mit Arrays arbeiten, oder?
                              2. Wie sieht es mit dem anderen Update-Befehl aus? Kann man diesen auch performanter durchführen?

                              Kommentar

                              Lädt...
                              X