Jump to content

Parsing and inserting multiple rows from ms sql

rflorentino

By rflorentino
in PHP Coding Help

 Share

Recommended Posts

rflorentino Newbie

rflorentino

Posted
Posted

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);
?>
mac_gyver Prolific Member

mac_gyver

Posted
Posted

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.