Query Optimisation

A slow page is a measurement problem before it is a coding problem. The usual failure is to guess: add an index, rewrite a Where clause, sprinkle in AsNoTracking, and hope the timing improves. Sometimes it does, and you still do not know why.

The method below takes longer to start and much less time to finish, because every step produces evidence. At the end you know what you changed, what it did, and what to tell the next person who opens the file.

// Development configuration only.
options.UseSqlServer(connectionString)
       .LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging();

IQueryable<Order> query = context.Orders
    .Where(o => o.Status == OrderStatus.Open)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product);

// The statement, without executing it.
Console.WriteLine(query.ToQueryString());

• LogTo sends EF Core's log anywhere you can write a string. At Information level you get each command, its parameters and how long it took, which is three of the numbers you need.
• EnableSensitiveDataLogging is what makes parameter values appear. It is genuinely useful while diagnosing and has no place in production, where those values would end up in log storage.
• ToQueryString() prints the SQL for a query you have built but not run. Useful in a test or a scratch endpoint when you want the text without waiting for a slow result.
• Count the statements as well as reading them. If the log shows the same statement 200 times with different parameters, stop here — that is the N+1 problem, and no amount of plan reading will fix it.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT [o].[Id], [o].[CustomerId], [o].[OrderDate], [o].[Status], [o].[Notes],
       [i].[Id], [i].[Quantity], [i].[UnitPrice],
       [p].[Id], [p].[Name], [p].[Sku]
FROM [Orders] AS [o]
LEFT JOIN [OrderItems] AS [i] ON [o].[Id] = [i].[OrderId]
LEFT JOIN [Products] AS [p] ON [i].[ProductId] = [p].[Id]
WHERE [o].[Status] = 1
ORDER BY [o].[Id], [i].[Id];

-- What the plan and the statistics showed:
--   Clustered index scan on Orders: 1,400,000 rows read
--   Rows returned: 84,000
--   Estimated rows 900, actual rows 84,000
--   Sort operator warning: spilled to temporary storage
--   Elapsed 2,140 ms, of which most was the scan and the sort

• A scan on Orders with a Status predicate means no index supports that predicate, so the database read the whole table to find roughly one row in seventeen. That is the first candidate.
• The gap between estimated and actual rows matters as much as the scan. The plan was built expecting 900 rows and handed 84,000, so the join and sort strategies it chose were the wrong ones for the real volume. Stale statistics are a common cause and are cheap to rule out.
• The sort spilling to temporary storage is the ORDER BY EF Core added so it could assemble the object graph — on far more rows than it should have had.
• Now notice what the plan cannot tell you: 84,000 rows for a few thousand orders means the parent columns are being repeated per order item. That is a query shape problem, and no index fixes it.

// One row per order, only the columns the screen shows, one page at a time.
List<OpenOrderRow> page = await context.Orders
    .Where(o => o.Status == OrderStatus.Open)
    .OrderByDescending(o => o.OrderDate)
    .Skip(pageIndex * pageSize)
    .Take(pageSize)
    .Select(o => new OpenOrderRow(
        o.Id,
        o.OrderDate,
        o.Customer.Name,
        o.Items.Count,
        o.Items.Sum(i => i.Quantity * i.UnitPrice)))
    .ToListAsync();

• Three changes, each with its own reason. The projection cuts the columns and removes the repeated parent rows. The paging caps the work at one screen. A supporting index on (Status, OrderDate) makes the filter a seek and supplies the sort order, so nothing has to be sorted at all.
• There is no AsNoTracking here, and that is not an omission: a projection to a row type is not an entity, so there was never anything to track.
• Apply these one at a time and measure after each. Otherwise you will not know which one mattered, and you will maintain the other two forever on the assumption that they did.
• Skip and Take need a deterministic sort to be meaningful. OrderDate alone can tie, so for a stable page order add a tie-breaker such as Id.