Making a script run faster
Posted: Mon Jul 18, 2005 4:35 pm
I have a php script that connects to a mysql database and does a bunch of stuff, and it takes 45 seconds to complete, and I was wondering how I should go about optimizing it. Right now it is set up so that it goes:
I was also thinking, would it be possible to go
Heres my code:
Or maybe I am just insane and have no idea how to code php.
Code: Select all
While $current hour <= $end hour{
Select trunk group {
Select * from other table where trunk group = from sql statement before AND where the startTime is inbetween the current hour and the next hour {
display
}
}
$current hour ++
}Code: Select all
Trunk group, Select * from other table where trunkgroup, THEN the while $current hour, and sort the results of the big sql statementCode: Select all
<?php
// Start TIMER
// -----------
$stimer = explode( ' ', microtime() );
$stimer = $stimer[1] + $stimer[0];
error_reporting(E_ALL);
//include("config.inc");
$server = mysql_connect("localhost","mmooers","zumiez");
mysql_select_db("matt",$server);
//get settings from index.html
$mode = $_POST['mode'];
$incomingc = 0;
//set variables for totals of specific trunks
$usageintotal = '00:00:00';
$usageouttotal = '00:00:00';
$incomingtc = 0;
$outgoingtc = 0;
$incomingll = 0;
if ($mode == "reg") {
$sdate1 = $_POST['syear']. "-" .$_POST['smonth']. "-" .$_POST['sday'];
$edate1 = $_POST['eyear']. "-" .$_POST['emonth']. "-" .$_POST['eday'];
$stime = $_POST['shour']. ":" .$_POST['smin']. ":" .$_POST['ssec'];
$etime = $_POST['ehour']. ":" .$_POST['emin']. ":" .$_POST['esec'];
$sdate = $sdate1. " " .$stime;
$edate = $edate1. " " .$etime;
//write default table
echo '<center><table width="200" cellspacing="0" cellpadding="5" border="1">
<tr>
<td><center>Trunk Group</center></td>
<td><center>Incoming Calls</center></td>
<td><center>Incoming Usage</center></td>
<td><center>Outgoing Calls</center></td>
<td><center>Outgoing Usage</center></td>
<td><center>Total Calls</center></td>
<td><center>Total Usage</center></td>
</tr>';
//go through each trunk group
$trunkq = mysql_query("SELECT trunkGroup FROM editrunkgroup");
while ($trunk = mysql_fetch_array($trunkq)) {
//reset values each time
$usageintotal = '00:00:00';
$usageouttotal = '00:00:00';
$incomingtc = 0;
$outgoingtc = 0;
$totalusage = '00:00:00';
$trunkGroup = $trunk['trunkGroup'];
//get db info about trunks
$ss7 = mysql_query("select
TIMEDIFF(endTime,startTime) AS 'usage',
ediss7.opc,
ediss7.dpc,
ediss7.cic,
editrunkgroup.trunkGroup,
editrunkgroup.pointCode2,
editrunkgroup.pointCode1,
ediss7.startTime,
ediss7.endTime from ediss7 left join editrunkgroup
on ((ediss7.cic >= editrunkgroup.ciclow and
ediss7.cic <=editrunkgroup.cichigh)and
((ediss7.opc = editrunkgroup.pointcode1 or ediss7.opc
= editrunkgroup.pointcode2)
and (ediss7.dpc = editrunkgroup.pointcode1 or
ediss7.dpc = editrunkgroup.pointcode2)))
Where
(unix_timestamp(ediss7.startTime)>=unix_timestamp('$sdate') AND unix_timestamp(ediss7.endTime)<=unix_timestamp('$edate')) AND editrunkgroup.trunkGroup='$trunkGroup'");
while ($ss7x = mysql_fetch_array($ss7)){
//update variables
$incomingll = $incomingll + 1;
if($ss7x['opc'] == $ss7x['pointCode2']){
$usage = $ss7x['usage'];
$incomingtc = $incomingtc + 1;
$usagetotalqin = mysql_query("SELECT ADDTIME('$usageintotal','$usage') as 'usageintotal'");
while ($uint = mysql_fetch_array($usagetotalqin)){
$usageintotal = $uint['usageintotal'];
}
} elseif ($ss7x['opc'] == $ss7x['pointCode1']) {
$outgoingtc = $outgoingtc + 1;
$usage = $ss7x['usage'];
$usagetotalqout = mysql_query("SELECT ADDTIME('$usageouttotal','$usage') as 'usageouttotal'");
while ($uoutt = mysql_fetch_array($usagetotalqout)){
$usageouttotal = $uoutt['usageouttotal'];
}
}
//calculate final totals
$totalusageq = mysql_query("SELECT ADDTIME('$usageouttotal','$usageintotal') as 'totalusage'");
while($totalusageR = mysql_fetch_array($totalusageq)){
$totalusage = $totalusageR['totalusage'];
}
}
//print that trunk group info
$totalcalls = $outgoingtc + $incomingtc;
echo"<tr>
<td><div align=\"center\">$trunkGroup</div></td>
<td><div align=\"center\">$incomingtc</div></td>
<td><div align=\"center\">$usageintotal</div></td>
<td><div align=\"center\">$outgoingtc</div></td>
<td><div align=\"center\">$usageouttotal</div></td>
<td><div align=\"center\">$totalcalls</div></td>
<td><div align=\"center\">$totalusage</div></td>
</tr>";
}
} elseif ($mode == "hourly") {
$sdate1 = $_POST['syear']. "-" .$_POST['smonth']. "-" .$_POST['sday'];
$edate1 = $_POST['eyear']. "-" .$_POST['emonth']. "-" .$_POST['eday'];
$stime = $_POST['shour']. ":00:00";
$etime = $_POST['ehour']. ":00:00";
$sdate = $sdate1. " " .$stime;
$edate = $edate1. " " .$etime;
$ehour = $_POST['ehour'];
$shour = $_POST['shour'];
//write default table
$diff = $ehour;
$x = $shour - 1;
$y = $x + 1;
$i = $shour;
while ($x <= $diff) {
echo '<br><center>Hour: '.$x.'<br><table width="200" cellspacing="0" cellpadding="5" border="1">
<tr>
<td><center>Trunk Group</center></td>
<td><center>Incoming Calls</center></td>
<td><center>Incoming Usage</center></td>
<td><center>Outgoing Calls</center></td>
<td><center>Outgoing Usage</center></td>
<td><center>Total Calls</center></td>
<td><center>Total Usage</center></td>
</tr>';
//go through each trunk group
$trunkq = mysql_query("SELECT trunkGroup FROM editrunkgroup");
while ($trunk = mysql_fetch_array($trunkq)) {
//reset values each time
$usageintotal = '00:00:00';
$usageouttotal = '00:00:00';
$incomingtc = 0;
$outgoingtc = 0;
$totalusage = '00:00:00';
$trunkGroup = $trunk['trunkGroup'];
//get db info about trunks
$ss7 = mysql_query("select
TIMEDIFF(endTime,startTime) AS 'usage',
ediss7.opc,
ediss7.dpc,
ediss7.cic,
editrunkgroup.trunkGroup,
editrunkgroup.pointCode2,
editrunkgroup.pointCode1,
ediss7.startTime,
ediss7.endTime from ediss7 left join editrunkgroup
on ((ediss7.cic >= editrunkgroup.ciclow and
ediss7.cic <=editrunkgroup.cichigh)and
((ediss7.opc = editrunkgroup.pointcode1 or ediss7.opc
= editrunkgroup.pointcode2)
and (ediss7.dpc = editrunkgroup.pointcode1 or
ediss7.dpc = editrunkgroup.pointcode2) AND (unix_timestamp(ediss7.startTime)>=unix_timestamp('".$sdate1. " ". $x .":00:00') AND unix_timestamp(ediss7.endTime)<=unix_timestamp('".$sdate1. " " . $y .":00:00'))))
Where
editrunkgroup.trunkGroup='$trunkGroup'");
if(!$ss7test = mysql_fetch_assoc($ss7)) {
} else {
while ($ss7x = mysql_fetch_array($ss7)){
//update variables
$incomingll = $incomingll + 1;
if($ss7x['opc'] == $ss7x['pointCode2']){
$usage = $ss7x['usage'];
$incomingtc = $incomingtc + 1;
$usagetotalqin = mysql_query("SELECT ADDTIME('$usageintotal','$usage') as 'usageintotal'");
while ($uint = mysql_fetch_array($usagetotalqin)){
$usageintotal = $uint['usageintotal'];
}
} elseif ($ss7x['opc'] == $ss7x['pointCode1']) {
$outgoingtc = $outgoingtc + 1;
$usage = $ss7x['usage'];
$usagetotalqout = mysql_query("SELECT ADDTIME('$usageouttotal','$usage') as 'usageouttotal'");
while ($uoutt = mysql_fetch_array($usagetotalqout)){
$usageouttotal = $uoutt['usageouttotal'];
}
}
//calculate final totals
$totalusageq = mysql_query("SELECT ADDTIME('$usageouttotal','$usageintotal') as 'totalusage'");
while($totalusageR = mysql_fetch_array($totalusageq)){
$totalusage = $totalusageR['totalusage'];
}
}
//print that trunk group info
$totalcalls = $outgoingtc + $incomingtc;
echo"<tr>
<td><div align=\"center\">$trunkGroup</div></td>
<td><div align=\"center\">$incomingtc</div></td>
<td><div align=\"center\">$usageintotal</div></td>
<td><div align=\"center\">$outgoingtc</div></td>
<td><div align=\"center\">$usageouttotal</div></td>
<td><div align=\"center\">$totalcalls</div></td>
<td><div align=\"center\">$totalusage</div></td>
</tr>";
}
}
echo "</table>";
$x++;
$y++;
$i++; }
} else {
}
?>
</table>
<br><br>
<?php
//end timer
$etimer = explode( ' ', microtime() );
$etimer = $etimer[1] + $etimer[0];
echo '<p style="margin:auto; text-align:center">';
printf( "Page took <b>%f</b> seconds to generate.", ($etimer-$stimer) );
echo '</p>';
?>
</center>