CODEWITHSUNDEEP
phpmysql
neuzehie
neuzehie

Reputation: 152

Improving script execution time

I want to improve the execution time of an insert query, because my webhost will not change the max_execution_time. The script below contains a test for the wallclock execution time. This results in 22 seconds execution time which is way too long because the webhost keeps the default at 30 seconds. After the code below, more code needs to be executed. For reference, there are about 1000 players in tblPlayers.

$time_start = microtime(true);

$sqlTotalPoints = array();
$sqlCompetingPlayers = "SELECT Id FROM tblPlayers WHERE Game='" . $gamenumber. "' AND Joined='1'";
$resultCompetingPlayers = mysql_query($sqlCompetingPlayers);
while($row= mysql_fetch_array($resultCompetingPlayers))
{
    $PlayerId = $row['Id'];
    $sqlAlreadyHasPoints = "SELECT PlayerId FROM tblPlayerPoints WHERE PlayerId='" . $PlayerId . "'";
    $resultAlreadyHasPoints = mysql_query($sqlAlreadyHasPoints);
    $PointsRowFound = mysql_num_rows($resultAlreadyHasPoints);
    if($PointsRowFound < 1 ) {
        $sqlTotalPoints[] = '("' . $gamenumber . '", "FPS", "' . $PlayerId . '", "0")';
    }
}
echo 'INSERT INTO tblPlayerPoints (Game,GameNaam,PlayerId,PointsCollected) VALUES ' . implode(',',$sqlTotalPoints);

$time_end = microtime(true);
$execution_time = ($time_end - $time_start);
echo '<b>Total Execution Time:</b> '.$execution_time.' secs';

This results in a correct INSERT statement and a Total Execution Time of 22 seconds.

I used to have the insert inside the while loop instead of imploding but that execution time was even worse.

Upvotes: 2

Views: 71

Answers (4)

cmorrissey
cmorrissey

Reputation: 8583

Here is a single SQL statement for you, This one is slightly different then the other one posted as it will only add new rows if one does not exist.

INSERT INTO `tblPlayerPoints` (`Game`,`GameNaam`,`PlayerId`,`PointsCollected`)
SELECT `tblPlayers`.`Game`, 'FPS', `tblPlayers`.`Id`, 0 
    FROM `tblPlayers`
    LEFT JOIN `tblPlayerPoints` ON `tblPlayers`.`Id` = `tblPlayerPoints`.`PlayerId` AND `tblPlayers`.`Game` = `tblPlayerPoints`.`Game` 
WHERE `tblPlayers`.`Game`='" . $gamenumber. "' AND `tblPlayers`.`Joined`='1' AND `tblPlayerPoints`.`PointesCollected` IS NULL

Upvotes: 1

Chris Fremgen
Chris Fremgen

Reputation: 5358

As others have mentioned, it's best to use 1 SQL statement.

"INSERT INTO tblPlayerPoints(Gamenumber,GameNaam,PlayerId,PointsCollected) ".
"SELECT tp.Id, 'FPS', tpp.PlayerID, 0 " .
" FROM tblPlayers tp " .
" JOIN tblPlayerPoints tpp ON tp.gamenumber = tpp.gamenumber " .
" WHERE tp.game = '" .$gamenumber ."' AND tp.joined = '1'"

You should add indexes to columns that exist in the WHERE clause (or JOIN):

CREATE INDEX IX_TBLPLAYERS_1 ON tblPlayers(gamenumber);
CREATE INDEX IX_TBLPLAYERS_2 ON tblPlayers(gamenumber, joined);

CREATE INDEX IX_TBLPLAYERPOINTS_1 ON tblPlayerPoints(gamenumber);

Also, I would recommend using an integer value for gamenumber and joined. Integers are much quicker to lookup.

Upvotes: 1

Parris Varney
Parris Varney

Reputation: 11478

This entire bit of code can be done in a single SQL statement. Try working with an INSERT INTO ... SELECT type query, and join your two selection tables instead of looping over the results of the first. Even with no table indexes, your data size is small enough where you shouldn't be worrying about time limits.

Upvotes: 3

Pratik Soni
Pratik Soni

Reputation: 2588

The best way is to use the below query.

INSERT INTO tblPlayerPoints
    (Gamenumber,GameNaam,PlayerId,PointsCollected)

SELECT col1,col2,col3,col4

FROM

tblPlayers //Your table from which you are retrieving data.

Upvotes: -1

Related Questions