Part 5 — Building it properly › Chapter 38

The ORM boundary

What Hibernate, EF and Eloquent actually generate, N+1 seen from the database side, and when to drop to SQL.

No advert asked for this

Most SQL you will be handed to fix was generated by an ORM. Reading it back is a distinct skill.

Why this is your problem

🧒 Simple

Somebody else's machine wrote the letter. It is your job to read it back and say why the postman is taking so long — even though you did not choose a single word of it.

🎓 Professional

Most SQL you will be handed to fix was generated by Hibernate, Entity Framework, Eloquent or Doctrine. Nobody wrote it; it came out of an object graph. So reading generated SQL back to the code that produced it is a distinct skill from writing SQL, and it is the one the person asking for help does not have. It is also why the database developer is often the most useful person in a performance conversation about an application they did not build.

N+1, from the database side

Why N plus one never appears in a slow query report Loading a list and then touching a related entity inside the loop issues one query for the list and one more for every row. Each of those queries is fast, so none of them crosses a slow query threshold and the average duration looks excellent, while the page waits for all of them in turn. ONE QUERY WITH A JOIN orders JOIN customers one round trip total: one query THE SAME PAGE, LAZY-LOADED SELECT … FROM orders the list: 2 ms SELECT … customer 1 SELECT … customer 2 SELECT … customer 3 … once per row Every one of them takes about two milliseconds, so not one crosses a slow-query threshold. Sort by CALL COUNT to see it.
The defect is invisible to every per-query report and obvious the moment you sort by how often a query ran.
N+1, from both sides of the boundary: four innocent lines, and what the database receives.
// The code. It looks completely innocent.
var orders = db.Orders.Where(o => o.CustomerId == 42).ToList();  // ToList() runs the query
foreach (var o in orders)                     // …and now we iterate the results…
    Console.WriteLine(o.Lines.Count);         // ← lazy load: a QUERY, once per order

-- What the database actually sees:
SELECT * FROM orders WHERE customer_id = 42;                 -- 1: the one you intended
SELECT * FROM order_lines WHERE order_id = 1;                -- 2: the one you did not
SELECT * FROM order_lines WHERE order_id = 2;                -- 3
...                                                          -- N+1
Where the query is hiding
  • o.Lines.Count is a property access in C# and a network round trip in reality. Nothing in the syntax marks the boundary, which is precisely why this bug is so common.
  • ToList() is the moment the expression tree becomes SQL. Everything before it is composed on the server; everything after it happens in memory, over data you have already paid to fetch.
  • Each of these queries is fast. Five hundred of them is half a second of latency, and no per-query report will ever flag it — which is why you sort by call count (chapter 42).
  • The fix is to say what you want up front: .Include(o => o.Lines), a fetch join, or selectinload. Then read the next example, because the obvious fix has its own failure mode.
⚠ Why you will not find it in a slow-query report

Every one of those queries is fast — a single-row index seek. Nothing exceeds a slow-query threshold, and sorting by average duration puts them at the bottom. They only appear when you sort pg_stat_statements or sys.dm_exec_query_stats by total time and notice one statement with a call count in the millions. That is the diagnostic: a high call count on a trivial query.

✔ What to tell the application developer

Not “your ORM is bad” — a fix they can apply: eager-load the collection. .Include(o => o.Lines) in EF Core, JOIN FETCH in JPQL, ->with('lines') in Eloquent, selectinload() in SQLAlchemy. That turns N+1 into one or two queries. Naming the exact method for their stack is the difference between being consulted again and not.

The other four ORM shapes you will meet

1 · The Cartesian explosion from over-eager loading

The fix for N+1, and the fan-out it causes when applied twice.
-- .Include(o => o.Lines).Include(o => o.Payments)
-- becomes ONE query joining BOTH collections: 2 lines × 3 payments = 6 rows per
-- order, with every order column repeated six times. That is the fan-out of
-- chapter 08, paid for over the wire instead of in a SUM.
-- EF Core calls the fix "split queries" (AsSplitQuery); Hibernate needs two fetches.
Why eager loading is not simply “the answer”
  • One .Include() is usually right: one query, one join, one round trip. Two sibling collections multiply, and the multiplication is in bytes on the network, not just in rows.
  • The ORM then de-duplicates in memory and hands you the object graph you expected — so the result is correct and the cost is invisible from the application side.
  • AsSplitQuery() issues one query per collection: three round trips instead of one enormous result. Which is faster depends on latency and on the fan-out factor, and measuring is the only honest answer.

2 · SELECT of every column, always

An ORM materialises entities, so it selects every mapped column whether or not you use them — which is SELECT * with all of its consequences, including defeating a covering index. The fix is projection: select into a DTO (.Select(o => new { o.Id, o.Total })) so only those columns are requested.

3 · Client-side evaluation

Client-side evaluation: a filter the ORM could not translate, applied after fetching everything.
// A filter the ORM cannot translate is sometimes applied IN MEMORY —
// after fetching the whole table. EF Core 3+ throws instead, which is a mercy.
.Where(o => MyHelper.IsInteresting(o))     // a C# method: there is no SQL for it
How to spot it before production does
  • The tell is a query that is fast on a developer's 200-row table and unusable on a million-row one, with no plan problem to find — because the WHERE never reached the database.
  • Only expressions the provider can translate become SQL. A call into your own code cannot be, so the provider either gives up loudly (EF Core 3+) or quietly downloads the table (EF Core 2, and several other ORMs).
  • The fix is to express the rule in terms the provider understands — comparisons on mapped columns — or to move it into the database as a view or a computed column.

4 · Parameter type mismatch

The classic .NET/SQL Server incident: the ORM binds a string parameter as NVARCHAR against a VARCHAR column, forcing an implicit conversion of the column and losing the index seek — on a query that is otherwise perfect. The plan shows a convert warning; the code shows nothing at all. See chapter 07.

Seeing what it really sends

Seeing what the ORM really sends: from the application first, from the database when you must.
-- The application side (ask for this first — it is the fastest route):
--   EF Core:     LogTo(Console.WriteLine) with EnableSensitiveDataLogging
--   Hibernate:   show_sql, or the org.hibernate.SQL logger
--   Eloquent:    DB::listen(...)
--   SQLAlchemy:  echo=True

-- The database side, when nobody can turn application logging on:
SELECT calls, total_exec_time, mean_exec_time, rows,   -- how often, how long, how many rows
       left(query, 150)                                -- and the statement, truncated
FROM pg_stat_statements                                -- one row per normalised statement
ORDER BY calls DESC LIMIT 20;                          -- ← sort by CALLS: that is the N+1 signal

SELECT TOP 20 qs.execution_count,                      -- the same idea in SQL Server
       qs.total_worker_time/1000 AS cpu_ms,            -- microseconds → milliseconds
       SUBSTRING(t.text, 1, 150) AS sql                -- the statement text
FROM sys.dm_exec_query_stats qs                        -- one row per cached plan
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t      -- resolve the handle to text
ORDER BY qs.execution_count DESC;                      -- most-executed first
Why sort by call count here, and by total time elsewhere
  • For performance triage you sort by total time (chapter 28). For finding an N+1 you sort by execution count: the signature is a trivially cheap statement executed tens of thousands of times.
  • A parameterised statement appears once in these views with a high count. A statement built by string concatenation appears thousands of times with a count of one each — which tells you something else useful about the application.
  • Ask for the application log first anyway. It shows the call site; the database only shows the symptom.

When to drop to SQL

🎓 The honest boundary

An ORM earns its place on the write side: change tracking, unit of work, identity map and relationship graphs are genuinely hard to hand-write and genuinely useful. It earns much less on the read side, where you want a specific shape rather than an object graph. So the common, sensible architecture is:

  • Writes through the ORM. Loading an aggregate, changing it and saving it is what it is for.
  • Reads that are reports, exports or dashboards: hand-written SQL into a flat DTO. Faster, clearer, and reviewable — you can read it and know exactly what it costs.
This is CQRS, without the ceremony

“Different model for reading than for writing” is the whole idea, and it does not require event sourcing, a message bus, or two databases. Saying it plainly — “I would keep writes going through the ORM and write the reports as SQL” — is a strong, unpretentious answer to a question that often attracts an architecture lecture.

✔ And do not be the person who says "ORMs are bad"

You will be interviewed by people whose codebase is an ORM. The useful position is: they solve a real problem well, they hide costs that only become visible from the database side, and part of your value is making those costs visible early — with the fix expressed in their framework's vocabulary, not as a suggestion to remove it.

  1. Most SQL you are asked to fix was generated. Reading it back to the code is a distinct skill.
  2. N+1 shows up as a huge call count on a trivial query, never as a slow query.
  3. The fix is eager loading, named in the developer's own framework.
  4. Two eager-loaded collections in one query is a Cartesian explosion; split the queries.
  5. An ORM selects every mapped column. Project into a DTO when it matters.
  6. Untranslatable predicates get evaluated client-side, after fetching everything.
  7. Parameter type mismatch silently defeats an index and looks like nothing at all.
  8. Sort by call count for N+1; sort by total time for everything else.
  9. ORM for writes, hand-written SQL for reports. That is CQRS without the ceremony.

Interview questions this chapter answers

What is the N+1 problem and how do you spot it?

One query for a collection, then one more per item because of lazy loading. From the database it looks like a trivial query with a call count in the millions — so you find it by sorting query statistics by call count or total time, never by average duration. The fix is eager loading, and I would name the specific method for their framework.

Would you use an ORM?

For writes, yes — change tracking and unit of work are genuinely valuable and tedious to hand-write. For reports and exports I would write SQL and project into a flat type, because there I want a specific result shape rather than an object graph, and I want to be able to read what it costs.

The developers say the ORM is slow. What do you do?

Ask them to turn on SQL logging, or look at query statistics sorted by call count and total time. It is usually one of four things: N+1, a Cartesian explosion from two eager loads, selecting every column and defeating a covering index, or a parameter bound as the wrong type. All four have a fix inside the ORM, which is what I would hand back.