CODEWITHSUNDEEP
mysqldoctrine-ormsymfony
Jonathan
Jonathan

Reputation: 15462

Improving performance on a large Doctrine query

In Symfony3, I'm using Doctrine's QueryBuilder to iterate up to 500k rows from my 35 million row table:

$query = $this->createQueryBuilder('l')
            ->where('l.foo = :foo')
            ->setParameter('foo', $foo)
            ->getQuery();

$results = $query->iterate();

foreach ($results as $result) {
   $em->clear();
   // My logic using $result[0]
}

The memory usage of this often approaches 512mb, before I even begin to iterate. Is there any further way I can optimise this? Am I correct in reading that hydration is turned off when iterating a query?

Upvotes: 3

Views: 4005

Answers (2)

Serhii Vasko
Serhii Vasko

Reputation: 443

Example from Doctrine docs

$batchSize = 20;
$i = 1;
$q = $em->createQuery('select u from MyProject\Model\User u');
foreach ($q->toIterable() as $user) {
    $user->increaseCredit();
    $user->calculateNewBonuses();
    ++$i;
    if (($i % $batchSize) === 0) {
        $em->flush(); // Executes all updates.
        $em->clear(); // Detaches all objects from Doctrine!
    }
}
$em->flush();

Upvotes: 0

Jakub Zalas
Jakub Zalas

Reputation: 36241

I had great results with generators. Perhaps processing results in a separate method helps PHP to cleanup unused objects. I'm not sure what you're doing to process your records, and cannot guarantee you'll get the same results, but in my case memory consumption remained constant through whole script execution:

public function getMyResults($foo)
{
    $query = $this->createQueryBuilder('l')
        ->where('l.foo = :foo')
        ->setParameter('foo', $foo)
        ->getQuery();

    foreach ($query->iterate() as $result) {
        yield $result[0]

        $em->clear();
    }
}

public function processMyResults($foo)
{
    foreach ($this->getMyResults($foo) as $result) {
    }
}

If this doesn't help, consider making a query with DBAL or PDO (both with the fetch() method to avoid fetching all records at once). Doctrine's iterator might leak memory (PDO's resultset shouldn't).

Doctrine will solve 80% of your problems. The remaining 20% is better approached without it.

Am I correct in reading that hydration is turned off when iterating a query?

No, unless you change the hydration mode. You can do it by passing a second argument to the iterate() method.

Upvotes: 15

Related Questions