.NET & C# Development · Lesson 69 of 92

Challenge: Profile & Fix This Slow OrderFlow Endpoint

The Challenge

Below is a real-looking ASP.NET Core API with seven deliberate performance problems. Your job: find each one, measure it, and fix it. The final result should be 10-50x faster end-to-end.

Tools we'll use:

BenchmarkDotNet — CPU/memory microbenchmarks
dotnet-trace — production-safe CPU sampling
EF Core logging — expose generated SQL
Stopwatch + logs — quick in-process timing

Setup: Enable EF Core Query Logging

Before hunting, enable SQL logging so you can see every query:

// Program.cs (dev only)
builder.Services.AddDbContext<AppDbContext>(opt =>
    opt.UseSqlServer(connectionString)
       .LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging());

Problem 1 — N+1 EF Query

// SLOW: this fires 1 + N queries (1 for orders, 1 per order for customer)
[HttpGet("orders")]
public async Task<IActionResult> GetOrdersBroken()
{
    var orders = await _db.Orders.ToListAsync();
    return Ok(orders.Select(o => new
    {
        o.Id,
        CustomerName = o.Customer.Name,  // lazy load triggers here
        o.Total
    }));
}

Benchmark: 100 orders = 101 SQL queries, ~800 ms.

Fix: Eager-load with Include, or use a projection:

// FAST: single query with a JOIN
[HttpGet("orders")]
public async Task<IActionResult> GetOrdersFixed()
{
    var orders = await _db.Orders
        .AsNoTracking()
        .Select(o => new { o.Id, CustomerName = o.Customer.Name, o.Total })
        .ToListAsync();
    return Ok(orders);
}

Result: 1 query, ~12 ms. 67x faster.

Problem 2 — Synchronous I/O Blocking the Thread Pool

// SLOW: blocks a thread pool thread for the entire duration of the HTTP call
[HttpGet("report")]
public IActionResult GetReportBroken()
{
    var data = _reportService.FetchFromExternalApi(); // sync HTTP call
    return Ok(data);
}

// In ReportService — sync over async antipattern
public string FetchFromExternalApi()
    => _httpClient.GetStringAsync("https://slow-api.example.com/data")
                  .GetAwaiter().GetResult(); // blocks thread

Under load this exhausts the thread pool. dotnet-trace will show threads all blocked in WaitHandle.WaitOne.

Fix: Go async all the way:

[HttpGet("report")]
public async Task<IActionResult> GetReportFixed()
{
    var data = await _reportService.FetchFromExternalApiAsync();
    return Ok(data);
}

public async Task<string> FetchFromExternalApiAsync()
    => await _httpClient.GetStringAsync("https://slow-api.example.com/data");

Result: Thread returned to pool during I/O. Throughput scales linearly with concurrent requests instead of hitting a thread wall.

Problem 3 — No Pagination on Large Result Sets

// SLOW: loads every row, serializes the full JSON, saturates the network
[HttpGet("products")]
public async Task<IActionResult> GetAllProductsBroken()
{
    var products = await _db.Products.ToListAsync(); // 80,000 rows
    return Ok(products);
}

Benchmark: 80k rows = 450 ms DB + 180 ms serialization + 12 MB response.

Fix: Cursor or offset pagination:

[HttpGet("products")]
public async Task<IActionResult> GetProductsFixed(
    [FromQuery] int page = 1, [FromQuery] int size = 50)
{
    size = Math.Clamp(size, 1, 100);
    var total = await _db.Products.CountAsync();
    var items = await _db.Products
        .AsNoTracking()
        .OrderBy(p => p.Id)
        .Skip((page - 1) * size)
        .Take(size)
        .Select(p => new { p.Id, p.Name, p.Price })
        .ToListAsync();

    Response.Headers["X-Pagination"] =
        JsonSerializer.Serialize(new { page, size, total, pages = (int)Math.Ceiling((double)total / size) });

    return Ok(items);
}

Result: 50-row response, ~8 ms, 3 KB payload. Scales regardless of table size.

Problem 4 — No Caching on a Static Lookup

// SLOW: hits the DB on every request for data that changes once a day
[HttpGet("categories")]
public async Task<IActionResult> GetCategoriesBroken()
{
    var cats = await _db.Categories.ToListAsync();
    return Ok(cats);
}

Fix: Cache with IMemoryCache or IHybridCache:

[HttpGet("categories")]
public async Task<IActionResult> GetCategoriesFixed()
{
    var cats = await _cache.GetOrCreateAsync("categories", async entry =>
    {
        entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromHours(1);
        return await _db.Categories.AsNoTracking().ToListAsync();
    });
    return Ok(cats);
}

For distributed caching across pods, swap in IHybridCache (Redis-backed):

var cats = await _hybridCache.GetOrCreateAsync(
    "categories",
    async ct => await _db.Categories.AsNoTracking().ToListAsync(ct),
    new HybridCacheEntryOptions { Expiration = TimeSpan.FromHours(1) });

Result: First request: ~15 ms. Subsequent: <1 ms. Database load drops to near zero for this endpoint.

Problem 5 — Missing Database Indexes

// SLOW: full table scan on every filter
var orders = await _db.Orders
    .Where(o => o.CustomerId == customerId && o.Status == "Pending")
    .ToListAsync();

Run EXPLAIN (PostgreSQL) or SET STATISTICS IO ON (SQL Server) to confirm. You'll see "Table Scan" on a 500k row table — 320 ms.

Fix: Add a composite index via EF Core Fluent API:

// AppDbContext.OnModelCreating
modelBuilder.Entity<Order>()
    .HasIndex(o => new { o.CustomerId, o.Status })
    .HasDatabaseName("IX_Orders_CustomerId_Status");

Or raw migration:

SQL

CREATE INDEX IX_Orders_CustomerId_Status
ON Orders (CustomerId, Status)
INCLUDE (Id, Total, CreatedAt);

Result: Index seek instead of scan — 4 ms. 80x faster on 500k rows.

Problem 6 — Loading Tracked Entities for a Read Endpoint

// SLOW: EF tracks every entity — allocates change-tracking proxies, runs DetectChanges
[HttpGet("orders/{id}")]
public async Task<IActionResult> GetOrderBroken(Guid id)
{
    var order = await _db.Orders
        .Include(o => o.Items)
        .FirstOrDefaultAsync(o => o.Id == id);  // tracked!
    return Ok(order);
}

BenchmarkDotNet result: 2.4x more allocations vs. AsNoTracking. Under load: noticeable GC pressure.

Fix: Add AsNoTracking() on all read-only queries:

[HttpGet("orders/{id}")]
public async Task<IActionResult> GetOrderFixed(Guid id)
{
    var order = await _db.Orders
        .AsNoTracking()
        .Include(o => o.Items)
        .FirstOrDefaultAsync(o => o.Id == id);

    if (order is null) return NotFound();
    return Ok(order);
}

Or set it globally for all read queries:

builder.Services.AddDbContext<AppDbContext>(opt =>
    opt.UseSqlServer(conn).UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

Result: 40% fewer allocations. Meaningful under high concurrency.

Problem 7 — Over-Serialization of Unused Fields

// SLOW: serializes 40-field entity including BLOBs and navigation collections
[HttpGet("orders/summary")]
public async Task<IActionResult> GetSummaryBroken()
{
    var orders = await _db.Orders.Include(o => o.Items).ToListAsync();
    return Ok(orders); // sends everything: binary fields, nested collections, nulls
}

Measurement: A 1000-order response is 2.4 MB. System.Text.Json takes 90 ms serializing it.

Fix: Project to a minimal DTO at the DB layer — never load what you don't return:

public record OrderSummaryDto(Guid Id, string CustomerName, decimal Total, int ItemCount);

[HttpGet("orders/summary")]
public async Task<IActionResult> GetSummaryFixed()
{
    var orders = await _db.Orders
        .AsNoTracking()
        .Select(o => new OrderSummaryDto(
            o.Id,
            o.Customer.Name,
            o.Total,
            o.Items.Count))
        .ToListAsync();
    return Ok(orders);
}

Result: 1000-order response is now 68 KB. Serialization: 4 ms. 22x smaller payload, 20x faster to serialize.

Running the Benchmarks

// Benchmarks/OrdersBenchmark.cs
[MemoryDiagnoser]
[SimpleJob(RuntimeMoniker.Net90)]
public class OrdersBenchmark
{
    private WebApplication _app = null!;
    private HttpClient _client = null!;

    [GlobalSetup]
    public void Setup()
    {
        _app = TestApplication.Create(); // test host
        _client = new HttpClient { BaseAddress = new Uri("http://localhost:5001") };
    }

    [Benchmark(Baseline = true)]
    public Task GetOrdersBroken() => _client.GetAsync("/orders-broken");

    [Benchmark]
    public Task GetOrdersFixed() => _client.GetAsync("/orders");

    [GlobalCleanup]
    public async Task Cleanup() => await _app.DisposeAsync();
}

Run with: dotnet run -c Release --project Benchmarks/

Final Results Summary

| Problem | Before | After | Improvement | |---------------------------|---------|---------|-------------| | N+1 query (100 orders) | 800 ms | 12 ms | 67x | | Sync I/O under load | ~wall | linear | scales | | No pagination (80k rows) | 630 ms | 8 ms | 79x | | No caching (categories) | 15 ms | <1 ms | 15x+ | | Missing index (500k rows) | 320 ms | 4 ms | 80x | | Tracked reads | +2.4x alloc | baseline | 40% less GC | | Over-serialization | 90 ms/2.4MB | 4 ms/68KB | 22x |

Measure Before You Optimize — BenchmarkDotNet in Practice

Next Lesson

IHttpClientFactory — Never Leak Sockets Again