A report that had been slow since the day it shipped. Not slow like "takes a few seconds." Slow like fifteen minutes. Support tickets going back years, all saying the same thing: the expenditure summary report just doesn't load. Users would click generate, walk away, come back, and it still wasn't done.
I picked it up expecting a missing index or a bad query plan. What I found was a loop inside a loop, each iteration firing multiple database round-trips, each round-trip returning the entire tenant's data table so the application could filter it down to one row.
What the code was doing
The report listed every location in the tenant. For each location, it broke down work order costs by category: labor, parts, equipment. Simple enough conceptually. The implementation had an outer loop over locations, an inner loop over categories, and inside that inner loop, several data retrieval calls.
Here's a genericized version of the pattern:
foreach (var location in locations)
{
var workOrders = _workOrderService.GetWorkOrders(locationId: location.Id);
foreach (var category in workOrders.Select(w => w.Category).Distinct())
{
var labors = _laborService.GetAllLabors(cultureId); // full table
var parts = _partsService.GetAllParts(cultureId); // full table
var equipment = _equipmentService.GetAllEquipment(cultureId); // full table
// filter down to this location + category
var locationLabors = labors.Where(l => l.WorkOrderId == ... && l.Category == category);
// ... same for parts, equipment
// ... calculate totals
}
}
The data retrieval methods returned IQueryable<T> backed by table-valued functions. Each call invoked the TVF and returned the full tenant table. The filtering happened afterward in memory.
For a small tenant this was fine. Slow, but fine. For a tenant with hundreds of locations and several WO categories each, the math became:
- N work order queries (one per location)
- N × C labor queries (one per location × category combination)
- N × C parts queries
- N × C equipment queries
A tenant with 200 locations averaging 4 categories each: roughly 2,600 database calls per report run. A larger tenant with 500 locations: over 6,000. Each call returning the full labor table, the full parts table, the full equipment table, hundreds of thousands of rows per call, discarded immediately after the filter ran.
The 15-minute report times made sense once I saw this. The mystery was why nobody had fixed it yet.
The obvious fix, and why it would have been worse
The first instinct was a global pre-fetch. Run each query once before the loop, hold the results in memory, filter from there:
// Pull everything once
var allLabors = _laborService.GetAllLabors(cultureId).ToList();
var allParts = _partsService.GetAllParts(cultureId).ToList();
var allEquipment = _equipmentService.GetAllEquipment(cultureId).ToList();
foreach (var location in locations)
{
var workOrders = _workOrderService.GetWorkOrders(locationId: location.Id);
foreach (var category in workOrders.Select(w => w.Category).Distinct())
{
var locationLabors = allLabors.Where(l => l.WorkOrderId == ... && l.Category == category);
// etc.
}
}
This works. Three database calls instead of thousands. The report that took fifteen minutes would finish in seconds.
I had the branch open, the change written, and was about to push it when I thought about the larger tenants.
The labor and parts tables hold the full history for the tenant: every work order ever completed, every part ever used, going back years. For a small tenant that's manageable. For a large one with tens of thousands of work orders per year, multiple sites, years of history, calling .ToList() with no predicate could pull millions of rows into the application server's memory in a single shot.
The TVF sources took only a culture ID parameter. No way to pass a filter into them. They returned the full tenant table and relied on the caller to narrow things down, so calling .ToList() with no predicate before it meant pulling everything regardless.
For the tenants with the worst report times, the ones that had been waiting years for this fix, a global pre-fetch might replace a 15-minute report with an out-of-memory crash. That's not a fix.
The approach that actually worked
The constraint was: we can't pass a predicate into the TVF, and we can't fetch everything at once either. But we also can't fetch per-loop. That was the original problem.
The answer was to split it into two phases.
Phase 1 — run the location loop normally, collecting work order IDs as you go:
var woIdList = new List<int>();
foreach (var location in locations)
{
var workOrders = _workOrderService.GetWorkOrders(locationId: location.Id);
woIdList.AddRange(workOrders.Select(w => w.Id));
}
woIdList = woIdList.Distinct().ToList();
This is still N database calls, one per location, no change there. But now we have a bounded list of exactly the work order IDs that appear in this report, scoped to the date range the user selected. Not the full tenant. Not all history. Just the IDs relevant to this specific report run.
Phase 2 — one pre-fetch per data type, filtered to that ID list:
var allLabors = _laborService.GetAllLabors(cultureId)
.Where(l => woIdList.Contains(l.WorkOrderId))
.ToList();
var allParts = _partsService.GetAllParts(cultureId)
.Where(p => woIdList.Contains(p.WorkOrderId))
.ToList();
var allEquipment = _equipmentService.GetAllEquipment(cultureId)
.Where(e => woIdList.Contains(e.WorkOrderId))
.ToList();
EF6 translates .Contains() on an integer list to a SQL IN clause. The database does the filtering. You get back only the rows for work orders that actually appear in the report.
Then the inner category loop runs against in-memory collections. Zero additional database calls.
Total queries: N (location work order fetches) + 3 (the scoped pre-fetches). For a tenant with 200 locations that's 203 queries instead of 2,600. For a tenant with 500 locations it's 503 instead of 6,000-plus.
The EF6 parameter limit
One more thing to watch: EF6 generates IN clauses as inline values, not parameterized. SQL Server has a 2,100-parameter limit on parameterized queries, but inline IN lists bypass that. The risk is query plan caching. Different ID lists produce different SQL strings, so the plan cache can't reuse them. For this use case that was acceptable. The report runs infrequently enough that plan cache pressure wasn't a concern.
If the ID list ever grew large enough to cause problems, the right move would be passing the IDs as a table-valued parameter and joining inside the query. That gives the plan cache something stable to reuse. For the typical report run the IN clause approach was clean and measurably fast.
What it looked like after
Before the fix: the main data-collection endpoint averaged around two minutes on the test environment, and the test environment was more powerful than production. In production, the same report ran five to fifteen minutes depending on the tenant.
After: four to eight seconds.
The fix had been obvious once the pattern was visible. The loop-inside-a-loop, the TVF sources that returned full tables, the in-memory filtering that discarded almost everything. All of it was right there in the code. The report had shipped like that and nobody had looked closely until the runtime climbed high enough to generate support tickets.
I don't know how many tenants had been quietly tolerating slow report times without filing tickets. The ones that filed them waited an average of eight months from first report to fix. The ones that didn't file tickets are harder to account for.
Until next time,
Deepanshu
Backend engineer writing about production bugs, distributed systems, and engineering patterns learned the hard way.
Top comments (2)
The two-phase shape is a good way to bound memory, but I’d make SQL generation an explicit regression test because it is the linchpin here. The post says the sources are stored-procedure-backed and non-composable, then shows
Where(...Contains(...))becoming anINpredicate. With EF6, that pushdown depends on whatGetAllLaborsactually returns; a non-composable procedure result can still execute the full procedure before client-side filtering.I’d capture the generated command plus logical reads for a large-tenant fixture and assert both returned-row count and rows read. If the filter cannot be pushed down, make the ID set part of the database contract instead: a table-valued parameter, temp table, or a filtered procedure. That also gives a cleaner path for very large ID sets than ever-growing literal
INlists.Hey @mads_hansen_27b33ebfee4c9 , fair point and thanks for digging into this.
One thing worth correcting though, these are actually table-valued functions not stored procedures. EF6 maps them via [DbFunction] which makes them composable, so the .Where(...Contains(idList)) does get pushed to SQL as an IN predicate. The article calling them "stored-procedure-backed" was wrong and that's what made your concern look applicable. That's on me, had some AI help cleaning up the draft and it quietly swapped the terminology. Annoying when that happens.
Your IN list point still stands. EF6 generates inline literals so query plans don't reuse across different ID sets and at large enough scale that's a real problem. The TVP approach you're suggesting is cleaner.
Adding a regression test that captures generated SQL and logical reads to the article as well. Good call.
Fixing the stored procedure reference now, appreciate the close read.