rflorentino

Wishing to make it possible to be able to process more than 10 rows per second without slowing down.

I have a php script which is read by batch file. This php script is parsing 1 line of row from mssql, process it and inserts it to another table. This is currently procsssing 1 data per 1 to 3 seconds. I wish to make it faster. Any ideas are welcome. Here's the code i did. <?php // Connect to the database (host, username, password) $con = mssql_connect('XXXXXXXXXXXXX','sa','XXXXXXXXX') or die('Could not connect to the server!'); // Select a database: mssql_select_db('XXXXXXXXXXXXXXXXXXXXX') or die('Could not select a database.'); // Example query: (TOP 10 equal LIMIT 0,10 in MySQL) $SQL = "SELECT TOP 1 * FROM table 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 = '351856040520710,241111;1R,150622062008,A,1449.5054N,12020.0745E,7.94,267,1.2,21020000;'; $from = $Row['smsfr']; //$from = '351856040520710'; $dataid = $Row['id']; //$dataid = '5176'; $smsdt = $Row['smsdt']; //$smsdt = '2015-06-17 2015-06-22 14:20:00'; $smsdtfin = date("Y-m-d H:i:s", strtotime($smsdt)); $your_date = date("m/d/Y", 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 table SET isHex = '5' WHERE id = '$dataid'"; //die($SQL2); $result2 = mssql_query($SQL2) or die('A error occured: ' . mysql_error()); if (($datastringlength == '105' || $datastringlength > '105') && ($det == 'RA1' || $det == 'RA2')) { if ($det == 'RA1' || $det == 'RA2') { $findet = "R10-V2"; } $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, 6); $battery = substr($datastring, 77, 2); $batfin = hexdec(substr($datastring, 77, 2)) + 1; //TRIANGULATION $laccid = "SELECT TOP 1 LAC, CID, LONG, LAT, street, municipal FROM [XXXXXXXXXXXXXXXXXXXXX].[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 TOP 1 NAME, MUNICIPALI FROM [XXXXXXXXXXXXXXXXXXXXX].[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 top 1 PLATENO,COMPANY FROM [XXXXXXXXXXXXXXXXXXXXX].[dbo].account where SENDERNO = '$from'"; $result = mssql_query($platetemp); while ($fin = mssql_fetch_array($result)) { $pltefin = $fin['PLATENO']; $companyfin = $fin['COMPANY']; } //echo "ENGINE STATUS: " .$enginefin; $speed = substr($datastring, 46, 5); // SPEED if ($speed > '0' && $enginefin == 'ON') { $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, -)/1000)*1.852),0); $removeresultkm = "DELETE FROM [XXXXXXXXXXXXXXXXXXXXX].[dbo].tblcurrent WHERE account = '$from'"; $res = mssql_query($removeresultkm) or print("Error " . mssql_get_last_message()); //INSERT INTO TBLCURRENT $queryresultcurrent = "INSERT INTO [XXXXXXXXXXXXXXXXXXXXX].[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()); echo "INSERT TO CURRENT DONE.....MOVING TO TBLGPS"; } else if (($datastringlength > '130' && $datastringlength < '150') && $det == '241') { if ($det == '241') { $findet = "RX-8W"; } list($account, $extra, $datetime, $status, $latraw, $longraw, $speed,$extra2,$extra3,$engine,$extra5,$extra6,$extra7,$extra8,$extra9,$kmrun) = explode(",", $datastring); $hour = floor((substr($datetime, 6, 2))); $hour = ($hour<10)?"0".$hour:$hour; $minutes = substr($datetime, 8, 2); $second = substr($datetime, 10, 2); $day = substr($datetime, 4, 2); $month = substr($datetime, 2, 2); $year = 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($latraw, 0, 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($longraw, 0, 10); // long $longdeg = substr($long, 0, 3); $longmin = round((substr($long, 3, 7)/60),6); $longfin = $longdeg + $longmin; $longfinal = substr($longfin, 0, 6); //TRIANGULATION $laccid = "SELECT TOP 1 LAC, CID, LONG, LAT, street, municipal FROM [XXXXXXXXXXXXXXXXXXXXX].[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 [XXXXXXXXXXXXXXXXXXXXX].[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']; } $finengine = substr($engine, 0, 1); if ($finengine == '2' || $finengine == 'A') { $remengine = "ON"; } elseif ($finengine == '0') { $remengine = "OFF"; } //echo "ENGINE STATUS:" .$remengine; $events = substr($engine, 0, ; if ($status = 'A') { $finstat = "OK"; } else{ $finstat = "Message Not Valid"; } $finkmrun = round($kmrun/3600 , 0); if ($speed > '0' && $remengine == 'ON') { $remarks = "Running"; } else{ $remarks = "Stopped"; } $platetemp = "SELECT PLATENO,COMPANY FROM [XXXXXXXXXXXXXXXXXXXXX].[dbo].account where SENDERNO = '$from'"; $result = mssql_query($platetemp); while ($fin = mssql_fetch_array($result)) { $pltefin = $fin['PLATENO']; $companyfin = $fin['COMPANY']; } $removeresultkm = "DELETE FROM [XXXXXXXXXXXXXXXXXXXXX].[dbo].tblcurrent WHERE account = '$from'"; $res = mssql_query($removeresultkm) or print("Error " . mssql_get_last_message()); //INSERT INTO TBLCURRENT $queryresultcurrent = "INSERT INTO [XXXXXXXXXXXXXXXXXXXXX].[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', '$finstat', '$street', '$municipal', '$remengine', '$lac', '$cid', '$locfin', '$tloc', '$events','$oldlong','$oldlat','0', '$finkmrun', '$remarks', '$findet', getdate(),'$pltefin')"; $res1 = mssql_query($queryresultcurrent) or print("Error " . mssql_get_last_message()); echo "INSERT TO CURRENT DONE.....MOVING TO TBLGPS"; } else if ($datastringlength > '80' && $datastringlength < '100' && ($det == '241' || $det == '740')) { if ($det == '241' || $det == '740') { $findet = "RX-8W"; } list($account, $extra, $datetime, $status, $latraw, $longraw, $speed,$extra2,$extra3,$engine) = explode(",", $datastring); $your_dates = date("Y-m-d 00:00:00.000", strtotime($your_date)); $hour = floor((substr($datetime, 6, 2))); $hour = ($hour<10)?"0".$hour:$hour; $minutes = substr($datetime, 8, 2); $second = substr($datetime, 10, 2); $day = substr($datetime, 4, 2); $month = substr($datetime, 2, 2); $year = 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")); $finengine = substr($engine, 0, 1); if ($finengine == '2' || $finengine == 'A') { $remengine = "ON"; } elseif ($finengine == '0') { $remengine = "OFF"; } //echo "ENGINE STATUS1:" .$remengine; //COMPUTATION FOR LAT $lat = substr($latraw, 0, 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($longraw, 0, 10); // long $longdeg = substr($long, 0, 3); $longmin = round((substr($long, 3, 7)/60),6); $longfin = $longdeg + $longmin; $longfinal = substr($longfin, 0, 6); //TRIANGULATION $laccid = "SELECT TOP 1 LAC, CID, LONG, LAT, street, municipal FROM [XXXXXXXXXXXXXXXXXXXXX].[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 [XXXXXXXXXXXXXXXXXXXXX].[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']; } $platetemp = "SELECT PLATENO,COMPANY FROM [XXXXXXXXXXXXXXXXXXXXX].[dbo].account where SENDERNO = '$account'"; $result = mssql_query($platetemp); while ($fin = mssql_fetch_array($result)) { $pltefin = $fin['PLATENO']; $companyfin = $fin['COMPANY']; } $events = substr($engine, 0, ; if ($speed > '0' && $remengine == 'ON') { $remarks = "Running"; } else{ $remarks = "Stopped"; } if ($status = 'A') { $finstat = "OK"; } else{ $finstat = "Message Not Valid"; } $removeresultkm = "DELETE FROM [XXXXXXXXXXXXXXXXXXXXX].[dbo].tblcurrent WHERE account = '$from'"; $res = mssql_query($removeresultkm) or print("Error " . mssql_get_last_message()); //INSERT INTO TBLCURRENT $queryresultcurrent = "INSERT INTO [XXXXXXXXXXXXXXXXXXXXX].[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', '$finstat', '$street', '$municipal', '$remengine', '$lac', '$cid', '$locfin', '$tloc', '$events','$oldlong','$oldlat','0', '0', '$remarks', '$findet', getdate(),'$pltefin')"; $res1 = mssql_query($queryresultcurrent) or print("Error " . mssql_get_last_message()); echo "INSERT TO CURRENT DONE.....MOVING TO TBLGPS"; } else { list($rawaccount,$productcoderaw,$rawdateandtime,$rawdevicestatus,$latraw,$longraw,$rawspeed,$rawcourse,$hdop,$rawevents,$rawdatetimesecond,$numofusedgpssat,$alt,$extpower,$intpower,$rawodometer) = explode(",", $datastring); list($productcode,$firstmsgtype) = explode(";", $productcoderaw); list($events,$secondmsgtype) = explode(";", $rawevents); $account = $rawaccount; $datetime = date('Y-m-d H:i:s'); //GPS DATE TO PHILTIME// $hour = floor((substr($rawdateandtime, 6, 2))); $hour = ($hour<10)?"0".$hour:$hour; $minutes = substr($rawdateandtime, 8, 2); $second = substr($rawdateandtime, 10, 2); $day = substr($rawdateandtime, 4, 2); $month = substr($rawdateandtime, 2, 2); $year = substr($rawdateandtime, 0, 2); $gpsdatetime= "20" .$year. "-" .$month. "-" .$day. " " .$hour. ":" .$minutes. ":" .$second; $gpsdate = date("Y-m-d 00:00:00.000", strtotime($gpsdatetime)); $gpstime = date("H:i:s", strtotime($gpsdatetime)); $trxdate = date("Y-m-d 00:00:00.000", strtotime($smsdt)); $trxtime = date("H:i:s", strtotime($smsdt)); $philtime = date("Y-m-d H:i:s", strtotime($gpsdatetime . " +8 hours")); //GPS DATE TO PHILTIME// $devstatus = $rawdevicestatus; //LONG | LAT | TLONG | TLAT// $lat = substr($latraw, 0, 9); // lat $latdeg = substr($lat, 0, 2); $latmin = round((substr($lat, 2, 7)/60),6); $latfin = $latdeg + $latmin; $tlatfinder = substr($latfin, 0, 6); $long = substr($longraw, 0, 10); // long $longdeg = substr($long, 0, 3); $longmin = round((substr($long, 3, 7)/60),6); $longfin = $longdeg + $longmin; $tlongfinder= substr($longfin, 0, ; $laccid = "SELECT TOP 1 LAC, CID, LONG, LAT, street, municipal from [XXXXXXXXXXXXXXXXXXXXX].[dbo].TRIANGULATION WHERE LONG LIKE '%$tlongfinder%' AND LAT LIKE '%$tlatfinder%'"; //die($laccid); $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']; } //LONG | LAT | TLONG | TLAT | TLOC// $speed = $rawspeed; if ($rawcourse == "") { $course = "0"; } else{ $course = $rawcourse; } //STREET || MUNICIPAL || LOCATION// $loctemp = "SELECT NAME, MUNICIPALI from [XXXXXXXXXXXXXXXXXXXXX].[dbo].ROAD where LONG LIKE '%$tlongfinder%' AND LAT LIKE '%$tlatfinder%'"; $result = mssql_query($loctemp); while ($fin = mssql_fetch_array($result)) { $locfin = $fin['NAME'] . " " . $fin['MUNICIPALI']; $street = $fin['NAME']; $municipal = $fin['MUNICIPALI']; } //STREET || MUNICIPAL || LOCATION// $detectengine= substr($events, 0, 1); if ($detectengine == '2') { $engine = "ON"; } elseif ($detectengine == '0') { $engine = "OFF"; } $kmrun = round($rawodometer/3600 , 0); //CHECKING FOR EVENTS FOR ALARM// $firstevent = substr($events, 0, 1); $secondevent = substr($events, 1, 1); $thirdevent = substr($events, 2, 1); $fourthevent = substr($events, 3, 1); $fifthevent = substr($events, 4, 1); $sixthevent = substr($events, 5, 1); $seventhevent = substr($events, 6, 2); $eightevent = substr($events, 8, 1); $ninthevent = substr($events, 9, 1); //CHECKING FOR EVENTS FOR ALARM// //PLATE || COMPANY// $platetemp = "SELECT PLATENO,COMPANY from [XXXXXXXXXXXXXXXXXXXXX].[dbo].account where SENDERNO = '$account'"; //die($platetemp); $result = mssql_query($platetemp); while ($fin = mssql_fetch_array($result)) { $pltefin = $fin['PLATENO']; $companyfin = $fin['COMPANY']; } //PLATE || COMPANY// if ($speed > '0' && $engine == 'ON') { $remarks = "Running"; } else{ $remarks = "Stopped"; } if ($rawdevicestatus == 'A') { $devstatus = "OK"; } else{ $devstatus = "Message Not Valid"; } $devtype = "RV-8"; $status2 = "0"; $removeresultkm = "DELETE FROM [XXXXXXXXXXXXXXXXXXXXX].[dbo].tblcurrent WHERE account = '$account'"; $res = mssql_query($removeresultkm) or print("Error " . mssql_get_last_message()); //INSERT INTO TBLCURRENT $queryresultcurrent = "INSERT INTO [XXXXXXXXXXXXXXXXXXXXX].[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 ('$account', '$datetime', '$gpsdate', '$gpstime', '$trxdate', '$trxtime', '$philtime', '$longfin', '$latfin', '$tlong', '$tlat', '$speed', '$devstatus', '$street', '$municipal', '$engine', '$lac', '$cid', '$locfin', '$tloc', '$events','$oldlong','$oldlat','0', '0', '$remarks', '$devtype', '$datetime','$pltefin')"; //die($queryresultcurrent); echo "RV-8 Adding....."; $res1 = mssql_query($queryresultcurrent) or print("Error " . mssql_get_last_message()); echo "INSERT TO CURRENT DONE.....MOVING TO TBLGPS"; } mssql_close($con); ?>