Code help please

[marcmoustache]

I'm trying to write a piece of code to process the Extender files into a table so I can write my own bespoke reports on the data. However, the way I'm doing it below takes about 2 mins to run. Any ideas on how to speed it up?
function process_extender_files ($filename, $tablename, $year, $week){

$time=date('j:M:y H:i:s',time());echo "<br>## start $filename processing ## $time<br>";

$result = mysql_query("DELETE FROM ". $tablename. ";");

$fcontents = file ($filename);

for($i=1; $i<sizeof($fcontents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode(",", $line);

if($arr[0] == ''){
// do nothing it is the end of the file or the player name is missing
}
else if($arr[2]== '"FA"'){
// do nothing as it's a Free Agent
}

else{
$playerid=110;
$arr[0]=str_replace('"','', $arr[0]);

$pname=$arr[0];
$playerid=getplayerid($pname);

$arr[0]=addslashes($arr[0]);
$sql = "insert into ". $tablename ." values (" . "'". $year . "', '" . $week . "', '" . $playerid . "', '" . implode("', '", $arr) ."','0','0','0','0','0','0')";
mysql_query($sql);
echo $sql ."<br>\n";

if(mysql_error()) {echo mysql_error() ."<br>\n";
echo $arr[0] ." not imported<br>\n";}
}



}

[marcmoustache]

I get that inserting each record doesn't help but ...

[Ben E Lou]

Marc:
I am getting good results by just using a custom table with the extender weights, and then having the code do the calcs I need for the given report. Very fast, and no need to run extender at all to get the info.

[marcmoustache]

Marc:
I am getting good results by just using a custom table with the extender weights, and then having the code do the calcs I need for the given report. Very fast, and no need to run extender at all to get the info.

That would work ok, except I have my own stats d/b run off the boxscores and don't use Greg's DB Updater. :mad: I can't use my own data because the extender weights use first downs gained quite heavily, which aren't in the boxscores.

What I really want is to set it up so one of the other Owners can upload the extender reports and run some tailored reports for the forum.

[Ben E Lou]

Ahhhhhhhh

[marcmoustache]

Yes.

[marcmoustache]

I think this is the way to do it. Instead of lots of small inserts, I do a few big inserts:

for($i=$startrow; $i<$filecount; $i++) {
$line = trim($fcontents[$i]);
$arr = explode(",", $line);

if($arr[0] == ''){
// do nothing it is the end of the file or the player name is missing
}
#else if($arr[2]== '"FA"'){
// do nothing as it's a Free Agent
#}

else{
$playerid=110;
$arr[0]=str_replace('"','', $arr[0]);

$pname=$arr[0];
$playerid=getplayerid($pname);

$arr[0]=addslashes($arr[0]);


/*
$sql = "insert into ". $tablename ." values (" . "'". $year . "', '" . $week . "', '" . $playerid . "', '" . implode("', '", $arr) ."','0','0','0','0','0','0')";
mysql_query($sql);
echo $sql ."<br>\n";
*/

if($i<$filecount-1)
{
$insert .= "(" . "'". $year . "', '" . $week . "', '" . $playerid . "', '" . implode("', '", $arr) ."','0','0','0','0','0','0'),";
}
else
{
$insert .= "(" . "'". $year . "', '" . $week . "', '" . $playerid . "', '" . implode("', '", $arr) ."','0','0','0','0','0','0')";
}


}



}
$sql = "insert into ". $tablename ." values " . $insert;
mysql_query($sql);
if(mysql_error()) {echo mysql_error() ."<br>\n";
echo $arr[0] ." not imported<br>\n";}
echo $sql ."<br>\n";

[primelord]

You could probably increase the speed by not reading the entire file twice. You read the whole file into a variable and then go through it again to manipulate it. It would be more efficient to read in the file a line at a time and deal with the data as it comes in. Less memory used and less time running over the file. It may not make a huge difference if the file isn't all that big, but it is better coding practice anyway. something like the following untested code:


$file = fopen($filename, 'rb');
for ($line = fgets($file); ! feof($file); $line = fgets($file)) {
$line = trim($line);
$data = explode(',', $line);
if ($data[0] == "" || $data[2] == "FA") {
// Do nothing
} else {
// The rest of your code
}
}

[marcmoustache]

Ok, thanks, I definitely don't do things by the book, one of the problems of being self-taught! What I realised was that it's quicker to execute 5 big queries rather than 2,700 little queries!