为大查询响应优化内存资源

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.

Answer 1

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.