2
\$\begingroup\$

Just wanted to seek some advice on how can I speed up this PHP form. My batch file that calls this form is for every 1 second but it seems something in my code does the slowing.

Is there any way to speed up the process of this PHP file?

<?php
// Connect to the database (host, username, password)
$con = mssql_connect('MARK-WEB2', 'sa', 'str0ngp@ssw0rd')
or die('Could not connect to the server!');

// Select a database:
mssql_select_db('Mark_Fast_Testing')
or die('Could not select a database.');

// Example query: (TOP 10 equal LIMIT 0,10 in MySQL)
$SQL = "SELECT TOP 1 * FROM GPRSIN where isHex is null or isHex = '0' ORDER BY smsdt desc";
//$SQL = "SELECT * FROM GPRSIN where id = '1586'";
//echo $SQL; 
//die($SQL);
// Execute query:
$result = mssql_query($SQL)
or die('A error occured: ' . mysql_error());


// Fetch rows:
while ($Row = mssql_fetch_assoc($result)) {

//print $Row['smsmsg'] . "\n";
$datastring = $Row['smsmsg'];
//$datastring       = '351856040525248,241111;1G,150207084455,V,1437.5775N,12059.0105E,0.0,168,4.2,01001000;2G,150207084455,00,11.3,12.24,04.10,017438095.2;';
$from = $Row['smsfr'];
//$from             = '351856040520850';
$dataid = $Row['id'];
//$dataid           = '38766';
$smsdt = $Row['smsdt'];
//$smsdt            = '2015-05-01 05:35:43.000';
$smsdtfin = date("Y-m-d H:i:s", strtotime($smsdt));
$your_date = date("Y-m-d", strtotime($smsdt));
$your_time = date("H:i:s", strtotime($smsdt));
$datastringlength = strlen($datastring);
$det = substr($datastring, 16, 3);
$trig = substr($datastring, 20, 2);




//$SQL2 = "UPDATE GPRSIN SET isHex = '5' WHERE id = '$dataid'";
//die($SQL2);
//$result2 = mssql_query($SQL2) or die('A error occured: ' . mysql_error());
if ($datastringlength == '105' && $det == 'RA1')
{
if ($det == 'RA1') {
$findet = "R10-V2";
}
echo "<strong>RAW:</strong>" .$datastring;
echo "</br>";
echo "<strong>SMSFR:</strong>" .$from;
echo "</br>";
echo "<strong>ID:</strong>" .$dataid;
echo "</br>";
echo "<strong>SMSDT:</strong>" .$smsdt;
echo "</br>";
echo "<strong>TIMESTAMP:</strong>" .$smsdtfin;
echo "</br>";
echo "<strong>DATE:</strong>" .$your_date;
echo "</br>";
echo "<strong>TIME:</strong>" .$your_time;
echo "</br>";
echo "<strong>LENGTH:</strong>" .$datastringlength;
echo "</br>";
echo "<strong>DETERMINATOR:</strong>" .$det;
echo "</br>";
echo "<strong>TRIGGER:</strong>" .$trig;
echo "</br>";

$time = substr($datastring, 65, -34);  // time
$date = substr($datastring, 71, -28);  // date
$datetime = $date . $time;
$hour = floor((substr($datetime, 6, 2)));
$hour = ($hour<10)?"0".$hour:$hour;
$minutes = substr($datetime, 8, 2);
$second = substr($datetime, 10, 2);
$year = substr($datetime, 4, 2);
$month = substr($datetime, 2, 2);
$day = substr($datetime, 0, 2);
$findate = "20" .$year. "-" .$month. "-" .$day. " " .$hour. ":" .$minutes. ":" .$second;
$gpsdate = date("Y-m-d 00:00:00.000", strtotime($findate));
$gpstime = date("H:i:s", strtotime($findate));
$philtime = date("Y-m-d H:i:s", strtotime($findate . " +8 hours"));
//COMPUTATION FOR LAT
$lat = substr($datastring, 25, 9);  // lat
$latdeg = substr($lat, 0, 2);
$latmin = round((substr($lat, 2, 7)/60), 6);
$latfin = $latdeg + $latmin;
$latfinal = substr($latfin, 0, 6);
//COMPUTATION FOR LONG
$long = substr($datastring, 35, 10);  // long
$longdeg = substr($long, 0, 3);
$longmin = round((substr($long, 3, 7)/60), 6);
$longfin = $longdeg + $longmin;
$longfinal = substr($longfin, 0, 8);
//TRIANGULATION
$laccid = "SELECT TOP 1 LAC, CID, LONG, LAT, street, municipal FROM [Mark_Fast_Testing].[dbo].TRIANGULATION WHERE LONG LIKE '%$longfinal%' AND LAT LIKE '%$latfinal%'";
$result = mssql_query($laccid);
while ($fin = mssql_fetch_array($result))
{
$lac = $fin['LAC'];
$cid = $fin['CID'];
$tlong = $fin['LONG'];
$tlat = $fin['LAT'];
$tloc = $fin['street']. " " . $fin['municipal'];
}
$loctemp = "SELECT NAME, MUNICIPALI FROM [Mark_Fast_Testing].[dbo].ROAD where LONG LIKE '%$longfinal%' AND LAT LIKE '%$latfinal%'";
//die($loctemp);
$result = mssql_query($loctemp);
while ($fin = mssql_fetch_array($result))
{
$locfin = $fin['NAME'] . " " . $fin['MUNICIPALI'];
$street = $fin['NAME'];
$municipal = $fin['MUNICIPALI'];

}
$engine = substr($datastring, 21, 1);  // ENGINE
if ($engine == '2' || $engine == '6') {
$enginefin = "ON";
}
elseif ($engine == '0' || $engine == '4') {
$enginefin = "OFF";
}
$platetemp = "SELECT PLATENO,COMPANY FROM [Mark_Fast_Testing].[dbo].account where SENDERNO = '$from'";
$result = mssql_query($platetemp);
while ($fin = mssql_fetch_array($result))
{
$pltefin = $fin['PLATENO'];
$companyfin = $fin['COMPANY'];

}
$speed = substr($datastring, 46, 5);  // SPEED
if ($speed > '0') {
$finspeed = "Running";
}
else{
$finspeed = "Stopped";
}
$status = substr($datastring, 24, 1);  // status
if ($status == 'A') {
$statusfin = "OK";
}
else{
$statusfin = "Message Not Valid";
}
$kmrun = ROUND(((hexdec(substr($datastring, 91, -8))/1000)*1.852), 0);
$fintrig1 = substr($trig, 0, 1);
$fintrig2 = substr($trig, 1, 1);
//FIRST DIGIT
if ($fintrig1 == '2') {
$fintrig3 = "Circuit Cut-Off";
$altype = "5";
//INSERT TO ALARM
$queryresultgpscur = "INSERT INTO [Mark_Fast_Testing].[dbo].ALARM (TRXDATE,TRXTIME,ACCOUNT,MSG,LAC,CID,LONG,LAT,LOCATION,TLONG,TLAT,KMRUN,ALARMTYPE,TRXDATETIME)
                                                                   VALUES ('$your_date','$your_time','$from','$fintrig3','$lac','$cid','$longfin', '$latfin','$locfin','$tlong','$tlat','$kmrun','$altype','$smsdtfin')";
$res1 = mssql_query($queryresultgpscur) or print("Error " . mssql_get_last_message());
}
else if ($fintrig1 == '4') {
$fintrig3 = "Over Speed Limit";
$altype = "3";
//INSERT TO ALARM
$queryresultgpscur = "INSERT INTO [Mark_Fast_Testing].[dbo].ALARM (TRXDATE,TRXTIME,ACCOUNT,MSG,LAC,CID,LONG,LAT,LOCATION,TLONG,TLAT,KMRUN,ALARMTYPE,TRXDATETIME)
                                                                   VALUES ('$your_date','$your_time','$from','$fintrig3','$lac','$cid','$longfin', '$latfin','$locfin','$tlong','$tlat','$kmrun','$altype','$smsdtfin')";
$res1 = mssql_query($queryresultgpscur) or print("Error " . mssql_get_last_message());
}
else if ($fintrig1 == '6') {
$fintrig3 = "Over the speed limit and Circuit Cut-off";
//INSERT TO ALARM
$queryresultgpscur = "INSERT INTO [Mark_Fast_Testing].[dbo].ALARM (TRXDATE,TRXTIME,ACCOUNT,MSG,LAC,CID,LONG,LAT,LOCATION,TLONG,TLAT,KMRUN,ALARMTYPE,TRXDATETIME)
                                                                   VALUES ('$your_date','$your_time','$from','$fintrig3','$lac','$cid','$longfin', '$latfin','$locfin','$tlong','$tlat','$kmrun','$altype','$smsdtfin')";
$res1 = mssql_query($queryresultgpscur) or print("Error " . mssql_get_last_message());
}
//SECOND DIGIT
if ($fintrig2 == '2' || $fintrig2 == '6' || $fintrig2 == '8' || $fintrig2 == 'C' || $fintrig2 == 'A' || $fintrig2 == 'E') {
$fintrig = "ENGINE ON";
$altype = "4";
//CHECK IF ENGINE ON EXISTS
$checkengineon = "SELECT COUNT(*) as [COUNT] FROM [Mark_Fast_Testing].[dbo].ALARM WHERE account = '$from' and MSG = 'ENGINE ON'";
$result = mssql_query($checkengineon);
while ($fin = mssql_fetch_array($result))
{
$rowcount = $fin['COUNT'];
}
if ($rowcount == '0') {
//INSERT TO ALARM
$queryresultgpscur = "INSERT INTO [Mark_Fast_Testing].[dbo].ALARM (TRXDATE,TRXTIME,ACCOUNT,MSG,LAC,CID,LONG,LAT,LOCATION,TLONG,TLAT,KMRUN,ALARMTYPE,TRXDATETIME)
                                                                   VALUES ('$your_date','$your_time','$from','$fintrig','$lac','$cid','$longfin', '$latfin','$locfin','$tlong','$tlat','$kmrun','$altype','$smsdtfin')";
$res1 = mssql_query($queryresultgpscur) or print("Error " . mssql_get_last_message());

}
else{
$checklatest = "SELECT TOP 1 MSG FROM [Mark_Fast_Testing].[dbo].ALARM WHERE account = '$from' and (MSG = 'ENGINE OFF' OR MSG = 'ENGINE ON') order by TRXDATETIME DESC";
$result = mssql_query($checklatest);
while ($fin = mssql_fetch_array($result))
{
$latest = $fin['MSG'];
if ($latest == "ENGINE OFF") {
$queryresultgpscur = "INSERT INTO [Mark_Fast_Testing].[dbo].ALARM (TRXDATE,TRXTIME,ACCOUNT,MSG,LAC,CID,LONG,LAT,LOCATION,TLONG,TLAT,KMRUN,ALARMTYPE,TRXDATETIME)
                                                                   VALUES ('$your_date','$your_time','$from','$fintrig','$lac','$cid','$longfin', '$latfin','$locfin','$tlong','$tlat','$kmrun','$altype','$smsdtfin')";
$res1 = mssql_query($queryresultgpscur) or print("Error " . mssql_get_last_message());
}
}
}

}
elseif ($fintrig2 == '0' || $fintrig2 == '4') {
$fintrig = "ENGINE OFF";
//CHECK IF ENGINE ON EXISTS
$checkengineon = "SELECT COUNT(*) as [COUNT] FROM [Mark_Fast_Testing].[dbo].ALARM WHERE account = '$from' and MSG = 'ENGINE OFF'";
$result = mssql_query($checkengineon);
while ($fin = mssql_fetch_array($result))
{
$rowcount = $fin['COUNT'];
}
if ($rowcount == '0') {
//INSERT TO ALARM
$queryresultgpscur = "INSERT INTO [Mark_Fast_Testing].[dbo].ALARM (TRXDATE,TRXTIME,ACCOUNT,MSG,LAC,CID,LONG,LAT,LOCATION,TLONG,TLAT,KMRUN,ALARMTYPE,TRXDATETIME)
                                                                   VALUES ('$your_date','$your_time','$from','$fintrig','$lac','$cid','$longfin', '$latfin','$locfin','$tlong','$tlat','$kmrun','$altype','$smsdtfin')";
$res1 = mssql_query($queryresultgpscur) or print("Error " . mssql_get_last_message());
}
else{
$checklatest = "SELECT TOP 1 MSG FROM [Mark_Fast_Testing].[dbo].ALARM WHERE account = '$from' and (MSG = 'ENGINE OFF' OR MSG = 'ENGINE ON') order by TRXDATETIME DESC";
$result = mssql_query($checklatest);
while ($fin = mssql_fetch_array($result))
{
$latest = $fin['MSG'];
if ($latest == "ENGINE ON") {
$queryresultgpscur = "INSERT INTO [Mark_Fast_Testing].[dbo].ALARM (TRXDATE,TRXTIME,ACCOUNT,MSG,LAC,CID,LONG,LAT,LOCATION,TLONG,TLAT,KMRUN,ALARMTYPE,TRXDATETIME)
                                                                   VALUES ('$your_date','$your_time','$from','$fintrig','$lac','$cid','$longfin', '$latfin','$locfin','$tlong','$tlat','$kmrun','$altype','$smsdtfin')";
$res1 = mssql_query($queryresultgpscur) or print("Error " . mssql_get_last_message());
}
}
}
}
elseif ($fintrig2 == '5') {
$fintrig = "PANIC BUTTON AND ENGINE OFF";
//INSERT TO ALARM
$queryresultgpscur = "INSERT INTO [Mark_Fast_Testing].[dbo].ALARM (TRXDATE,TRXTIME,ACCOUNT,MSG,LAC,CID,LONG,LAT,LOCATION,TLONG,TLAT,KMRUN,ALARMTYPE,TRXDATETIME)
                                                                   VALUES ('$your_date','$your_time','$from','$fintrig','$lac','$cid','$longfin', '$latfin','$locfin','$tlong','$tlat','$kmrun','$altype','$smsdtfin')";
$res1 = mssql_query($queryresultgpscur) or print("Error " . mssql_get_last_message());
}
elseif ($fintrig2 == '1') {
$fintrig = "PANIC BUTTON";
$altype = "7";
//INSERT TO ALARM
$queryresultgpscur = "INSERT INTO [Mark_Fast_Testing].[dbo].ALARM (TRXDATE,TRXTIME,ACCOUNT,MSG,LAC,CID,LONG,LAT,LOCATION,TLONG,TLAT,KMRUN,ALARMTYPE,TRXDATETIME)
                                                                   VALUES ('$your_date','$your_time','$from','$fintrig','$lac','$cid','$longfin', '$latfin','$locfin','$tlong','$tlat','$kmrun','$altype','$smsdtfin')";
$res1 = mssql_query($queryresultgpscur) or print("Error " . mssql_get_last_message());
}
elseif ($fintrig2 == '3' || $fintrig2 == '7' || $fintrig2 == '9' || $fintrig2 == 'D' || $fintrig2 == 'B' || $fintrig2 == 'F') {
$fintrig = "PANIC BUTTON AND ENGINE ON";
//INSERT TO ALARM
$queryresultgpscur = "INSERT INTO [Mark_Fast_Testing].[dbo].ALARM (TRXDATE,TRXTIME,ACCOUNT,MSG,LAC,CID,LONG,LAT,LOCATION,TLONG,TLAT,KMRUN,ALARMTYPE,TRXDATETIME)
                                                                   VALUES ('$your_date','$your_time','$from','$fintrig','$lac','$cid','$longfin', '$latfin','$locfin','$tlong','$tlat','$kmrun','$altype','$smsdtfin')";
$res1 = mssql_query($queryresultgpscur) or print("Error " . mssql_get_last_message());
}


echo "</br>";
echo "<strong>ACCOUNT:</strong>" .$from;
echo "</br>";
echo "<strong>DATETIME:</strong>" ."DATE NOW";
echo "</br>";
echo "<strong>GPSDATE:</strong>" .$gpsdate;
echo "</br>";
echo "<strong>GPSTIME:</strong>" .$gpstime;
echo "<br/>";
echo "<strong>TRXDATE:</strong>" .$your_date;
echo "<br/>";
echo "<strong>TRXTIME:</strong>" .$your_time;
echo "<br/>";
echo "<strong>PHILTIME:</strong>" .$philtime;
echo "<br/>";
echo "<strong>LONG:</strong>" .$longfin;
echo "<br/>";
echo "<strong>LAT:</strong>" .$latfin;
echo "<br/>";
echo "<strong>TLONG:</strong>" .$tlong;
echo "<br/>";
echo "<strong>TLAT:</strong>" .$tlat;
echo "<br/>";
echo "<strong>SPEED:</strong>" .$finspeed;
echo "<br/>";
echo "<strong>STREET:</strong>" .$street;
echo "<br/>";
echo "<strong>MUNICIPAL:</strong>" .$municipal;
echo "<br/>";
echo "<strong>ENGINE:</strong>" .$enginefin;
echo "<br/>";
echo "<strong>LAC:</strong>" .$lac;
echo "<br/>";
echo "<strong>CID:</strong>" .$cid;
echo "<br/>";
echo "<strong>LOCATION:</strong>" .$locfin;
echo "<br/>";
echo "<strong>TLOCATION:</strong>" .$tloc;
echo "<br/>";
echo "<strong>COMPANY:</strong>" .$companyfin;
echo "<br/>";
echo "<strong>PLATENO:</strong>" .$pltefin;
echo "<br/>";
echo "<strong>EVENTS:</strong>" .$trig;
echo "<br/>";
echo "<strong>MESSAGE:</strong>" .$fintrig;
echo "<br/>";
echo "<strong>DEVSTATUS:</strong>" .$statusfin;
echo "<br/>";
echo "<strong>DEVTYPE:</strong>" .$findet;
echo "<br/>";
echo "<strong>LastValidDateTime:</strong>" ." DATE TODAY";
echo "<br/>";
echo "<strong>KMRUN:</strong>" .$kmrun;
echo "<br/>";


//CHECK IF DATA IS PRESENT IN TBLCURRENT...IF PRESENT REMOVE BUT GET LONG AND LAT FIRST
$getlatlong = "SELECT lat,long from [Mark_Fast_Testing].[dbo].tblcurrent WHERE account = '$from'";
$result = mssql_query($getlatlong);
while ($old = mssql_fetch_array($result))
{
$oldlong = $old['long'];
$oldlat = $old['lat'];
}
$removeresultkm = "DELETE FROM [Mark_Fast_Testing].[dbo].tblcurrent WHERE account = '$from'";
$res = mssql_query($removeresultkm) or print("Error " . mssql_get_last_message());
//INSERT INTO TBLCURRENT
$queryresultcurrent = "INSERT INTO [Mark_Fast_Testing].[dbo].tblcurrent (ACCOUNT,DATETIME,GPSDATE,GPSTIME,TRXDATE,TRXTIME,PHILTIME,LONG,LAT,TLONG,TLAT,
                                                                                                              SPEED,DEVICESTATUS,STREET,MUNICIPAL,ENGINE,LAC,CID,LOCATION,TLOCATION,EVENTS,OLDLONG,OLDLAT,status2,kmrun,REMARKS, DeviceType,LastValidDateTime,plateno)
                                                               VALUES ('$from', getdate(), '$gpsdate', '$gpstime', '$your_date', '$your_time', '$philtime', '$longfin', '$latfin',
                                                                       '$tlong', '$tlat', '$speed', '$statusfin', '$street', '$municipal', '$enginefin', '$lac', '$cid', '$locfin', '$tloc', '$trig','$oldlong','$oldlat','0', '$kmrun', '$finspeed', '$findet', getdate(),'$pltefin')";
$res1 = mssql_query($queryresultcurrent) or print("Error " . mssql_get_last_message());
//INSERT TO TBLGPS
$queryresultgps = "INSERT INTO [Mark_Fast_Testing].[dbo].tblgps (ACCOUNT,DATETIME,GPSDATE,GPSTIME,TRXDATE,TRXTIME,PHILTIME,LONG,LAT,TLONG,TLAT,
                                                                                                              SPEED,DEVICESTATUS,STREET,MUNICIPAL,ENGINE,LAC,CID,LOCATION,TLOCATION,EVENTS,kmrun,REMARKS, DeviceType,LastValidDateTime,plateno)
                                                               VALUES ('$from', getdate(), '$gpsdate', '$gpstime', '$your_date', '$your_time', '$philtime', '$longfin', '$latfin',
                                                                       '$tlong', '$tlat', '$speed', '$statusfin', '$street', '$municipal', '$enginefin', '$lac', '$cid', '$locfin', '$tloc', '$trig', '$kmrun', '$finspeed', '$findet', getdate(),'$pltefin')";
$res1 = mssql_query($queryresultgps) or print("Error " . mssql_get_last_message());
//INSERT TO TBLGPSCUR
$queryresultgpscur = "INSERT INTO [Mark_Fast_Testing].[dbo].tblgpscur (ACCOUNT,DATETIME,GPSDATE,GPSTIME,TRXDATE,TRXTIME,PHILTIME,LONG,LAT,TLONG,TLAT,
                                                                                                              SPEED,DEVICESTATUS,STREET,MUNICIPAL,ENGINE,LAC,CID,LOCATION,TLOCATION,EVENTS,OLDLONG,OLDLAT,kmrun,REMARKS, DeviceType,LastValidDateTime,plateno)
                                                               VALUES ('$from', getdate(), '$gpsdate', '$gpstime', '$your_date', '$your_time', '$philtime', '$longfin', '$latfin',
                                                                       '$tlong', '$tlat', '$speed', '$statusfin', '$street', '$municipal', '$enginefin', '$lac', '$cid', '$locfin', '$tloc', '$trig','$oldlong','$oldlat', '$kmrun', '$finspeed', '$findet', getdate(),'$pltefin')";
$res1 = mssql_query($queryresultgpscur) or print("Error " . mssql_get_last_message());


}
mssql_close($con);



function haversineGreatCircleDistance(
$latitudeFrom, $longitudeFrom, $latitudeTo, $longitudeTo, $earthRadius = 6371000)
{
// convert from degrees to radians
$latFrom = deg2rad($latitudeFrom);
$lonFrom = deg2rad($longitudeFrom);
$latTo = deg2rad($latitudeTo);
$lonTo = deg2rad($longitudeTo);

$latDelta = $latTo - $latFrom;
$lonDelta = $lonTo - $lonFrom;

$angle = 2 * asin(sqrt(pow(sin($latDelta / 2), 2) +
cos($latFrom) * cos($latTo) * pow(sin($lonDelta / 2), 2)));
return $angle * $earthRadius;
}
?>
\$\endgroup\$
  • 2
    \$\begingroup\$ (also, for the sake of your and our eyes, if you're going to post that in codereview, start by presenting your code with a better indentation!..) \$\endgroup\$ – briosheje May 20 '15 at 13:54
  • \$\begingroup\$ How long does the form take to process? Maybe there is some work that is suitable for offline processing, so control returns to the user faster, and the processing is done in the background? \$\endgroup\$ – halfer May 20 '15 at 15:10
  • \$\begingroup\$ Is there some particular reason you're using LIKE with numbers (lat, long)? \$\endgroup\$ – cHao May 20 '15 at 16:11
2
\$\begingroup\$

Firstly, you know that this code is not readable at all?

Secondly, you can replace double quotes with single quotes where you don't need string interpolation and escape-sequences, it works a little bit faster.

Thirdly, I see that there is a lot of database operations, try to run script as daemon this would save time on parsing and connecting to database also you should use prepared statements (I don't know are there some in MSSQL).

Fourthly, try to introduce some caching layer like apc or redis between MSSQL and your code, or simply move to oracle db and then you can just subscribe to updates.

Others: you don't need to parse strings to get some part of DATETIME, just use strtotime and \DateTime, which have method \DateTime::format which you can use to get specific part of datetime.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy