optimizing memory resources for a big query response

Question

I have to perform a query that can generates a very big string in response (up to 1Gb), which is basically a big, big JSON array. Yes, pagination is in order but I'm stressing the concept in order to get you the idea. Symfony simply uses doctrine to get the response:

    $stmt = $this->getEntityManager()->getConnection()->prepare(self::Q_GET_NUM_TIMEBOX);

    $stmt->bindValue('t_end', $tEnd);
    $stmt->bindValue('t_granularity', $tGranularity);
    $stmt->bindValue('t_span', $varSelection->getStart());

    $stmt->execute();

    $resData = $stmt->fetchColumn(0);

and then I create a Response by setting the content I had in return from the execute.

    $res = new Response();
    $res->setStatusCode(200);
    $res->headers->set('Content-Type', 'application/json');
    $res->setContent($resData);

Keep in mind I oversimplified the code for the sake of clarity: I actually have a controller, a handler service performing the request and a Repository returning the query response.

Back straight to the problem: this implies that PHP must hold that big amount of data in memory and I was wondering if there was an lighter way to return the response in order to stress less PHP engine with big amount of data.

use a StreamedResponse in conjunction with an iterable query result — Matteo, Nov 22 '16 at 11:30
@Matteo that still does not directly solve the problem of encoding this data to JSON. — Gerry, Nov 22 '16 at 11:34
@Gerry no need of encoding. Postgres returns a string well-encoded already. Basically a function I wrote generates and returns it — Bertuz, Nov 22 '16 at 11:36

score 4 · Accepted Answer · edited May 23 '17 at 10:30

this implies that PHP must hold that big amount of data

PHP is not required to keep in the memory a whole response body. Through output buffers and Symfony response streaming you can fetch result set row-by-row and send a data by chunks. Unfortunately I don't known well-tried solution for JSON stream encoding in PHP, but you can implement it manually (1, 2).

Update (2017-02-27):

Streaming JSON Encoder is a PHP library that provides a set of classes to help with encoding JSON in a streaming manner, i.e. allowing you to encode the JSON document bit by bit rather than encoding the whole document at once.

optimizing memory resources for a big query response

1 Answers1