DEV Community

Raz Galstyan
Raz Galstyan

Posted on

JOIN FETCH can be slower than N+1: a reproducible Doctrine benchmark (+ 1-row-per-entity JSON aggregation)

#performance #database #architecture #php

If you’ve ever built a “product listing” in Symfony/Doctrine with multiple relations (category, brand, images, reviews, counts…), you’ve probably hit one of these:

  • classic N+1
  • “EAGER” collections still triggering extra queries
  • heavy entity hydration cost
  • or JOIN FETCH that looks like “2 queries” but becomes slower in practice

This post is about read-heavy, DTO-style endpoints (arrays / API responses), where you want:

  • predictable query count
  • predictable runtime
  • stable scaling when multiple OneToMany relations are involved

It’s not a Doctrine replacement — it’s an explicit read-model escape hatch.

The real problem: multiple OneToMany JOINs explode rows

JOINing multiple OneToMany relations multiplies the SQL result set (cartesian product).

Example:

  • 3 images × 5 reviews = 15 rows per product
  • for 2000 products => ~30,000 rows transferred from DB and processed

Doctrine’s identity map hides duplicates in PHP, but the DB still returns the multiplied rowset.
That can kill performance even when query count is low.

Links (package + reproducible benchmark)

If you only want to run the benchmark, go to the benchmark repo.

If you want to use the package in your project, go to the bundle repo.

Reproducible benchmark (Symfony + PostgreSQL)

I published a standalone Symfony benchmark project with fixtures and a CLI command so anyone can reproduce the results locally:

https://github.com/rgalstyan/doctrine-aggregated-queries-benchmark

Sample run (limit=2000)

Note: timings depend on your machine/DB/cache state. The trend is what matters.


txt
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
PRODUCTS PERFORMANCE TEST
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Dataset size: 2000 products

TRADITIONAL DOCTRINE (2000 records)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Time:    327.73ms
Memory:  44559.6 KB (43.52 MB)
Queries: 43
Result:  2000 Product entities

DOCTRINE JOIN FETCH (entities) (2000 records)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Time:    816.37ms
Memory:  39795.9 KB (38.86 MB)
Queries: 2
DB rows: ~30000 (Cartesian product in SQL)
Result:  2000 Product entities

SIMPLE JOINS (naive) (2000 records)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Time:    103.14ms
Memory:  11659.4 KB (11.39 MB)
Queries: 1
DB rows: 30000 (Cartesian product!)
Result:  2000 products (after deduplication)

AGGREGATED QUERIES (2000 records)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Time:    66.18ms
Memory:  13989.1 KB (13.66 MB)
Queries: 1
DB rows: 2000
Result:  2000 products (arrays)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
COMPARISON
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Approach                   | Return   |  Time (ms) |     Mem (KB) | Queries |    DB rows | Products
───────────────────────────────────────────────────────────────────────────────────────────────────
1) Traditional Doctrine    | entities |     327.73 |      44559.6 |      43 |        N/A |     2000
2) Doctrine JOIN fetch     | entities |     816.37 |      39795.9 |       2 |      30000 |     2000
3) Simple JOINs (naive)    | arrays   |     103.14 |      11659.4 |       1 |      30000 |     2000
4) JSON aggregation        | arrays   |      66.18 |      13989.1 |       1 |       2000 |     2000
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Enter fullscreen mode Exit fullscreen mode

Top comments (1)

Collapse
 
xwero profile image
david duymelinck • • Edited

I love it that you added a benchmark.

I looked at the simple joins query and it is indeed naive. By just one fix you could shave quite some time from the query. And that is by moving the subquery for the ids from where to a derived table and join that table with the products table.

FROM (
        SELECT id
        FROM products
        ORDER BY id ASC
        LIMIT :limit
     ) AS p_ids
JOIN products p               ON p.id = p_ids.id
Enter fullscreen mode Exit fullscreen mode

The typical speed gain with that change is 30% so that means the time will be in the low 70 ms.
So getting more rows is only impacting the speed a little, while using less memory.