How I Cut an EF Core Endpoint’s Response Time by 50% With Dapper and Raw SQL

The execution plan, the missing index, and the raw SQL query that saved one job portal’s busiest page.

In my post on **10 ASP.NET Core Mistakes to Avoid Before Production**, I mentioned that loading more data than necessary is one of the easiest ways to hurt performance, and that AsNoTracking() and projection go a long way.

Turns out “a long way” has a ceiling.

This is the story of one endpoint on a job portal that outgrew every EF Core trick I knew, and what it took to actually fix it.

The Endpoint That Started Falling Over

The job listings search page was the busiest thing we had. Keyword, category, location, salary range, sort by posted date, paginate. Nothing exotic.

Here’s roughly what the query looked like.

public async Task<List<JobListingDto>> SearchJobsAsync(JobSearchRequest request)
{
    var query = _context.Jobs
        .Include(j => j.Company)
        .Include(j => j.Location)
        .Include(j => j.Skills)
        .Where(j => j.IsActive);

if (!string.IsNullOrEmpty(request.Keyword))
        query = query.Where(j => j.Title.Contains(request.Keyword));
    if (!string.IsNullOrEmpty(request.Category))
        query = query.Where(j => j.Category == request.Category);
    if (!string.IsNullOrEmpty(request.Location))
        query = query.Where(j => j.Location.City == request.Location);
    var jobs = await query
        .OrderByDescending(j => j.PostedDate)
        .Skip((request.Page - 1) * request.PageSize)
        .Take(request.PageSize)
        .ToListAsync();
    return _mapper.Map<List<JobListingDto>>(jobs);
}

This was fine for months. Then a recruiting partner bulk-posted a few thousand openings overnight, traffic to the search page tripled by 8 AM, and average response time went from around 820ms to well over two seconds during peaks.

First Aid: AsNoTracking and Projection

The first problem was obvious once I looked. Three Include() calls pulling full related entities, EF's change tracker snapshotting every row, and a mapper converting entities we never needed to track in the first place. Nobody was updating a job listing from a search result.

public async Task<List<JobListingDto>> SearchJobsAsync(JobSearchRequest request)
{
    var query = _context.Jobs
        .AsNoTracking()
        .Where(j => j.IsActive);

if (!string.IsNullOrEmpty(request.Keyword))
        query = query.Where(j => j.Title.Contains(request.Keyword));
    if (!string.IsNullOrEmpty(request.Category))
        query = query.Where(j => j.Category == request.Category);
    if (!string.IsNullOrEmpty(request.Location))
        query = query.Where(j => j.Location.City == request.Location);
    var jobs = await query
        .OrderByDescending(j => j.PostedDate)
        .Skip((request.Page - 1) * request.PageSize)
        .Take(request.PageSize)
        .Select(j => new JobListingDto
        {
            Id = j.Id,
            Title = j.Title,
            CompanyName = j.Company.Name,
            City = j.Location.City,
            Salary = j.Salary,
            PostedDate = j.PostedDate
        })
        .ToListAsync();
    return jobs;
}

Dropping the tracking overhead and projecting straight into a DTO cut average response time to around 600ms. A real win, and also nowhere near enough. The database itself was still doing too much work per request.

Reading the Execution Plan

At this point I stopped guessing and pulled up the actual execution plan in SQL Server Management Studio.

What I found: a Clustered Index Scan on the Jobs table eating almost all the query cost, plus a Key Lookup for every row that survived the filter. There was no index supporting IsActive, Category, or LocationId at all, only the clustered primary key on JobId.

Every search, regardless of how selective it looked, was reading the entire table and then doing an extra round trip per row to fetch columns the index didn't cover.

SQL Server’s missing index suggestion (the green text in the plan) pointed at exactly this gap. So I added a covering index.

CREATE NONCLUSTERED INDEX IX_Jobs_Active_Category_Location
ON Jobs (IsActive, Category, LocationId)
INCLUDE (Title, CompanyId, Salary, PostedDate);

Logical reads on that query dropped from around 45,000 pages to under 400. Average response time fell to roughly 480ms. Better, but the plan still wasn’t stable, and that’s where EF Core started working against me instead of for me.

Where EF Core Still Left Performance on the Table

Here’s the part that took the longest to track down. Even with the index in place, response times were inconsistent. Same query shape, wildly different execution times depending on which filters were populated.

The cause was EF Core’s handling of optional filters. Because Category and Location could each be null, EF generated SQL along the lines of WHERE (@Category IS NULL OR Category = @Category). SQL Server caches a plan for the first set of parameters it sees and reuses it for everyone after.

If the first request that ran was an unfiltered "show me everything," that plan got cached and reused for a highly selective search a minute later, and vice versa. Classic parameter sniffing, made worse by a dynamic filter pattern the optimizer couldn't reason about cleanly.

EF Core wasn’t wrong to generate that SQL. It’s the correct general-purpose translation of “maybe filter by this.” But general-purpose was exactly the problem on our busiest endpoint.

Replacing the Hot Path With Dapper and Raw SQL

For this one query, I dropped down to Dapper and wrote the SQL by hand.

public async Task<List<JobListingDto>> SearchJobsAsync(JobSearchRequest request)
{
    const string sql = @"
        SELECT
            j.Id,
            j.Title,
            c.Name AS CompanyName,
            l.City,
            j.Salary,
            j.PostedDate
        FROM Jobs j
        INNER JOIN Companies c ON c.Id = j.CompanyId
        INNER JOIN Locations l ON l.Id = j.LocationId
        WHERE j.IsActive = 1
            AND (@Category IS NULL OR j.Category = @Category)
            AND (@LocationId IS NULL OR j.LocationId = @LocationId)
            AND (@Keyword IS NULL OR j.Title LIKE @Keyword)
        ORDER BY j.PostedDate DESC
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY
        OPTION (RECOMPILE);";

using var connection = _connectionFactory.CreateConnection();
    var jobs = await connection.QueryAsync<JobListingDto>(sql, new
    {
        request.Category,
        request.LocationId,
        Keyword = string.IsNullOrEmpty(request.Keyword) ? null : $"%{request.Keyword}%",
        Offset = (request.Page - 1) * request.PageSize,
        request.PageSize
    });
    return jobs.AsList();
}

Two things made the difference here, not just the ORM swap. OPTION (RECOMPILE) tells SQL Server to build a fresh, accurately estimated plan for each execution instead of reusing whatever got cached first. That costs a small amount of CPU per call, but it was nothing compared to the cost of a bad plan scanning the wrong number of rows.

And Dapper skips the change tracker entirely, so there’s no snapshotting, no proxy generation, no mapping overhead beyond a straightforward object hydration.

Average response time landed around 390ms. Down from 820ms at the start. Roughly cut in half, and the database server’s CPU utilization dropped along with it.

When Raw SQL Is the Wrong Tool

Don’t rewrite your whole data layer in Dapper. This was one endpoint, flagged by an actual incident and an actual execution plan. Everything else stayed exactly as EF Core generated it.

Don’t skip parameterization. Raw SQL still means parameterized SQL. Concatenating user input into a query string trades a performance problem for a security incident.

Don’t guess at indexes without a plan in hand. I’ve seen teams add an index because “it felt like it would help” and end up with a dozen overlapping ones. Look at the actual plan first.

Don’t do this without team buy-in. Raw SQL bypasses EF Core’s migrations, so someone has to keep the hand-written query in sync with the schema by hand. Fine as an escape hatch for one hot path, painful once five engineers are hand-rolling SQL everywhere.

Final Thoughts

If I had to compress this into a few rules:

• AsNoTracking and projection are the first move, not the last one. They’re necessary but often not sufficient.
• Look at the execution plan before you touch an index. Guessing gets you the wrong index and a false sense of progress.
• A missing index fixes a scan. It doesn’t fix an unstable plan caused by optional filters. Those are two different problems.
• Raw SQL and Dapper earn their place on measured, high-traffic hot paths. They’re not a wholesale replacement for your ORM.
• This is a targeted trade, not a philosophy. You’re giving up EF’s migrations and query composition on one endpoint in exchange for a plan you fully control.

None of this makes EF Core the wrong choice for the app. It just means the busiest door in the building deserved a different lock. I left that job a while ago, but I still catch myself pulling up an execution plan out of habit before touching an index anywhere else.

Have you ever had to drop down to raw SQL for a single endpoint, or do you hold the ORM abstraction no matter what? Curious how far people push that tradeoff.