rflorentino Posted August 4, 2015 Share Posted August 4, 2015 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); ?> Quote Link to comment Share on other sites More sharing options...
rflorentino Posted August 4, 2015 Author Share Posted August 4, 2015 Wishing to make it possible to be able to process more than 10 rows per second without slowing down. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted August 4, 2015 Share Posted August 4, 2015 do you really have four different data definitions stored in one table or is this an exercise to see how you would solve a made up problem? any performance problem is due to the queries and due to the number of overall queries. you would want to make sure that each query is as efficient as possible and that you run a minimum of different queries. are the queries that are using LIKE '%some_value.%' actually trying to find exact matches? if so, you should be using = comparisons. will the queries using TOP 1 only match at most one row in the table or can they match multiple rows but you only want one? do you have indexes set up in the database tables so that finding information would be as efficient as possible? so, how would i (probably) approach doing this. i would query for all your main data at once, loop over each row and split up the four different datastring formats to come up with one common set of data for each existing row, without performing any of the other select queries. insert this now, split up, common set of data into a new table. to retrieve any related data, just JOIN this new table with your TRIANGULATION, ROAD, and account tables. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.