Extremely Slow, Any suggestions?

[Andy-H]

Where I work we have a range of fleet management tracking units that fire data into our server once every 30 seconds which is then processed by a visual basic socket server and inserted into a mysql database.

 

 

The database was designed a while ago and I don't think changing it is an option at the moment, but I am trying to query for a time period between two specific sets of criteria, and the script is taking forever to load.

 

 

<?php
include 'db.php';


$row              = array('partof' => 1, 'timedif' => -1, 'bst' => 0, 'units' => 'M');
$_GET['device']   = array('07975196717', '07584157517');
$_GET['location'] = 32;
$_GET['start']    = '22/10/2011';
$_GET['end']      = '30/10/2011';


include 'common.php';


if ( $row )
{
$data = array();
/* GET LOCATION */
$query    = "SELECT cl.location_name, cl.location_lat, cl.location_lon, cl.location_radius
		  FROM custom_locations cl
		  WHERE cl.id = ". (int)$_GET['location'] ." AND owner = ". (int)$row['partof'] ." LIMIT 1";
$result   = $result = mysql_query($query, $db_link)or trigger_error('Couldn\'t load all devices ' . mysql_error($db_link));
$location = mysql_fetch_row($result);

/* SELECT DEVICES */
while ( true )
{
	$query      = "SELECT nl.thetimestamp, nl.device
				FROM newlocations nl
				WHERE nl.thetimestamp > $date_start AND nl.thetimestamp < $date_end 
				AND ( nl.device IN ( '". implode("', '", $_GET['device']) ."' ) )
				AND ( nl.msgid = 11 AND nl.inputi = 0 )
				AND GEOFENCE(nl.lat, nl.lon, ". $location[1] .", ". $location[2] .", ". $location[3] .")
				ORDER BY nl.thetimestamp ASC LIMIT 1";
	$result     = mysql_query($query, $db_link)or trigger_error('Couldn\'t load all devices ' . mysql_error($db_link));
	$num        = mysql_num_rows($result);
	if ( !$num )
		break;
	$nl         = mysql_fetch_assoc($result);
	$query      = "SELECT nl.thetimestamp
		         FROM newlocations nl
			     WHERE ( nl.device IN ( '". implode("', '", $_GET['device']) ."' ) )
				 AND nl.thetimestamp > ". $nl['thetimestamp'] ."
			     AND !GEOFENCE(nl.lat, nl.lon, ". $location[1] .", ". $location[2] .", ". $location[3] .")
			     ORDER BY nl.thetimestamp ASC LIMIT 1";
	$result     = mysql_query($query, $db_link)or trigger_error('Couldn\'t load all devices ' . mysql_error($db_link));
	$nl2        = mysql_fetch_assoc($result);

	$nl['thetimestamp']  = formattime($nl['thetimestamp'], $row['timedif'], $row['bst']);
	$nl2['thetimestamp'] = formattime($nl2['thetimestamp'], $row['timedif'], $row['bst']);

	$diff       = $nl2['thetimestamp'] - $nl['thetimestamp'];
	$data[$nl['device']][] = array('start' => $nl['thetimestamp'], 'end' => $nl2['thetimestamp'], 'diff' => $diff);
	$date_start = $nl2['thetimestamp'];
}
echo '<pre>' . print_r($data, 1) . '</pre>';

//include 'common_end.php';
}
?>

FYI: GEOFENCE is a MySQL function that returns 1 if a longitude/latitude is within a certain radius of a custom location, 0 otherwise.

 

 

This script is selecting all instances between startdate and enddate that inputi (ignition) turns off within a geofence, then querying for the next inbound location outside of that geofence to calculate the time spent at a given location between two dates, but, like I say it's running extremely slowly.

 

 

Any suggestions to optimize it?

 

 

Thanks, Andy.

[Andy-H]

if ( $row )
{
$data = array();
/* GET LOCATION */
$query    = "SELECT cl.location_name, cl.location_lat, cl.location_lon, cl.location_radius
		  FROM custom_locations cl
		  WHERE cl.id = ". (int)$_GET['location'] ." AND owner = ". (int)$row['partof'] ." LIMIT 1";
$result   = $result = mysql_query($query, $db_link)or trigger_error('Couldn\'t load all devices ' . mysql_error($db_link));
$location = mysql_fetch_row($result);

/* SELECT DEVICES */
$query      = "SELECT nl.thetimestamp, nl.device
			FROM newlocations nl
			WHERE nl.thetimestamp > $date_start AND nl.thetimestamp < $date_end 
			AND ( nl.device IN ( '". implode("', '", $_GET['device']) ."' ) )
			AND ( nl.msgid = 11 AND nl.inputi = 0 )
			AND GEOFENCE(nl.lat, nl.lon, ". $location[1] .", ". $location[2] .", ". $location[3] .")
			ORDER BY nl.thetimestamp ASC";
$result     = mysql_query($query, $db_link)or trigger_error('Couldn\'t load all devices ' . mysql_error($db_link));


while ( $nl = mysql_fetch_assoc($result) )
{
	if ( $nl['thetimestamp'] < $date_start )
		continue;
	$query      = "SELECT nl.thetimestamp
				 FROM newlocations nl
				 WHERE ( nl.device IN ( '". implode("', '", $_GET['device']) ."' ) )
				 AND nl.thetimestamp > ". $nl['thetimestamp'] ."
				 AND !GEOFENCE(nl.lat, nl.lon, ". $location[1] .", ". $location[2] .", ". $location[3] .")
				 ORDER BY nl.thetimestamp ASC LIMIT 1";
	$res        = mysql_query($query, $db_link)or trigger_error('Couldn\'t load all devices ' . mysql_error($db_link));
	$nl2        = mysql_fetch_assoc($res);

	$nl['thetimestamp']  = formattime($nl['thetimestamp'], $row['timedif'], $row['bst']);
	$nl2['thetimestamp'] = formattime($nl2['thetimestamp'], $row['timedif'], $row['bst']);

	$diff       = $nl2['thetimestamp'] - $nl['thetimestamp'];
	$data[$nl['device']][] = array('start' => $nl['thetimestamp'], 'end' => $nl2['thetimestamp'], 'diff' => $diff);
	$date_start = $nl2['thetimestamp'];
}

echo '<pre>' . print_r($data, 1) . '</pre>';

//include 'common_end.php';
}

 

 

Made a significant improvement.

[Adam]

Is a "significant improvement" enough of an improvement?

 

I can't really tell what you've changed..? But since you're executing essentially the same query (just different column values) in a loop, you can gain some easy performance by using a prepared statement instead. The DB will cache the execution plan on the server, making any subsequent requests much quicker. Depends how many subsequent requests you actually make as to how much of an improvement you'll see, but looking at your SQL it can't do any harm!

[Andy-H]

It's still pretty sluggish, but I've used a COM object to create an excel spreadsheet and email/download a spreadsheet/pdf depending on get variables, so it was never going to be "fast" I suppose.

Will it gain improvement with a PDO (MySQL emulated) prepared statement or would I be better off using MySQLi?

[Adam]

There won't really be a difference between them, they both use MySQL. Just use whichever you prefer. As for the Excel/PDF creation, you could negate the effect by passing it to a background process to handle. There would be a delay in receiving it of course, but would mean you don't have to sit around waiting for it. I would try to improve the speed/efficiency before deciding whether that kind of solution is necessary though.

[Andy-H]

OK thanks, and I don't have to sit around waiting for it, it just opens excel on the server as an invisible application, then depending on get variables saves it as .pdf, .xls, .xlst, .ods and either emails it as an attachment to a specified email address or sends it to the client as a download file, I know there is a PHP Excel class that allows you to create spreadsheets but it had to be COM because I need to use features that PHP Excel doesn't support such as graphs and pivot tables.