Alternatives to nested loops to improve efficiency?

[futurshox]

Hello, this is my first post here.

 

I am trying to make an alarm system. I have got it mostly built and it works, but my ISP is complaining that it chews up a lot of CPU resources. And this is just in testing, with only a small set of data (nine records). They are telling me that nested loops are the culprit. They're probably right, but I just don't know what else to use. OOP is a mystery to me.

 

I will describe below what the script is doing. It is meant to run via cron every 10 minutes. I am trying not to process data for which there is no need, so the script starts processing by addressing only the stations for which settings have been made. Then it will filter them by company, so messages for companies with mutiple locations get sent as one, not many.

Please can anyone tell me if there is a better way of doing this kind of thing? (This is PHP, there must be!) What should I be learning to write this stuff more efficiently?

 

Thank you,

 

Jo

 

Apache version 1.3.41 (Unix)

PHP version 5.2.3

MySQL version 5.0.45-community

 

(first, other scripts put data into currentdata table)

 

 

// SQL: copy incoming current data from currentdata into daydata

// SQL: delete data older than 24 hours from daydata

// SQL: fetch locations and settings from alarmsettings table and order by company

 

foreach (company) {

foreach (location) {

// SQL: fetch latest data from daydata

// compare current data against alarm settings

 

if (alarm_triggered) {

// if enough time has elapsed since the last alarm trigger, set 'send_response' flag

// SQL: update timestamps/alertstate in daydata according to when the last trigger occurred

 

} else { // if no alarm

// if enough time has passed for the all-clear AND we are in an alert state, set all-clear.

// SQL: update daydata with all-clear timestamp

}

} // end foreach(location)

 

if (company_alarm>0) {

send_response()

}

if (company_allclear>0) {

send_allclear()

}

} // end foreach(company)

 

 

function send_response() {

$to = fetch_addresses();

// SQL: insert log messages sent in alarmlog table

// send_email()

// (later: send SMS)

}

 

function send_allclear() {

$to = fetch_addresses();

// SQL: insert log messages sent in alarmlog table

// send_email()

// (later: send SMS)

}

 

function fetch_addresses() {

// SQL: select email from alarmusers where company='company'

// return addresses

}

 

function send_email() {

mail($to,$subject,$message,$headers);

}

 

[DarkWater]

That script doesn't parse.  Are you sure it's your actual script?

[futurshox]

I am sorry for the misunderstanding, this is a simplification of the script in an attempt to show only the logic of what it's doing.

[DarkWater]

That's over-simplified.  Show us the actual script so we can see any potential speed traps.  Also, please post any code in

tags.

[PFMaBiSmAd]

One of the points of using a database is you form queries that operate on just the relevant data. I'm going to guess you are basically selecting all the data and looping through it using some slow parsed/tokenized/interpreted php code.

 

As has already been mentioned, the only way to get any specific help with your problem is if you post your actual code. Posting your table definitions and some examples of the data would help as well.

[futurshox]

OK, thank you for your advice.

 

The code is this:

 

<?php


$dbname="name";
$connectdb = mysql_connect('localhost', 'user', 'pass') or die("Could not connect to database: " . mysql_error());
$db = mysql_select_db($dbname, $connectdb) or die ('Can\'t use database : ' . mysql_error());


// copy from currentdata into daydata, but we need to generate the indexkey for this table
$insert="INSERT INTO daydata (stationid, datestamp, wspeed, wdir, wsector, tout, hout, indexkey) VALUES ";
$values='';

$sql="SELECT * FROM currentdata";
$result=mysql_query($sql);
while ($myrow=mysql_fetch_array($result,MYSQL_ASSOC)) {
$STATION=$myrow['stationid'];
$datestamp=$myrow['datestamp'];
$myrow['indexkey']=$datestamp.$STATION;
$values.="('".implode("','",$myrow)."'),";
}
if (substr($values,-1)==',') { $values=substr($values,0,-1); } // knock the last comma off
$newsql=$insert.$values." on duplicate key update indexkey=VALUES(indexkey)"; // need to 'update if duplicate' due to different update intervals from each station
$newresult=mysql_query($newsql);


// delete data older than 24 hours
$date1 = gmmktime();
$date2 = $date1-86400; // minus 1 day
$date3 = date('Y-m-d H:i:s',$date2);
$sql2 = "DELETE FROM daydata WHERE datestamp < '$date3'";
$result2 = mysql_query($sql2);


// we want to group data by company so fetch the stations and order by CID
$mainarray=array();
$sql4="select * from alarmsettings order by CID, STATION";
$result4=mysql_query($sql4);
while ($myrow=mysql_fetch_array($result4,MYSQL_ASSOC)) {
$CID=$myrow['CID'];
$STATION=$myrow['STATION'];

$mainarray[$CID][$STATION]['CID']=$CID;
$mainarray[$CID][$STATION]['stationid']=$STATION;
$mainarray[$CID][$STATION]['alertfreqmins']=$myrow['alertfreqmins'];
$mainarray[$CID][$STATION]['allclearmins']=$myrow['allclearmins'];
$mainarray[$CID][$STATION]['deadhours']=$myrow['deadhours'];
$mainarray[$CID][$STATION]['alertstate']=$myrow['alertstate'];
$mainarray[$CID][$STATION]['firsttrigger']=$myrow['firsttrigger'];
$mainarray[$CID][$STATION]['lasttrigger']=$myrow['lasttrigger'];
$mainarray[$CID][$STATION]['lastsent']=$myrow['lastsent'];
$mainarray[$CID][$STATION]['allclear']=$myrow['allclear'];
$mainarray[$CID][$STATION]['windspeed']=$myrow['windspeed'];
$mainarray[$CID][$STATION]['windsector']=$myrow['windsector'];
$mainarray[$CID][$STATION]['temphigh']=$myrow['temphigh'];
$mainarray[$CID][$STATION]['templow']=$myrow['templow'];
$mainarray[$CID][$STATION]['humhigh']=$myrow['humhigh'];
$mainarray[$CID][$STATION]['humlow']=$myrow['humlow'];
}

$utc_str = gmdate("Y-m-d H:i:s", time());
$utcH = gmdate("H", time());
$now = strtotime($utc_str);

foreach ($mainarray as $CID=>$location) { // loop through companies
$response=array();
$alert=array();
$allclear=array();

foreach ($location as $STATION=>$info) {
	// check trigger times against $now and $alertfreqmins.
	$alarm=0;
	$send_response=0;
	$dead_hours=0;

	// fetch latest data
	$sql5="select * from currentdata where stationid='$STATION' order by datestamp desc limit 1";
	$result5=mysql_query($sql5);
	if (mysql_num_rows($result5)>0) { // if no result, there's nothing to compare with
		$myrow5=mysql_fetch_array($result5,MYSQL_ASSOC);

		// ALARM TRIGGERS

		// TEMP
		if (!empty($info['templow']) and $info['templow']>=$myrow5['tout']) {
			$alert[$STATION]['TempLo']=$myrow5['tout'];
		}
		if (!empty($info['temphigh']) and $info['temphigh']<=$myrow5['tout']) {
			$alert[$STATION]['TempHi']=$myrow5['tout'];
		}

		// HUMIDITY
		if (!empty($info['humlow']) and $info['humlow']>=$myrow5['hout']) {
			$alert[$STATION]['HumLo']=$myrow5['hout'];
		}
		if (!empty($info['humhigh']) and $info['humhigh']<=$myrow5['hout']) {
			$alert[$STATION]['HumHi']=$myrow5['hout'];
		}

		// WIND
		if (!empty($info['windspeed']) and $info['windspeed']<=$myrow5['wspeed'] ) {
			$alert[$STATION]['WSpeedHi']=round($myrow5['wspeed'],1);
		}
		$shortcompass = array('N','NE','E','SE','S','SW','W','NW');
		$wsector_in = $shortcompass[round($myrow5['wdir'] / 45) % 8];
		if (!empty($info['windsector']) and stripos($info['windsector'],$wsector_in)!==false ) {
			$alert[$STATION]['WSector']=$wsector_in." (".$myrow5['wsector'].")";
		}


		// $now is UTC in seconds from above
		$first_trigger=strtotime($info['firsttrigger']);
		$last_trigger=strtotime($info['lasttrigger']);
		$last_sent=strtotime($info['lastsent']);

		// IF ANY ALERT HAS BEEN TRIGGERED
		if (count($alert[$STATION])>0) {

			$alertstate="alertstate='1', ";

			// DETERMINE WHETHER TO SEND ALARM MESSAGE OR NOT
			if ($now-$last_sent>=($info['alertfreqmins']*60)-300) { // if enough time has elapsed since the last alarm trigger (with 5-min fudge factor)
				$send_response=1;
				$new_last_sent="lastsent='$utc_str', ";
			} else {
				$new_last_sent='';
			}
			if (stripos($info['deadhours'],$utcH)!==false) { // check for dead hours on this location
				$dead_hours=1;
			}


			// SET TIMESTAMPS
			if ($first_trigger<$now-(($info['alertfreqmins']*60) + ($info['allclearmins']*60)) ) { // resetting firsttrigger for new alarm event
				$sql6="update alarmsettings set $alertstate $new_last_sent firsttrigger='$utc_str', lasttrigger='$utc_str' where STATION='$STATION' ";
				$result6=mysql_query($sql6);
			} else {
				$sql6="update alarmsettings set $alertstate $new_last_sent lasttrigger='$utc_str' where STATION='$STATION' ";
				$result6=mysql_query($sql6);
			}

			// BUILD MESSAGE IF SENDABLE
			if ($send_response==1 and $dead_hours==0) {
				$string="$STATION: ";
				foreach ($alert[$STATION] as $key=>$value) {
					$string.="\n$key=$value ";
				}
				$response[$STATION]=$string."\n";
			}

		} else { // NO ALERT TRIGGERED
			// if enough time has passed for the all-clear AND we are in an alert state, send an all-clear.
			if ($now>($last_trigger + ($info['allclearmins']*60)) and $info['alertstate']==1) {
				$allclear[$STATION]="$STATION all-clear\n";

				$sql6="update alarmsettings set alertstate='0', allclear='$utc_str' where STATION='$STATION' ";
				$result6=mysql_query($sql6);
			}
		} // end if count($alert[$STATION])>0

	} // end if mysql_num_rows($result5)>0

} // end foreach($location)

if (count($response)>0) {
	send_response($CID,$response,$utc_str);
}
if (count($allclear)>0) {
	send_allclear($CID,$allclear,$utc_str);
}

} // end foreach($CID)


function send_response($CID,$response,$utc_str) {
$to = fetch_addresses($CID);

// log messages sent in new table and build email message
$values='';
$email='';
$where='';
foreach ($response as $STATION=>$msg) {
	$values.="('$utc_str','$STATION','$CID','$msg','alarm'),";
	$email.=$msg;
	$where.=$STATION." ";
}

if (substr($values,-1)==',') { $values=substr($values,0,-1); }
$sqlstart="insert into alarmlogger (alertdate,STATION,CID,alertmsg,alerttype) values ";
$sql8=$sqlstart.$values;
$result8=mysql_query($sql8);

send_email("WX Alarm $where",$email,$to);
}



function send_allclear($CID,$allclear,$utc_str) {
$to = fetch_addresses($CID);

// log messages sent in new table and build email message
$values='';
$email='';
$where='';
foreach ($allclear as $STATION=>$msg) {
	$values.="('$utc_str','$STATION','$CID','$msg','allclear'),";
	$email.=$msg;
	$where.=$STATION." ";
}

if (substr($values,-1)==',') { $values=substr($values,0,-1); }
$sqlstart="insert into alarmlogger (alertdate,STATION,CID,alertmsg,alerttype) values ";
$sql8=$sqlstart.$values;
$result8=mysql_query($sql8);

send_email("WX All-Clear $where",$email,$to);
}


function fetch_addresses($CID) {
$sql7="select * from alarmusers where CID='$CID' ";
$result7=mysql_query($sql7);
$email_to=array();
while ($myrow7=mysql_fetch_array($result7,MYSQL_ASSOC)) {
	$email_to[]=$myrow7['email'];
}
$to=implode(",",$email_to);
return($to);
}


function send_email($subject,$message,$to) {
$from="me@here.net";
$fromname="Alerter";
$fromaddress="me@here.net";
$reply="me@here.net";
$eol="\r\n";
# Common Headers
$headers = "From: ".$fromname."<".$fromaddress.">".$eol;
$headers .= "Reply-To: ".$fromname."<".$fromaddress.">".$eol;
$headers .= "Return-Path: ".$fromname."<".$fromaddress.">".$eol;    // these two to set reply address
$headers .= "Message-ID: <".time()."-".$fromaddress.">".$eol;
$headers .= "X-Mailer: PHP v".phpversion().$eol;          // These two to help avoid spam-filters
mail($to,$subject,$message,$headers);
}

?>

 

When writing a script like this, how many SQL queries are too many? I have been trying to reduce the number of them but is that really the problem?

 

Example of the alarmsettings table:

STATION CID alertfreqmins allclearmins deadhours alertstate firsttrigger lasttrigger lastsent allclear windspeed windsector temphigh templow humhigh humlow

XYZ 8 60 20 NULL 1 2008-10-23 13:20:02 2008-10-23 14:20:03 2008-10-23 13:40:02 2008-10-23 07:50:03 5 S 15 0 90 0

 

Thank you,

Jo