CODEWITHSUNDEEP
phpmysqljson
Ng Sek Long
Ng Sek Long

Reputation: 4786

PHP echo json_encode line by line instead of at the end to tackle memory problem, is that a bad idea?

I need to output at least 500,000 row of records in JSON format, was originally using something like the following

<?php

  $con=mysqli_connect("localhost",$username,$password,databaseName);

  // Check connection
  if (mysqli_connect_errno())
  {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

  $query = "the query here";

  $result = mysqli_query($con,$query);

  $rows = array();
  while($r = mysqli_fetch_array($result)) {
    $rows[] = $r;
  }
  echo json_encode($rows);

  mysqli_close($con);
?>

That is I store everything in a variable, then output it at the end.

However I found out that with large records, I ran into the out of memory error Allowed memory size of xxxxx bytes exhausted (tried to allocate xx bytes)

Then, I modified the above to tackle the problem as following:

<?php
  //Same as above

  $result = mysqli_query($con,$query);
  $numItems = mysql_num_rows($result);
  $i = 0;
  echo "[";
  while($r = mysqli_fetch_array($result)) {
    echo json_encode($r);
    if(++$i !== $numItems) {
       echo ",";
    }
  }
  echo "]";

  mysqli_close($con);
?>

Instead of outputting the row at the end, I output each row during each MySQL iteration. So no need to stored all the row in a variable buffer.

It ran fine for the 500,000 rows of records that I have. However when I search online there is no one doing something that I does. So my question is:

  1. Is my approach of row by row echo json_encode() safe to use?
  2. If no, is there a better method to tackle the out of memory issue? (Resources is limited, increase memory is not an option)

Upvotes: 1

Views: 809

Answers (1)

tadman
tadman

Reputation: 211720

Since you don't have the memory to hold the entire JSON document in memory, consider an alternative that's more memory efficient:

Dump out each row as a simple array instead which avoids repeating the keys for every single row. For example instead of:

[
  { "id": 1, "valueA": "A", ... },
  { "id": 2, "valueA": "A", ... },
  ...
]

Consider doing:

{
  "columns": [ "id", "valueA", ... ],
  "values": [
    [ 1, "A" ],
    [ 2, "A" ],
    ...
  ]
}

Which results in a substantially smaller document in practice.

You may also want to consider JSON Streaming, or line-delimited JSON where you output one document per line:

{ "id": 1, "valueA": "A", ... }
{ "id": 2, "valueA": "A", ... }

While that doesn't reduce the overall size of the result it does make it way easier to parse on the other end. The reader only needs enough memory to hold the largest single line document, not all 500K of them at once.

It may also have performance benefits if your web server supports streaming results, as you can start transmitting records before all of them have been loaded.

Consider: If you don't have the memory to generate the document in its entirety, how can someone consume it without having a ton of memory on hand? Line-delimited JSON is much easier to read, you can just stream it in, quickly iterating over it.

Upvotes: 2

Related Questions