Menu
I am currently having problems with the speed of uploading and processing employee information. A tab delimited file containing employee names and a set of category of rating criteria for each of the employees. There are close to 6500 employees beiing processed.
The thing is, it takes around 16 mins or so to process the 6000+ employees. Is it possible to speed it up?
Hope someone can optimize this.
Below is the code along with the functions it calls.
[code=php]
function getEmpFactorIdFromName($ef_name)
{
$db = getdb();
$query = “SELECT ef_id FROM rew_employee_factors WHERE UPPER(ef_name) = UPPER(‘$ef_name’)”;
$result = $db->query($query);
$row = $result->fetch_assoc();
return $row[‘ef_id’];
}
function getEmpCategoryFactor($ef_id, $category, $ef_ranges)
{
$db = getdb();
if ($ef_ranges[$ef_id] == 1)
$query = “SELECT factor FROM rew_employee_values WHERE category = floor($category) AND ef_id = $ef_id”;
else
$query = “SELECT factor FROM rew_employee_values WHERE UPPER(category) = UPPER(‘$category’) AND ef_id = $ef_id”;
$result = $db->query($query);
$row = $result->fetch_assoc();
return $row[“factor”];
}
if($_SERVER[‘REQUEST_METHOD’] == “POST”)
{
$rewardsdb = getdb();
$today = date(“Y-m-d H:i:s”);
$somename = basename($_FILES[‘targetfile’][‘name’]);
$uploadfile = $_FILES[‘targetfile’][‘tmp_name’];
$target_path = getUploadPath() . $somename;
move_uploaded_file($uploadfile, $target_path);
$fcontents = file($target_path);
$num = 1;
$weight = 1;
$totalWeight = 0;
$ratingArray = array();
foreach($fcontents as $line)
{
if ($num == 1)
{
$data = explode(“t”, $line);
$numCols = count($data);
if (strtoupper($data[0]) == “TIN NUMBER”)
{
//column 5 is the start of the ratings
for ($i = 4; $i < $numCols; $i++)
{
$ef_id = getEmpFactorIdFromName(trim($data[$i]));
$contents .= “$data[$i] == $ef_id<br>rn”;
$ratingArray[$i] = $ef_id;
}
$rewardsdb->query(“TRUNCATE rew_employee_ratings”);
$rewardsdb->query(“TRUNCATE rew_employees”);
$del3 = “DELETE FROM system_var WHERE variable = ’empe_last’ OR variable = ’empe_user’ OR variable = ’empe_file'”;
$rewardsdb->query($del3);
$qry = “INSERT INTO system_var VALUES (’empe_last’, ‘$today’), (’empe_user’, ‘$user’), (’empe_file’, ‘$somename’)”;
$rewardsdb->query($qry);
$message = “Individual Evaluation File uploaded.”;
}
else
{
$message = “Wrong individual evaluation file format.”;
break;
}
}
else
{
$data = explode(“t”, $line);
$numCols = count($data);
$tin = $data[0];
$lname = $data[1];
$lname = trim($lname, “””);
$lname = addslashes($lname);
$fname = $data[2];
$fname = trim($fname, “””);
$fname = addslashes($fname);
$mi = trim($data[3]);
//column 5 is the start of the ratings
for ($i = 4; $i < $numCols; $i++)
{
$factor = getEmpCategoryFactor($ratingArray[$i], trim($data[$i]), $ef_ranges);
$qry = “INSERT INTO rew_employee_ratings VALUES ($tin, $ratingArray[$i], $factor)”;
echo $qry . “<br>”;
$contents .= “⇒$qry<br>rn”;
$rewardsdb->query($qry);
$weight *= $factor;
}
$qry = “INSERT INTO rew_employees (tin_num, last_name, first_name, middle_init, weight)
VALUES ($tin, ‘$lname’, ‘$fname’, ‘$mi’, $weight)”;
echo $qry . “<br>”;
$contents .= “$qry<br>rn”;
$rewardsdb->query($qry);
$totalWeight += $weight;
$weight = 1;
}
$num++;
}
$contents .= <<<EOT
<strong>$message</strong>
EOT;
}