From 2 Minutes to Sub-200ms: How a Single Implicit ORM Query Time-Outed Our Production API

It started with a warning ping from our production monitoring bot:

⚠️ Slow API Response Detected
GET /api/vendors/store/reviews
Duration: 38,201 ms

Within hours, that latency crept up to 66 seconds, then 126 seconds, before finally collapsing into a string of HTTP 500 server timeouts.

The feature itself was incredibly simple: load a paginated list of reviews for a store, showing the user’s comment, their star rating, and a tiny order summary.

So, how did a simple review card end up taking over two minutes to load?

The answer lies in a common trap that developers fall into when using ORMs like Entity Framework: unintentional query creep.

The Culprit: The "Simple" Query That Wasn't
When we dug into the service layer, the query looked something like this under the hood:

var reviews = await _context.CustomerReviews
    .Include(r => r.User)
    .Include(r => r.Order)
    .Skip(offset)
    .Take(pageSize)
    .ToListAsync();

On paper, this looks normal. We are grabbing the reviews, including the user who wrote it, and including the order they placed.

But here is where it went off the rails. The review card displayed a summary of the order. To build this summary, the code called a shared mapping class: OrderMapping.OrderResource().
This mapping was a massive, 230-line projection that was originally designed for the checkout detail screen. It joined almost every table in our schema:

• Order items
• Product option groups and individual options
• Recipient delivery addresses
• Delivery ranges and pricing calculations
• Rider information, vehicle details, and active statuses
• Tracking timelines

Because the ORM was instructed to load these relationships, it executed a second, massive round-trip database query for every single order attached to every review on the page.

In production, under load, this created a massive SQL statement with over 10 table joins executing repeatedly. As our review count grew, the database simply choked.

The Fix: Slimming down the payload
The solution wasn't to write complex SQL, but to apply clean query principles:

1. Ditch the Generic Projections Instead of reusing the heavy checkout mapping, we created a slim, inline projection that only selected the 5 fields the review card actually needed:

var orderSummaries = await _context.Orders
    .Where(o => orderIds.Contains(o.Id))
    .Select(o => new {
        o.Id,
        o.OrderCode,
        Status = o.Status.ToString(),
        o.TotalAmount,
        Items = o.OrderItems.Select(oi => new { oi.ProductName, oi.Quantity })
    })
    .ToListAsync();

By selecting only the required columns, we eliminated the expensive joins on addresses, riders, and options.

2. Run Aggregate Queries in Parallel
The endpoint was also running a heavy GroupBy on the reviews table to calculate positive vs. negative counts. We split these into two fast scalar queries and ran them in parallel:

var positiveTask = baseQuery.CountAsync(r => r.Rating >= 3);
var negativeTask = baseQuery.CountAsync(r => r.Rating < 3);

await Task.WhenAll(positiveTask, negativeTask);

3. Add Cache with a Short TTL
Once the database query was fast, we added a short 2-minute Redis cache key using the pagination and filter parameters:

var cacheKey = $"vendor_reviews:{vendorId}:page:{pageNumber}";
var cached = await _cache.GetAsync<ReviewResponse>(cacheKey);
if (cached != null) return cached;

4 Takeaways for Junior & Mid-Level Devs
If you are building APIs that need to scale, keep these rules in mind:

1. Always Inspect Your Generated SQL
Don't trust your ORM blindly. Use diagnostic tools or query logging in your development environment to see the actual SQL hitting the database. If you see dozens of joins for a simple card, something is wrong.

2. Project Directly to DTOs
Avoid returning database entities directly to your controller. Project your database queries directly into Data Transfer Objects (DTOs) using .Select(). This ensures you only pull the columns you intend to send back.

3. Caching is not a band-aid
Never cache a slow, broken query to "fix" performance. Optimize the query first so a cache-miss is still fast, then layer on caching to handle high traffic.

4. Small choices compound
An extra join or two feels harmless on a clean local machine with a database of 10 rows. In production, with millions of rows and hundreds of concurrent users, those small choices scale into server crashes.

What is the most memorable performance bug you've ever had to hunt down in production? Let me know in the comments below!

Comments

[Luis Cruz]

I was particularly intrigued by the concept of "unintentional query creep" and how it led to a massive SQL statement with over 10 table joins executing repeatedly. The fact that a simple review card feature ended up causing such a significant performance issue is a great reminder to always inspect the generated SQL and project directly to DTOs. The solution of creating a slim, inline projection to select only the required columns is a great example of how applying clean query principles can make a huge difference. I've had similar experiences where a small change in the query or data model can have a significant impact on performance - have you considered implementing any automated testing or monitoring to detect potential query performance issues before they make it to production?