Executing huge queries(60,000rows +)
Posted: Mon Aug 03, 2009 2:20 pm
Hey guys I have a problem. I'm porting a j2ee web application for my company to php. I have several parts of the script where I have to query over 60,000 rows. Is there anyway I can speed up this query. It must be the way I am coding it but it is really slow(obviously) while the java application is a little faster. The way I have to do is really tricky. Each query builds upon it self. So it goes from Days, to hour summary to per hour summary. Here is an example of how it works
What I'm asking if there are any suggestions, tricks to make this faster. Thanks guys.
Code: Select all
function DisplayReport($currentLine, $currentProcess, $dateStart, $dateEnd) {
$dsn = 'mysql://'.MYSQL_USERNAME.''.MYSQL_PASSWORD.'@localhost/***';
$mdb2 =& MDB2::factory($dsn);
if (PEAR::isError($mdb2)) {
echo ($mdb2->getMessage().' - '.$mdb2->getUserinfo());
}
logToFile("info.log", "Start Day Summary");
$daySummary = DaySummaryPallet($currentLine, 0, $dateStart, $dateEnd);
$numDays = count($daySummary);
for ($counterDay = 0; $counterDay < $numDays; $counterDay ++) {
echo "<table border = '1'>";
echo "<tr><td>";
$dateHour = $daySummary[$counterDay]['year']."-".$daySummary[$counterDay]['month']."-".$daySummary[$counterDay]['day']; //start date
echo "Date: ".$dateHour."<br/>";
echo "---------------";
echo "<table><tr><td>";
logToFile("info.log", "Start Hour Summary");
$hourSummary = HourSummaryPallet($currentLine, 0, $dateHour, $dateHour);
$numHours = count($hourSummary);
for ($counterHour = 0; $counterHour < $numHours; $counterHour++) {
echo "Hour: ".$hourSummary[$counterHour]['hour']."<br/>";
//echo "Average ounces ".$hourSummary[$counterHour]['avg_ounces'];
$datePerHour = $dateHour." ".$hourSummary[$counterHour]['hour'];
logToFile("info.log", "Start Per Hour Summary");
echo "<table border = '1'>";
echo "<tr><td>Time</td><td>Ounces</td><td>Revolutions</td><td>Pallet Count</td>
<td>Roll Change</td><td>Breaks</td></tr>";
echo "</td></tr></table>";
$perHourSummary = PerHourSummaryPallet($currentLine, 0, $datePerHour, $datePerHour);
$numPerHours = count($perHourSummary);
logToFile("info.log", "Number of hours:" .$numPerHours);
for ($counterPerHour = 0; $counterPerHour < $numPerHours; $counterPerHour ++) {
echo $perHourSummary[$counterPerHour]['ounces'];
echo "<br/>";
}
}
echo "</table>";
echo "</td></tr></table>";
}
$mdb2->disconnect();
}
function DaySummaryPallet( $currentLine, $currentProcess, $dateStart, $dateEnd) {
$dsn = 'mysql://'.MYSQL_USERNAME.''.MYSQL_PASSWORD.'@localhost/***';
$mdb2 =& MDB2::factory($dsn);
if (PEAR::isError($mdb2)) {
echo ($mdb2->getMessage().' - '.$mdb2->getUserinfo());
}
$query = ("SELECT year(time_stamp) year, month(time_stamp) month, dayofmonth(time_stamp) day,
truncate(avg(ounces),2) avg_ounces, truncate(sum(ounces)/16.0,2) lbs, count(pkg_load_id) total_pallets
FROM pkg_load WHERE pkg_line_id = '$currentLine[$currentProcess]' AND time_stamp
BETWEEN '".$dateStart." 00:00:00' AND '".$dateEnd." 23:59:59'
GROUP BY year(time_stamp), dayofyear(time_stamp)
ORDER BY time_stamp DESC;");
$result = $mdb2->query($query);
logToFile("info.log", $query);
if (PEAR::isError($result)) {
echo ($result->getMessage().' - '.$result->getUserinfo());
exit();
}
$resultArray = $result->fetchAll(MDB2_FETCHMODE_ASSOC);
$result->free();
$mdb2->disconnect();
return $resultArray;
}