Update-Performance bei Tabellen mit vielen Einträgen

[Niko310391]

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:

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".

[Dormilich]

Gibt es eine Möglichkeit die Performance des Updates zu verbessern?

Prepared Statements.

[hausl]

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.

[Niko310391]

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):

$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); 

[hausl]

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.

[Niko310391]

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
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;*/
?>

[akretschmer]

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 ...

[hausl]

@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.

[jspit]

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.

[akretschmer]

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.

[Niko310391]

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.

[akretschmer]

das hier:

$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.

[Niko310391]

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

[akretschmer]

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

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.

[Niko310391]

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

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?