Hands-on: finding N+1 queries, over-fetching and an overloaded database in a client's code

In one of Microsoft’s sample diagnostics, the method GetProductsInSubCategoryAsync ran 45 SELECT statements per call, each opening a new SQL connection. The results were still correct, so functional tests were unlikely to catch the problem. But with 1,000 concurrent users, latency climbed to nearly a minute.

Imagine you are an FDE brought in to run an agent on a client’s existing system. If that system has endpoints like this one, your agent will be slow too, and the client will conclude that your product is slow. Knowing how to find the bottlenecks in a client’s code is a skill worth practising now.

The three antipatterns below should be checked in a single pass: chatty I/O, extraneous fetching and busy database. The reason is that fixing one can easily push the system into another. The code examples use C# with Entity Framework and are simplified for readability, but the ideas apply to any ORM.

What do you need before opening the code?

You need three things: read access to the source code, a tracing or APM tool that shows the SQL the ORM actually generates, and per-request metrics rather than only aggregates. If the client has not enabled tracing, ask them to turn it on first. Reading code without traces is guesswork.

Step 1: Read the slowest traces, not the averages

Microsoft’s guidance makes a point that sounds obvious: if you look only at averages, you can miss problems that get much worse as load increases. So sort traces by processing time in descending order and open a few requests at the top of the list.

For each trace, answer one question: how many statements does this request send to the same data store? The sign of chatty I/O is a single application instance sending many small requests to the same data store.

Every I/O call carries its own overhead, and as those costs add up, the whole system slows down.

Check: you have recorded the query count for each slow endpoint. If a list screen needs 45 queries, that is where to dig.

Step 2: Find the N+1 the ORM is hiding

A familiar culprit is N+1. The AppSignal blog defines it as a query being run again for each result of a previous query. An ORM can hide this because it quietly fetches child records one at a time, so all you see in the code is a harmless-looking foreach loop:

// Illustration, simplified: each access to Items may trigger an extra query
var orders = context.Orders.ToList();      // 1 query
foreach (var o in orders)
{
    Render(o.Items);                        // +1 query per order
}

With 44 orders, this code runs 45 queries. The Ebean ORM documentation describes exactly this: loading an object graph takes N + 1 SQL statements. If the query count from step 1 grows with the number of rows, you are almost certainly dealing with N+1.

Check: the trace contains many identical SELECT statements that differ only in the foreign key value.

Step 3: Consolidate with eager loading

The standard fix is eager loading: the query count stays constant as the number of rows grows. In Entity Framework, use Include to fetch the child table in the same query:

// Illustration: orders and items are fetched in a single query
var orders = context.Orders
    .Include(o => o.Items)
    .ToList();

Some ORMs offer alternatives out of the box. Ebean, for example, supports batched lazy loading and lets you specify in advance which relationships to preload. Before rewriting queries by hand, check what the client’s ORM already supports.

Measure again after the fix, because the numbers can change dramatically. In Microsoft’s example, after consolidating into a single query, latency at 1,000 users fell from nearly a minute to 5-6 seconds, and throughput rose from 410 to 3,970 requests per minute.

Check: rerun the same load scenario and compare queries per request. That number should stay the same as you increase the number of orders in the test data.

Worked example: from a single trace to a before-and-after table

Suppose a client complains that the “Recent orders” screen is slow at peak times. You open the slowest request and count 201 SELECT statements: one fetching 200 orders, and 200 fetching items, each differing only in OrderId.

You open another request, for a customer with only 20 orders, and count 21 statements. The query count rises exactly with the number of rows, so the diagnosis is almost certainly N+1, not a weak database.

After adding Include and rerunning the same load scenario, both requests drop to a single query. You record a table with the endpoint, queries per request and the latency of the slowest request, before and after. The latency column must contain real measurements from the client’s system, not estimates.

Step 4: Don’t overcorrect into extraneous fetching

This is the moment to slow down. Extraneous fetching, retrieving more data than needed, often comes directly from fixing chatty I/O too aggressively. Once queries have been consolidated, it is tempting to fetch everything just to be safe.

In code review, two warning signs can be found with a simple command:

grep -rn "AsEnumerable" src/
grep -rni "select \*" src/

Every call to AsEnumerable hints at a problem: every operation after it runs on the client, after all the data has been pulled across. SELECT * statements and entity fetches without a filter belong to the same group:

// Illustration: pull all of Sales into the app, then filter and sum
var total = context.Sales.AsEnumerable()
    .Where(s => s.Year == 2024)
    .Sum(s => s.Amount);

Removing AsEnumerable lets the filter and the sum be translated into SQL and run in the database. To confirm this with data rather than instinct, track the ratio between the volume of data the database returns and the volume the application returns to the client. If the two figures differ widely, the application is over-fetching.

The gains can be large. In Microsoft’s sample, moving an aggregation from the client into the database cut data per transaction from more than 280 KB to 53 bytes. Maximum sustained throughput rose from about 2,000 to more than 25,000 requests per minute.

Step 5: When the database is busy running code instead of returning data

The third fault runs in the opposite direction. A busy database occurs when stored procedures and triggers make the database server spend most of its time running code rather than storing and returning data. The usual cause is treating the database as a service for formatting data, manipulating strings or running business logic, rather than purely as storage.

You can spot this by comparing two metrics: database processing load and data throughput. If the database is doing a lot of processing while very little data leaves it, read the source code to see whether that processing belongs in another tier.

In the same set of examples, a query that built XML inside the database quickly drove CPU/DTU to 100%. The code below is a simplified sketch of this kind of fix, not the original code:

-- Before (illustration): the database builds the XML string for every row
SELECT '<Order id="' + CAST(Id AS varchar(20)) + '">'
     + '<Total>' + CAST(Total AS varchar(20)) + '</Total></Order>'
FROM Orders WHERE CustomerId = @customerId;

// After (illustration): the database returns only raw columns, the application handles formatting
var rows = context.Orders
    .Where(o => o.CustomerId == customerId)
    .Select(o => new { o.Id, o.Total })
    .ToList();
var xml = BuildOrderXml(rows);   // formatting function runs in the application layer

After moving the formatting into the application, throughput rose from 12 to more than 400 requests per second.

Steps 4 and 5 pull in opposite directions: step 4 pushes work into the database, step 5 pulls work out of it. Many database systems are optimised for tasks such as computing aggregates over large datasets, so do not move that work out.

So move formatting, string concatenation and business logic into the application, and keep SUM, COUNT and GROUP BY in the database.

Common mistakes on a client site

The first mistake is fixing before measuring. Without query counts and throughput figures from before the fix, you cannot prove anything to the client. Nor can you tell whether you fixed the right thing or merely pushed the system into another antipattern.

The second is testing only with small datasets. N+1 with 3 rows is almost invisible; it only shows up with thousands of rows.

The third is about working with the client more than technique. The client’s stored procedures may have been written by another team, and that team had its reasons. Bring data, for example “database CPU is at 100% but it returns only a few bytes”, and propose moving the formatting out rather than scrapping all the stored procedures.

How does this skill show up on a CV and in interviews?

When reading FDE job descriptions, do not just look for the words “N+1”. Watch for requirements around debugging production systems or resolving performance issues on client systems, because the skills in this guide belong to exactly that group.

On your CV, describe each fix using the same template: what you saw in the trace, what the cause was, how you fixed it, and the before-and-after metrics. The metric might be queries per request or bytes per transaction.

If you have no real project yet, build a small API with a parent-child relationship. Deliberately write an N+1, measure it, fix it with eager loading, then deliberately over-fetch with AsEnumerable and measure again. A repo with all three measurements is far more convincing than a line saying “experienced in performance optimisation”.

On a client site, few people remember how many lines of code you rewrote. They remember that an endpoint which used to take nearly a minute now takes a few seconds, and that you have the traces to prove it.