Skip to content
Tutorials Center
My learning

Topics

Lesson 39 of 62 ยท Databases with Doctrine

Doctrine Performance and Pitfalls

Make Doctrine fast: spot the N+1 problem, use fetch joins, understand lazy proxies, process big tables in batches, add indexes and cache wisely.

An ORM makes the first version of a feature quick to write and, if you are not careful, slow to run. Doctrine hides SQL behind objects, which is a great convenience, until a page that looks harmless quietly sends two hundred queries. The good news is that a handful of patterns cause nearly all Doctrine performance problems, and each has a standard cure.

In this lesson you will learn to recognize the N+1 query problem, fix it with fetch joins, understand what a lazy proxy is, process large tables in batches without running out of memory, choose indexes, and know where caching fits. Doctrine cannot run on this site, so the real code is read-only. The runnable examples use PDO and SQLite with a query counter, which reproduces the same effects.

Measure first

Before tuning, find out what actually happens. In the dev environment, the Symfony web profiler toolbar shows how many database queries a page made and how long they took, and the Doctrine panel lists every query with its parameters. A page with a number that grows with the amount of data (20 rows, 21 queries; 100 rows, 101 queries) has the N+1 problem. Fixing it usually matters more than any micro-optimization.

The N+1 problem

Suppose a page lists posts with the number of comments of each one:

TWIG
{% for post in posts %}
    <li>{{ post.title }} ({{ post.comments|length }} comments)</li>
{% endfor %}

and the controller loaded the posts with $postRepository->findAll(). Doctrine runs one query for the posts. Then every post.comments is a lazy collection: the first access runs a query for that post's comments. For 50 posts that is 1 + 50 = 51 queries. This is the "N+1": one query for the list, plus N for the children.

sequenceDiagram
    participant T as Twig loop
    participant D as Doctrine
    participant DB as Database
    T->>D: findAll() posts
    D->>DB: SELECT * FROM post
    DB-->>D: 3 rows
    loop for each post
        T->>D: post.comments
        D->>DB: SELECT * FROM comment WHERE post_id = ?
        DB-->>D: rows
    end

One query for the list, then one more for every row of the list: 1 + N queries.

Each query is fast, so the problem hides in development with five rows and appears in production with five thousand. The next stand-in counts queries through a small wrapper around PDO, so you can see the effect without a framework:

PHP Counting the N+1 queries (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Fix it with a fetch join

The cure is to ask for the comments in the same query as the posts. In DQL or the QueryBuilder this is a fetch join: you join the relation and also select the joined alias, so Doctrine fills the collections from the one result set.

PHP
public function findWithComments(): array
{
    return $this->createQueryBuilder('p')
        ->leftJoin('p.comments', 'c')
        ->addSelect('c')
        ->orderBy('p.id', 'DESC')
        ->getQuery()
        ->getResult();
}

The addSelect('c') is the part people forget. With only leftJoin, the join can filter and order, but nothing is loaded into the collections, and lazy loading starts again. Now post.comments is already filled and the loop makes no extra queries.

Fetch joins have limits you should know:

  • Joining several collections in one query multiplies rows (a post with 10 comments and 10 tags returns 100 rows), so join at most one collection per query, or run a second query for the other.
  • Fetch-joining a collection together with pagination is tricky, because LIMIT would cut rows, not posts. Doctrine's Paginator handles this with extra queries, as described in Repositories and Queries.
  • If you only need a count or a total, select it directly (COUNT(c.id) with GROUP BY) instead of loading every comment object.

You can also make a relation load differently by default with the fetch option (fetch: 'EXTRA_LAZY' lets count() on a collection run a COUNT query without loading it), but a deliberate query in a repository method is clearer than a hidden global setting. Avoid fetch: 'EAGER' unless you are sure: it loads the relation every time the entity is loaded, wanted or not.

Lazy proxies

When Doctrine loads a Comment, its post property does not contain a fully loaded Post. It contains a proxy: an object that looks like a Post, knows its id, and loads the other fields from the database the first time you read one. This is what makes $comment->getPost()->getId() free, while $comment->getPost()->getTitle() triggers a query.

You can create such a proxy yourself with $entityManager->getReference(Post::class, 5). It returns an object for post 5 without querying, which is useful when you only need to link something to an existing row:

PHP
$comment = new Comment();
$comment->setBody('First!');
$comment->setPost($entityManager->getReference(Post::class, $postId));
$entityManager->persist($comment);
$entityManager->flush();

Only the foreign key is written, and the post row is never read. Proxies are an implementation detail you rarely touch, but they explain why a query appears at a surprising line of code. The stand-in below builds a proxy by hand:

PHP A lazy proxy that loads on first use (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Batch processing

Loading a million rows into objects will exhaust PHP's memory, because the entity manager keeps a reference to every managed entity (that is how it detects changes). For imports, exports and big updates, work in chunks and clear the manager between chunks:

PHP
$batchSize = 200;
$i = 0;

foreach ($rows as $row) {
    $entityManager->persist(new Product($row['name'], $row['price']));

    if ((++$i % $batchSize) === 0) {
        $entityManager->flush();
        $entityManager->clear();   // forget every managed entity
    }
}

$entityManager->flush();           // the last, partial batch
$entityManager->clear();

clear() detaches everything, so objects you loaded before the call must not be used afterwards. For reading, use $query->toIterable() instead of getResult() so rows are hydrated one at a time, and clear regularly there too. For a plain "set all rows of this kind to that value", a single DQL UPDATE or DELETE statement runs entirely in the database and is far faster than loading objects. The trade-off is that it bypasses lifecycle events and any entities already loaded in memory are not updated.

Wrapping each batch in a transaction (wrapInTransaction) also speeds up writes, because the database commits once per batch instead of once per row. The next stand-in imports 1,000 rows one at a time and in batches inside a transaction. It prints the number of commits instead of a time, so the output is the same on every run:

PHP Batch inserts and commits (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The last lines of the example show the effect of an index on how the database finds a row.

Indexes

An index is a sorted structure that lets the database find rows without reading the whole table. Without one on name, the query above has to scan every row ("SCAN"); with one, it jumps straight to the match ("SEARCH ... USING COVERING INDEX" in the output above). Foreign key columns are normally indexed for you, but columns you filter or sort by are your decision. In Doctrine you declare them on the entity:

PHP
#[ORM\Entity(repositoryClass: PostRepository::class)]
#[ORM\Table(name: 'post')]
#[ORM\Index(name: 'idx_post_status_published', columns: ['status', 'published_at'])]
class Post
{
    #[ORM\Column(length: 20)]
    private string $status = 'draft';

    #[ORM\Column(nullable: true)]
    private ?\DateTimeImmutable $publishedAt = null;
}

Then create a migration with make:migration (see Migrations). A few guidelines:

  • Index columns that appear in WHERE, JOIN and ORDER BY of your frequent queries, and verify with EXPLAIN that the database uses them.
  • In a composite index, the order of columns matters: an index on (status, published_at) helps a filter on status, or on both, but not a filter on published_at alone.
  • Every index slows down writes and uses disk, so do not index everything.
  • Use #[ORM\UniqueConstraint] (or unique: true on the column) when a value must be unique. It protects your data and makes lookups fast.

Caching

If a query is correct, indexed and still too slow or too frequent, caching is the next tool. Doctrine has a result cache ($query->enableResultCache(300)), which stores the result of one query in a cache pool, and an optional second-level cache (#[ORM\Cache] on an entity, enabled in the bundle config), which stores entity data by id so that loading by primary key can skip the database. Both add the problem of stale data: you must decide when cached data may be out of date. Reach for them last, after queries and indexes, and measure the gain. For general-purpose caching in your own code, see The Cache Component.

Other pitfalls

  • Hydrating too much: if a list needs only a title and an id, select only those fields (->select('p.id', 'p.title')) or use a DTO via SELECT NEW, instead of building full entities.
  • Flushing in a loop: call flush() once after many changes, not after each one (except when batching on purpose).
  • Loading to count: use count() or COUNT(...), not count($repository->findAll()).

The next lesson shows how to fill a development database with realistic data, so you can spot these problems early.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A page lists 40 posts and shows each post's comment count through a lazy collection. How many queries does it make?

    One query loads the posts, then each post's collection runs its own query: 1 + 40 = 41. This is the N+1 problem.
  2. 2. What is easy to forget when writing a fetch join in the QueryBuilder?

    leftJoin alone only joins for filtering and sorting. addSelect('c') makes Doctrine hydrate the joined rows into the collection.
  3. 3. Why call clear() between batches in an import?

    The entity manager holds a reference to every managed entity. clear() detaches them so PHP can free the memory.
  4. 4. You have an index on (status, published_at). Which filter can it help?

    A composite index is usable from its leftmost column onward. A filter only on the second column cannot use it effectively.
  5. 5. What does getReference(Post::class, 5) do?

    It gives you a lazy object that knows its id, so you can link to the row without reading it.

Summary

  • Measure with the profiler; a query count that grows with the data means N+1.
  • Cure N+1 with a fetch join (leftJoin plus addSelect), a direct aggregate, or a second targeted query.
  • Relations are lazy proxies or collections that query on first real access; getReference() links without loading.
  • Process big tables in batches with flush() and clear(), toIterable() for reads and DQL UPDATE/DELETE for bulk changes.
  • Index the columns you filter and sort by, mind the order in composite indexes, and cache only after fixing queries.

Next up: Fixtures and Factories.