在循环关联数组时优化SQL更新

Question

I'm iterating over an associative array with key value pairs. Inside the loop there's an SQL Update, SQL Select and another Update. Is it possible to combine two or all of those statements in one to reduce execution time? In short: the foreach is looping through given points to a player. It should add those points to the points table. It then should take the players total current points and add the new points (value of the foreach) to that total. Then, it should update the players total score.

This code does work and give correct results, but just takes to long to run when having lots of players. It gives me a total execution time of 12 seconds to run this script with only 800 players so far.

foreach($arrPoints as $key => $value)
{
    $sqlUpdatePoints = "UPDATE tblPoints SET Points = '$value' WHERE Game='FPS' AND PlayerId ='$key' AND Gamenumber = 2";

    $PointsBefore=0;$PointsAfter=0;
    $sqlReadPoints = "SELECT TotalPointsBefore, TotalPointsAfter FROM tblplayer WHERE Id=$key";
    $parameters=array('');
    $resultReadPoints = dataQuery($sqlReadPoints,$parameters);
    foreach($resultReadPoints AS $rowRP)
    {
        $PointsBefore= $rowRP['TotalPointsBefore'];
        $PointsAfter= $rowRP['TotalPointsAfter'];
    }
    $NewTotalBefore= $PointsBefore + $value;
    $NewTotalAfter= $PointsAfter+ $value;
    $sqlUpdateNewPoints = "UPDATE tblplayer SET TotalPointsBefore='$NewTotalBefore', TotalPointsAfter='$NewTotalAfter' WHERE Id=$key";
}

Answer 1

I solved the issue. I could eliminate the foreach by incrementing the value inside the update statement itself. This reduces execution time a lot because no lookup needs to happen. Final execution not included in below sample, should be done using PDO as statet by @ArtisticPhoenix

foreach($arrPoints as $key => $value)
{
    $sqlUpdatePoints = "UPDATE tblPoints SET Points = '$value' WHERE Game='FPS' AND PlayerId ='$key' AND Gamenumber = 2";
    $sqlUpdateNewPoints = "UPDATE tblplayer SET TotalPointsBefore=TotalPointsBefore+$value, TotalPointsAfter=TotalPointsAfter+$value WHERE Id=$key";
}