orm: Batch Processing ,out of memory

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/batch-processing.html#iterating-large-results-for-data-processing

$q = $this->_em->createQuery('select u from MyProject\Model\User u');
$iterableResult = $q->iterate();
foreach ($iterableResult as $row) {
    //nothing
    $this->_em->detach($row[0]);
}

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to all
ocate 1425408 bytes) in C:\mmood\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\P
DOConnection.php on line 104


  [Symfony\Component\Debug\Exception\OutOfMemoryException]
  Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate
  1425408 bytes)

Total: 4000,000 rows I tried the code and found that was out of memory

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 23 (11 by maintainers)

Most upvoted comments

$em->getConnection()->getConfiguration()->setSQLLogger(null);

worked for me

stouch on Nov 24, 2019

As a workaround, I ended up iterating over 10K rows at a time:

$lastId = 0;

while (true) {
    $iterable = $this->entityManager
        ->getRepository(UploadView::class)
        ->createQueryBuilder('v')
        ->where('v.id > :id')
        ->orderBy('v.id', 'asc')
        ->setMaxResults(10000)
        ->getQuery()
        ->iterate([
            'id' => $lastId,
        ]);

    $iterated = false;
    foreach ($iterable as $row) {
        $iterated = true;
        /** @var UploadView $view */
        $view = $row[0];
        $lastId = $view->getId();
        // do actual processing
        $this->entityManager->detach($view);
    }

    $this->entityManager->clear();

    if (!$iterated) {
        break;
    }
}

elnur on Sep 10, 2017

Ah yes, fairly sure that the ORM doesn’t lazily iterate over DBAL resultsets: that is a big issue that needs work.

Ocramius on Sep 10, 2017

$em->getConnection()->getConfiguration()->setSQLLogger(null);

worked for me

It works perfectly

rafalrudnicki on Apr 8, 2020

Try this, works for me

$conn = $emd->getConnection(); $conn->getWrappedConnection() ->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); $conn->getConfiguration()->setSQLLogger(null);

Cirrusware on Mar 3, 2020