Making a script run faster

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

AlbinoJellyfish
Forum Commoner
Posts: 76
Joined: Sun Apr 04, 2004 7:39 pm

Making a script run faster

Post 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.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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).
AlbinoJellyfish
Forum Commoner
Posts: 76
Joined: Sun Apr 04, 2004 7:39 pm

Post 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?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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?
AlbinoJellyfish
Forum Commoner
Posts: 76
Joined: Sun Apr 04, 2004 7:39 pm

Post by AlbinoJellyfish »

ADDTIME adds two 00:00:00 formatted times together. Does sum work with timestamps without the date?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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))
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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;
AlbinoJellyfish
Forum Commoner
Posts: 76
Joined: Sun Apr 04, 2004 7:39 pm

Post 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!
Last edited by AlbinoJellyfish on Tue Jul 19, 2005 10:56 am, edited 1 time in total.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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?
AlbinoJellyfish
Forum Commoner
Posts: 76
Joined: Sun Apr 04, 2004 7:39 pm

Post 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.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
AlbinoJellyfish
Forum Commoner
Posts: 76
Joined: Sun Apr 04, 2004 7:39 pm

Post 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
?>
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
AlbinoJellyfish
Forum Commoner
Posts: 76
Joined: Sun Apr 04, 2004 7:39 pm

Post by AlbinoJellyfish »

Ok, dumb question, what do you mean by indices? An index coulumn in each table?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply