The Fastest Query Is Sometimes the One You Never Make

A customer report taking 80 to 120 seconds to load usually makes you think the database needs more help.

That was my first thought too.

At the time, several reports in one of the systems I worked on were taking well over a minute to render. Our target was under 10 seconds, so I started considering the obvious heavy-duty fix: move more of the report-generation logic out of the .NET application and into SQL Server stored procedures.

That would probably have made some queries faster.

It also would have solved the wrong problem.

The bigger issue was simpler: we were moving far more data than the reports actually needed.

The first bottleneck: loading entities when we needed values

A typical application query often starts innocently enough:

var projects = await dbContext.Projects
    .Include(x => x.Customer)
    .Include(x => x.Tasks)
    .Include(x => x.ProjectManager)
    .ToListAsync();

The report might ultimately need only:

• project name
• customer name
• completion percentage
• total cost

But the application has asked Entity Framework to materialize complete entities and related objects.

The database returns them.

EF Core creates the objects.

The application holds them in memory.

Some of that data may then be transformed again before being sent to the frontend.

We were paying for information the report never displayed.

The fix was projection.

Instead of loading the complete object graph, the query could describe the shape of the report directly:

var projects = await dbContext.Projects
    .Select(project => new ProjectReportDto
    {
        ProjectName = project.Name,
        CustomerName = project.Customer.Name,
        Completion = project.CompletionPercentage,
        TotalCost = project.TotalCost
    })
    .ToListAsync();

That change looks almost boring.

It was also the important one.

Projection meant SQL Server could return only the columns required by the report rather than enough information to reconstruct every underlying entity.

Less data crossed the database connection.

Fewer objects were materialized.

Less memory was consumed.

Less data had to travel through the rest of the application.

For most of the reports, that was enough to bring performance close to the target without rewriting the reporting system around stored procedures.

Then the cached report was still slow

One report was different.

Its data was already cached in Redis, yet it was still taking far too long to load.

At first this seemed contradictory.

The expensive database work had supposedly already happened. Reading from Redis should have been cheap.

But caching does not make the size of an object irrelevant.

We inspected what was actually being cached and found that the report's underlying model contained navigation properties to several related objects. Those related objects contained relationships of their own.

The cached value was therefore much larger than the report itself required.

Conceptually, instead of caching something like this:

{
  "projectName": "Project Alpha",
  "customer": "Acme Ltd",
  "completion": 84,
  "totalCost": 125000
}

we were much closer to caching this:

Project
├── Customer
│   ├── Contacts
│   └── Addresses
├── ProjectManager
│   └── ...
├── Tasks
│   ├── Assignee
│   ├── Comments
│   └── ...
└── Other related entities

The cache was avoiding the database round trip while still making the application serialize, store, transfer and deserialize a large object graph.

That is an important distinction.

A cached oversized payload is still an oversized payload.

Cache the representation, not the entity graph

We changed the boundary.

Instead of caching domain entities and whatever relationships happened to be attached to them, we created an explicit representation for the report:

public sealed class CustomerReportDto
{
    public string CustomerName { get; init; }
    public decimal TotalRevenue { get; init; }
    public int ActiveProjects { get; init; }
    public decimal OutstandingAmount { get; init; }
}

Then the cache contained exactly what the report needed:

var report = await dbContext.Customers
    .Where(customer => customer.Id == customerId)
    .Select(customer => new CustomerReportDto
    {
        CustomerName = customer.Name,
        TotalRevenue = customer.Projects.Sum(x => x.Revenue),
        ActiveProjects = customer.Projects.Count(x => x.IsActive),
        OutstandingAmount = customer.Invoices
            .Where(x => !x.IsPaid)
            .Sum(x => x.Amount)
    })
    .SingleAsync();

await cache.SetAsync(cacheKey, report);

The DTO became a useful boundary.

The database query produced the report shape.

The cache stored the report shape.

The frontend received the report shape.

The internal entity graph no longer leaked through all three layers.

That report subsequently loaded in under 10 seconds.

About virtual

One detail from my original diagnosis deserves a more precise explanation.

The entities involved had several navigation properties marked virtual.

In Entity Framework, virtual can allow navigation properties to participate in lazy-loading proxies when lazy loading is configured. But virtual by itself is not the fundamental performance problem.

The important question is:

What relationships have actually been loaded, and what object graph are you passing to your serializer?

If an application serializes ORM entities directly, it becomes surprisingly easy for persistence concerns and object relationships to leak into API responses or caches.

A DTO makes that boundary explicit.

Database model
     │
     │ projection
     ▼
Report DTO
   ┌──────┴──────┐
   ▼             ▼
 Redis           API
   │             │
   └──────┬──────┘
          ▼
       Client

The DTO is not just a stylistic abstraction.

It answers a performance question:

What is the minimum representation this operation actually requires?

Why I did not reach for stored procedures

Stored procedures have perfectly legitimate uses, particularly for complex set-based operations and workloads where tighter control over SQL execution matters.

But moving code into SQL Server would have increased the amount of reporting logic we had to maintain in two different places.

Before doing that, I needed to know whether SQL execution was actually the bottleneck.

In this case, much of it wasn't.

The system was spending too much time retrieving, materializing, serializing and moving unnecessary data.

A more sophisticated query would not compensate for an unnecessarily large data boundary.

That changed how I approached performance work afterward.

Instead of beginning with:

How do I make this operation faster?

I increasingly started with:

What work is this operation doing that it never needed to do?

Those are different questions.

The second one often produces much simpler fixes.

What I took away from it

The reports did not need more sophisticated infrastructure.

They needed less data.