CODEWITHSUNDEEP
phpmysqljsonperformance
user2635961
user2635961

Reputation: 379

Performance of Parsing JSON and Loading into MYSQL

I am trying to speed up the performance of this PHP code. I am running it through the browser on XAMPP using Apache and MYSQL on there.

My problem is that I am reading a very large 2GB JSON file. I cannot read it in one go as I do not have enough memory and thus I have to read it line by line

The code reads the JSON line by line. For each line (chunk of JSON data) a function (Function parse_item) is called which decodes the line of the JSON and then opens up the mysql database and inserts selected data from the decoded JSON line and then closes the MySQL database. I know its the inserting to the database that is causing my performance problems but not sure what to do.(ie when I remove the database and just echo the decoded JSON it is much faster)

The code (an abbreviated slice) works but is very slow...................

Can anybody help? I am new to PHP so any explanation in simple terms would be gratefully received.

Also .......if I ran this from the command line (ie loaded PHP to my PC) would it run much quicker??

Thanks John

//Parse Function
function parse_item($string) {

$data = json_decode($string);


$uid=($data->JsonScheduleV1->CIF_train_uid);
$stp=($data->JsonScheduleV1->CIF_stp_indicator);                                                      
$head=($data->JsonScheduleV1->schedule_segment->signalling_id);       
$startlocation=($data->JsonScheduleV1->schedule_segment->schedule_location[0]->tiploc_code);

require('connect_db.php');
mysqli_select_db($mysql_link,"timetable");
mysqli_query($mysql_link,"INSERT INTO railstp (uid,stp,head,startlocation)
  VALUES ('$uid','$stp','$head','$startlocation')");
mysqli_close($mysql_link);

if (is_null($data)) {
   die("Json decoding failed with error: ". json_last_error());
}

}


$file = 'CIF_PA_TOC_FULL_DAILY_toc-full';

// Slices up the JSON into lines and then
parse_item($string);



//

EDIT

I have a InnoDB database There are 20 keys - 2 are integers , 2 are dates and the rest text Not sure how many lines but guess over a 1,000 -not been able to run it long enough

Upvotes: 0

Views: 1978

Answers (1)

01e
01e

Reputation: 701

Create MySQL connection and close it per parse_item() function call will waste your script time and also your if statement doest have enough function!, so I'd rewrite your code: 

require('connect_db.php');
mysqli_select_db($mysql_link,"timetable");
function parse_item($string,&$mysql_link) {
  $data = json_decode($string);
  if (is_null($data)) {
     mysqli_close($mysql_link);die("Json decoding failed with error: ". json_last_error());
  }
  $uid=($data->JsonScheduleV1->CIF_train_uid);
  $stp=($data->JsonScheduleV1->CIF_stp_indicator);                                                      
  $head=($data->JsonScheduleV1->schedule_segment->signalling_id);       
  $startlocation=($data->JsonScheduleV1->schedule_segment->schedule_location[0]->tiploc_code);
  mysqli_query($mysql_link,"INSERT INTO railstp (uid,stp,head,startlocation)
                            VALUES ('$uid','$stp','$head','$startlocation')");
}


$file = 'CIF_PA_TOC_FULL_DAILY_toc-full';
$handle = fopen("c:\\folder\$file" , "r");
while(!feof($handle)){
   $string=fgets($handle); 
   parse_item($string,$mysql_link);       //REMEMBER: to pass your $mysql_link variable!
}
fclose($handle);
mysqli_close($mysql_link);

I hope this help you.

Upvotes: 1

Related Questions