Making Doctrine Fast

Fix N+1 with fetch joins, hydrate read-only data as arrays, and batch large writes so the entity manager stays small.

Syntax$qb->leftJoin('a.author', 'u')->addSelect('u')

Three things make Doctrine slow: the number of queries, the cost of turning rows into objects (hydration), and the memory the entity manager holds while it tracks them.

Query count: the N+1

An association is lazy by default, meaning the related object is not loaded until you touch it. Load twenty articles and print each author's name and you get one query for the articles plus twenty for the authors. The fix is a fetch join: join the association and name its alias in the select, so one query returns both sides. In the query builder that is leftJoin('a.author', 'u') followed by addSelect('u'). In DQL it is the same thing written SELECT a, u FROM App\Entity\Article a LEFT JOIN a.author u. A join without the alias in the select filters rows but loads nothing.

Hydration: do not build objects you will not change

For data you only display, ask for arrays: getArrayResult(), which is shorthand for getResult(Query::HYDRATE_ARRAY). If you want entities but will not modify them, set $query->setHint(Query::HINT_READ_ONLY, true). Doctrine then skips the snapshot it would otherwise keep of every object in order to detect changes.

Memory: batch and clear

Persisting 100,000 rows in one loop keeps every entity in the identity map, the table of objects the manager is managing. Call flush() every few hundred rows, then clear(), and the memory is released. For reading a large result, toIterable() hydrates one entity at a time instead of the whole set.

Example

Example · php
<?php

// src/Repository/ArticleRepository.php

declare(strict_types=1);

namespace App\Repository;

use App\Entity\Article;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @extends ServiceEntityRepository<Article>
 */
final class ArticleRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Article::class);
    }

    /**
     * @return array<int, Article>
     */
    public function findRecentWithAuthors(int $limit): array
    {
        return $this->createQueryBuilder('a')
            // The join alone only makes the table available to the query.
            ->leftJoin('a.author', 'u')
            // addSelect() is what makes it a fetch join: the authors are
            // hydrated now, so touching $article->getAuthor() costs nothing.
            ->addSelect('u')
            ->orderBy('a.publishedAt', 'DESC')
            // Safe here: author is a to-one association, so one article is
            // still one row.
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }
}

// Rendering 20 articles and each author's name:
//   without addSelect('u'):  1 query + 20 queries
//   with addSelect('u'):     1 query

When to use it

  • A list page issues one query per row and you need it to issue one.
  • An export or report page builds thousands of entities it never modifies.
  • A nightly import grows until the process runs out of memory.
  • Backfill a new column across a table too large to load at once.

More examples

Read-only reads: arrays and the read-only hint

Use the array form when the result goes straight into a template or a JSON response, and the read-only hint when you need real objects for their methods but will not write them back. The hint has a consequence worth saying out loud: a change made to a read-only entity is not written on flush, so do not use it on anything you intend to update.

Example · php
<?php

// src/Repository/ArticleRepository.php (two more methods on the same class)

use Doctrine\ORM\Query;

/**
 * Titles for a sidebar. Nothing here is ever modified.
 *
 * @return array<int, array{id: int, title: string}>
 */
public function latestTitles(int $limit): array
{
    return $this->createQueryBuilder('a')
        ->select('a.id', 'a.title')   // only the two columns the page prints
        ->orderBy('a.publishedAt', 'DESC')
        ->setMaxResults($limit)
        ->getQuery()
        // getArrayResult() is shorthand for getResult(Query::HYDRATE_ARRAY).
        // The two lines are the same call.
        ->getArrayResult();
}

/**
 * Entities, because a Twig template calls methods on them, but Doctrine is
 * told they will not change.
 *
 * @return array<int, Article>
 */
public function latestReadOnly(int $limit): array
{
    $query = $this->createQueryBuilder('a')
        ->orderBy('a.publishedAt', 'DESC')
        ->setMaxResults($limit)
        ->getQuery();

    // No change-tracking snapshot is kept, so flush() will ignore these.
    $query->setHint(Query::HINT_READ_ONLY, true);

    return $query->getResult();
}

A batched import command

persist() only schedules the insert; flush() is what writes, and clear() is what frees. Without the clear the process grows until it is killed, and without the final flush the last partial batch is lost. Reach for this shape for any import, backfill or migration script that touches more rows than fit comfortably in memory.

Example · php
<?php

// src/Command/ImportPricesCommand.php

declare(strict_types=1);

namespace App\Command;

use App\Entity\Price;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\Console\Attribute\AsCommand;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Style\SymfonyStyle;

#[AsCommand(name: 'app:import-prices', description: 'Imports prices from a CSV file')]
final class ImportPricesCommand extends Command
{
    private const BATCH_SIZE = 500;

    public function __construct(private readonly EntityManagerInterface $em)
    {
        parent::__construct();
    }

    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        $io = new SymfonyStyle($input, $output);
        $handle = fopen('var/import/prices.csv', 'r');
        $imported = 0;

        while (false !== ($row = fgetcsv($handle))) {
            $this->em->persist(new Price((string) $row[0], (int) $row[1]));

            if (0 === ++$imported % self::BATCH_SIZE) {
                $this->em->flush();   // writes this batch
                $this->em->clear();   // detaches it, releasing the memory
                $io->writeln(sprintf('%d rows', $imported));
            }
        }

        fclose($handle);

        // The last partial batch still needs writing.
        $this->em->flush();

        $io->success(sprintf('Imported %d prices.', $imported));

        return Command::SUCCESS;
    }
}

// php bin/console app:import-prices
// 500 rows
// 1000 rows
// 1500 rows
//
//  [OK] Imported 1732 prices.
//

Updating a large table one entity at a time

This is the read-side counterpart of the batched import, and it is the right tool when the new value has to come from PHP. If the change is expressible in SQL, a DQL UPDATE is far faster and touches no entities at all. One restriction: toIterable() cannot be used on a query that fetch-joins a collection, because such a result set cannot be hydrated incrementally.

Example · php
<?php

// src/Service/PriceRounder.php

declare(strict_types=1);

namespace App\Service;

use Doctrine\ORM\EntityManagerInterface;

final class PriceRounder
{
    private const BATCH_SIZE = 500;

    public function __construct(private readonly EntityManagerInterface $em)
    {
    }

    public function roundAll(): int
    {
        $query = $this->em->createQuery('SELECT p FROM App\Entity\Price p');
        $updated = 0;

        // toIterable() hydrates one entity per step rather than the whole
        // result set, so memory stays flat no matter how many rows there are.
        foreach ($query->toIterable() as $price) {
            $price->roundToWholeUnits();

            if (0 === ++$updated % self::BATCH_SIZE) {
                $this->em->flush();
                $this->em->clear();
            }
        }

        $this->em->flush();

        return $updated;
    }
}

Related

symfony lazy eagersymfony hydrationsymfony querybuildersymfony paginationsymfony profiler deep

Discussion

  • Be the first to comment on this lesson.