DEV Community: Raz Galstyan

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

Raz Galstyan — Thu, 18 Dec 2025 05:54:04 +0000

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)

Symfony Aggregated Queries (the bundle):

https://github.com/rgalstyan/symfony-aggregated-queries
Reproducible benchmark project (Symfony app that uses the bundle):

https://github.com/rgalstyan/doctrine-aggregated-queries-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
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

I Made My Laravel API 83% Faster by Rethinking Database Queries

Raz Galstyan — Mon, 15 Dec 2025 06:36:45 +0000

How I solved the N+1 query problem using JSON aggregation instead of traditional eager loading
tags: laravel, php, performance, database.

Last month, I was debugging a slow admin dashboard. The page loaded 500 partner records with their profiles, countries, and promotional codes. Each page load took over 2 seconds.

The culprit? The classic N+1 query problem.

The Problem Everyone Knows

Even with Laravel's eager loading, I was still hitting the database 5 times per request:

$partners = Partner::with(['profile', 'country', 'promocodes'])->get();

This generates:

SELECT * FROM partners                        -- Query 1
SELECT * FROM profiles WHERE partner_id IN... -- Query 2  
SELECT * FROM countries WHERE id IN...        -- Query 3
SELECT * FROM promocodes WHERE partner_id IN...-- Query 4

Each query means another round-trip to the database. With 50 records, that's 4 network round-trips, adding 15-20ms of latency each.

The "Aha!" Moment

I asked myself: "Can we load everything in ONE query?"

That's when I remembered MySQL's JSON_OBJECT and JSON_ARRAYAGG functions. What if instead of multiple queries, we could aggregate all relations into JSON directly in SQL?

The Solution: JSON Aggregation

I built a Laravel package that does exactly this:

$partners = Partner::aggregatedQuery()
    ->withJsonRelation('profile')
    ->withJsonRelation('country')
    ->withJsonCollection('promocodes')
    ->get();

This generates a single optimized query:

SELECT 
    base.*,
    JSON_OBJECT('id', profile.id, 'name', profile.name) AS profile,
    JSON_OBJECT('id', country.id, 'name', country.name) AS country,
    (SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'code', code))
     FROM promocodes WHERE partner_id = base.id) AS promocodes
FROM partners base
LEFT JOIN profiles profile ON profile.partner_id = base.id
LEFT JOIN countries country ON country.id = base.country_id

One query. All the data.

The Results

I ran benchmarks on a dataset of 2,000 partners with 4 relations each, fetching 50 records:

Method	Time	Memory	Queries
Standard Eloquent	27.44ms	2.06MB	4
JSON Aggregation	4.41ms	0.18MB	1
Improvement	83% faster	91% less	75% fewer

That's not a typo. 83% faster.

Why Is It So Fast?

The performance gain comes from three factors:

1. Network Latency (80% of the gain)

Database round-trips are expensive. Even on localhost, each query adds 5-10ms. On a remote database? 15-20ms each.

Before: 4 queries × 15ms = 60ms in network time

After: 1 query × 15ms = 15ms

2. Avoiding Eloquent Hydration (15% of the gain)

By returning arrays instead of Eloquent models, we skip:

Object instantiation
Attribute casting
Relationship binding
Event firing

3. Optimized SQL (5% of the gain)

The database does the aggregation work using highly optimized C code instead of PHP loops.

Real-World Impact

On my dashboard handling 10,000 API requests per day:

40,000 fewer database queries
4 minutes saved in total response time
19GB less memory usage
Better server resource utilization

How It Works

Installation

composer require rgalstyan/laravel-aggregated-queries

Setup

Add the trait to your model:

use Rgalstyan\LaravelAggregatedQueries\HasAggregatedQueries;

class Partner extends Model
{
    use HasAggregatedQueries;

    public function profile() {
        return $this->hasOne(PartnerProfile::class);
    }

    public function promocodes() {
        return $this->hasMany(PartnerPromocode::class);
    }
}

Usage

// Instead of this:
$partners = Partner::with(['profile', 'promocodes'])->get();

// Do this:
$partners = Partner::aggregatedQuery()
    ->withJsonRelation('profile', ['id', 'name', 'email'])
    ->withJsonCollection('promocodes', ['id', 'code', 'discount'])
    ->where('is_active', true)
    ->get();

Output

The data structure is predictable and clean:

[
    'id' => 1,
    'name' => 'Partner A',
    'profile' => [
        'id' => 10,
        'name' => 'John',
        'email' => 'john@example.com'
    ],
    'promocodes' => [
        ['id' => 1, 'code' => 'SAVE10'],
        ['id' => 2, 'code' => 'SAVE20']
    ]
]

Guaranteed:

Relations are array or null (never undefined)
Collections are always array (never null)
No surprises in your code

When Should You Use This?

✅ Perfect for:

API endpoints with multiple relations
Admin dashboards with complex queries
Mobile backends where every millisecond counts
Read-heavy applications (90%+ reads)
High-traffic services needing optimization

⚠️ Not recommended for:

Write operations (use standard Eloquent)
When you need model events/observers
Complex nested relations (coming in v1.1!)

Performance vs. Eloquent Models

The package offers two modes:

// Array mode (default, fastest - 83% faster)
$partners = Partner::aggregatedQuery()->get();

// Eloquent mode (still faster - 27% improvement)
$partners = Partner::aggregatedQuery()->get('eloquent');

Array mode is fastest because it skips Eloquent's hydration overhead. But even in Eloquent mode, you still save 1 database query, which gives a significant boost.

Trade-offs

Let's be honest about limitations:

What you lose:

Model events (no created, updated, deleted events)
Accessors/mutators (in array mode)
The ability to call save() or update() (read-only)

What you gain:

83% faster response times
91% less memory usage
Simpler, more predictable data structures
Better scalability

For read-heavy operations like APIs and dashboards, this trade-off is absolutely worth it.

What's Next?

I'm currently working on v1.1.0 with:

Nested relations (profile.company.country)
Conditional loading with query constraints
Relation aliasing
Enhanced debugging tools

Try It Out!

If you're building APIs or dashboards with Laravel, give it a try:

🔗 GitHub: rgalstyan/laravel-aggregated-queries

📦 Packagist: composer require rgalstyan/laravel-aggregated-queries

I'd love to hear your results! Have you tried optimizing N+1 queries in other ways? Drop a comment below.

P.S. The package has been featured in Laravel News! If you find it useful, a GitHub star would mean a lot ⭐