Improving script execution time

Question

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.

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). Are your tables properly indexed? — Jay Blanchard, Jul 07 '15 at 19:54
So you know how long the script takes to implement, but do you know exactly how long each part takes? I'm making the assumption it's your while loop and not your `insert` statement but that may not be the case. — the_pete, Jul 07 '15 at 19:55
`"SELECT PlayerId FROM tblPlayerPoints WHERE Game='" . $gamenumber. "'";` You are selecting all player ids from the table, there is no filter besides `$gamenumber`, If I had to take a shot in the dark, before I suggest checking your indexes and data types, it would be to make that query more robust. — the_pete, Jul 07 '15 at 20:01
The query `"SELECT PlayerId FROM tblPlayerPoints WHERE Game='" . $gamenumber. "'"` is run for each player, but it's always the same query ! You should only do it only once outside the loop or add a `player` parameter maybe. — Mat, Jul 07 '15 at 20:05
@the_pete the only thing in his while loop relevant is the query. Even a 2nd query in there gets a fresh 30 sec timer — Drew, Jul 07 '15 at 20:05
his `SQL` statements don't even match up, `WHERE Game=` there is no column `Game` according to his `INSERT` statement — cmorrissey, Jul 07 '15 at 20:07
@the_pete It is correct the while loop takes 21.3 seconds of the entire execution time. The insert itself (when really executed) doesn't take that long. — neuzehie, Jul 07 '15 at 20:09
It seems there are a lot of things wrong with what we have here, chief among them being a SQL statement in a loop that will never change, retrieving the same data over and over. — Jerbot, Jul 07 '15 at 20:10
The while loop is irrelevant. The while loop can take 17 years and not cause a 30 second timeout — Drew, Jul 07 '15 at 20:10
@cmorrissey correct, it's just a typing error. I've changed Gamenumber to Game in the insert statement. — neuzehie, Jul 07 '15 at 20:11

score 3 · Answer 1 · answered Jul 07 '15 at 20:06

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.

score 1 · Answer 2 · answered Jul 07 '15 at 20:19

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.

score 1 · Accepted Answer · answered Jul 07 '15 at 20:24

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

score -1 · Answer 4 · answered Jul 07 '15 at 20:03

-1

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.

answered Jul 07 '15 at 20:03

Pratik Soni

2,498
16
26

Improving script execution time

4 Answers4