Page 1 of 2

Making a script run faster

Posted: Mon Jul 18, 2005 4:35 pm
by AlbinoJellyfish
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:

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 ++
}
I was also thinking, would it be possible to go

Code: Select all

Trunk group, Select * from other table where trunkgroup, THEN the while $current hour, and sort the results of the big sql statement
Heres my code:

Code: 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>
Or maybe I am just insane and have no idea how to code php.

Posted: Mon Jul 18, 2005 4:47 pm
by nielsene
Try putting some of your timing statements before/after some of the large queries to see if they are taking a long time.

My guess, you have some a LEFT OUTER JOIN with a rather involved join condition, that's probably a tough join for the DB to optimize. Make sure you have indices. Check the data, do you really need an outer join?

You could probably also combine the "get all trunk" query with the first nested query, for some savings.

It appears you're using mysql to update a simple counter, (all the SELECT ADDTIME calls) this looks on the surface to be extremely inefficient, but I don't know the details of the ADDTIME function).

Posted: Tue Jul 19, 2005 10:00 am
by AlbinoJellyfish
I'll try those, one of the main reasons it probably runs slow is that it is sorting through at least 15,000 records each time, and once I added the sort by hour it got really slow. It started out as taking 8 seconds, now it takes 45. I think that taking that 8 seconds, and doing it over and over a few times probably makes it add up. I was just wondering if you could have it sort by hour after the big query, but I dont know if that is possible. Put it into an array and sort through the values there?

Posted: Tue Jul 19, 2005 10:22 am
by nielsene
15,000 records isn't really a large number of records for a DB system.

What exactly does the ADDTIME function do; could you replace all those calls with a simple SUM() in the main query?

Posted: Tue Jul 19, 2005 10:23 am
by AlbinoJellyfish
ADDTIME adds two 00:00:00 formatted times together. Does sum work with timestamps without the date?

Posted: Tue Jul 19, 2005 10:35 am
by nielsene
Hmm ugh, that sounds horribly inefficient to make a DB call just for that.

So howabout

Code: Select all

SUM(TIME_TO_SEC(column_name))

Posted: Tue Jul 19, 2005 10:50 am
by nielsene
OK, so here's a combined suggestion: I don't know your exact schema and I'm a PostGreSQL guy, but I think MySQL has all these features:

First version, use once for incoming and once for outgoing:

Code: Select all

SELECT trunkGroup,
       SUM(TIME_TO_SEC(TIMEDIFF(endTime,startTime)) AS 'usage'
  FROM ediss7 LEFT JOIN
       editrunkgroup ON ((ediss7.cic >= editrunkgroup.ciclow AND
                          ediss7.cic <=editrunkgroup.cichigh) AND
                         ((ediss7.opc=editrunkgroup.pointcode1) AND -- CHANGE HERE FOR INCOMING VERSUS OUTGOING?
                          (ediss7.dpc = editrunkgroup.pointcode1) AND -- AND HERE? 
                          (unix_timestamp(ediss7.startTime >=unix_timestamp('&quote;.$sdate1. &quote; &quote;. $x .&quote;:00:00') AND 
                           unix_timestamp(ediss7.endTime)<=unix_timestamp('&quote;.$sdate1. &quote; &quote; . $y .&quote;:00:00')))
GROUP BY trunkGroup;

Posted: Tue Jul 19, 2005 10:54 am
by AlbinoJellyfish
Does PHP do that? I think it would be faster because this script goes through every record, decides if the data is incoming or outgoing, and adds the number of seconds it took to a variable that will echo the total time overall of the data, and the total incoming time, and the total outgoing time, which adds up to be around 10 hours. I'm just confused on how to do this, but thanks for your help!

Posted: Tue Jul 19, 2005 10:56 am
by nielsene
I guess, back the original question: have you added other timing statements to your script to see where all the time is spent?

Posted: Tue Jul 19, 2005 11:15 am
by AlbinoJellyfish
Each hour's query takes about 8 seconds if empty, and 11 if its full. I'm testing with about 7 hours each time. 2 hours have data in them. I think the main query is what takes so long, and it usually takes it about 8 seconds to complete, and I am doing it over and over again about 7 times, any way I could consolidate checking what hour the call was made in the main query?
Such as having the big query select everything I want, then having php sort it out by hour then printing the results by hour?

http://www.chaski.org/matt/Reports/1/

Choose the start date as May 19th 2004 and the end date as anything after that.
The time should be from 14:00:00 to 19:00:00 and the mode should be hourly.

Posted: Tue Jul 19, 2005 11:27 am
by nielsene
Modified version of my earlier one, still once for incoming, once for outgoing, but all hours

Code: Select all

SELECT trunkGroup, HOUR(startTime) as 'hour_block',
       SUM(TIME_TO_SEC(TIMEDIFF(endTime,startTime)) AS 'usage'
  FROM ediss7 LEFT JOIN
       editrunkgroup ON ((ediss7.cic >= editrunkgroup.ciclow AND
                          ediss7.cic <=editrunkgroup.cichigh) AND
                         ((ediss7.opc=editrunkgroup.pointcode1) AND -- CHANGE HERE FOR INCOMING VERSUS OUTGOING?
                          (ediss7.dpc = editrunkgroup.pointcode1) AND -- AND HERE? 
                          (DAY(ediss7.startTime)={$_POST&#1111;'sday']}))
GROUP BY trunkGroup, hour_block ORDER BY trunkGroup, hour_block;
This should return a table like

Code: Select all

trunkgroup1 | 1 | totalusage hour 1
trunkgroup1 | 2 | totalusage hour 2
...
trunkgroup2 | 1 | totalusage hour 1
...
so you'll still have to sum the subtotals, but it should save a lot of database queries.

Posted: Tue Jul 19, 2005 1:52 pm
by AlbinoJellyfish
Ok, basing a query off of yours this goes a bit faster, but I need some help still.

Code: Select all

<?php
$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;
//update variables
//  Start TIMER
//  -----------
$s1timer = explode( ' ', microtime() );
$s1timer = $s1timer[1] + $s1timer[0];
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)) { //2 open
//reset values each time
$usageintotal = '00:00:00';
$usageouttotal = '00:00:00';
$incomingtc = 0;
$outgoingtc = 0;
$totalcalls = 0;
$totalusage = '00:00:00';
$trunkGroup = $trunk['trunkGroup'];
$anyrecords = "no";
//get db info about trunks
$ss7 = mysql_query($queery = "select
TIMEDIFF(endTime,startTime) AS 'usage',
HOUR(ediss7.startTime) as 'hour_block',
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'
ORDER BY hour_block");
while ($ss7x = mysql_fetch_array($ss7)){ //3 open
//update variables
if ($ss7x['hour_block'] > $x) { // 4 is it  anew hour
while ($ss7x['hour_block'] > $x) { // 5 keep adding till there is a record
$x++;
if ($ss7x['hour_block'] == $x) { //there is a record
$anyrecords = "yes";
}
if ($anyrecords = "no"){ //if there wasnt a record for that entire hour
echo "Hour: ".$x." has no records<br>";
}
} // 4 open
} //4 open
// {calculation stuff that works just fine}
$anyrecords = "no";
$totalcalls = $outgoingtc + $incomingtc;
} // 2 close big query
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>";

} // 1 open close trunk sql

echo "</table>";
//end timer
$e1timer = explode( ' ', microtime() );
$e1timer = $e1timer[1] + $e1timer[0];
echo '<p style="margin:auto; text-align:center">';
printf( "Hourly took <b>%f</b> seconds to generate.", ($e1timer-$s1timer) );
echo '</p>';?>
After the big query I would like to have it separate the hours into different tables, similar to
http://www.chaski.org/matt/Reports/1/display.htm
except instead of having the top of the table, have it report there isnt any records for that whole hour. Right now it runs in 20 seconds, but combines everything into 1 table instead of separating it.

I dont think this lil bit is correct

Code: Select all

<?php
while ($ss7x = mysql_fetch_array($ss7)){ //3 open
//update variables
if ($ss7x['hour_block'] > $x) { // 4 is it  anew hour
while ($ss7x['hour_block'] > $x) { // 5 keep adding till there is a record
$x++;
if ($ss7x['hour_block'] == $x) { //there is a record
$anyrecords = "yes";
}
if ($anyrecords = "no"){ //if there wasnt a record for that entire hour
echo "Hour: ".$x." has no records<br>";
}
} // 4 open
} //4 open
?>

Posted: Tue Jul 19, 2005 2:24 pm
by pickle
I strongly suggest adding indices. I had a similar time problem as this once, and adding indices dropped my page load from 2 minutes to 11 seconds.

Posted: Tue Jul 19, 2005 2:27 pm
by AlbinoJellyfish
Ok, dumb question, what do you mean by indices? An index coulumn in each table?

Posted: Tue Jul 19, 2005 2:33 pm
by pickle
MySQL Manual wrote: Indexes are used to find rows with specific column values fast. Without an index, MySQL has to start with the first record and then read through the whole table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. Note that if you need to access almost all 1,000 rows, it is faster to read sequentially, because that minimizes disk seeks.
Just make an index for every column you're using in your join and you should notice an improvement.

Manual: CREATE INDEX syntax