Benchmarking EF Core Compiled Queries on Large Production-Style Datasets

Entity Framework Core makes database access much easier for .NET applications, but convenience does not automatically mean every query is executed as efficiently as possible.

One optimization that often comes up when investigating EF Core query performance is compiled queries. EF Core normally has to process a LINQ query before it can execute the corresponding database command. For frequently executed queries, especially in high-throughput applications, compiled queries can reduce some of that repeated query-processing overhead.

The important question is not whether compiled queries are faster in theory. The useful question is whether they make a measurable difference for the workload your application actually has.

That requires a proper benchmark.

A benchmark using 100 rows on a developer laptop may show an improvement that disappears when the application is connected to a production-sized database. Conversely, a small per-call saving can become important when a query executes hundreds or thousands of times per second.

This article explains how EF Core compiled queries work, how to benchmark them against normal LINQ queries using large production-style datasets, what measurements matter, and when the optimization is worth introducing.

What Are EF Core Compiled Queries?

A normal EF Core query typically starts with a LINQ expression:

var customer = await db.Customers
    .Where(x => x.Id == customerId)
    .SingleOrDefaultAsync();

EF Core has to process the expression and translate it into a database command.

For example, conceptually:

LINQ expression
      |
      v
EF Core query processing
      |
      v
SQL command
      |
      v
Database

When the same query shape is executed repeatedly, EF Core can reuse internal query compilation information. However, an application can also explicitly use a compiled query.

For example:

private static readonly Func<AppDbContext, int, Customer?>
    GetCustomer =
        EF.CompileQuery(
            (AppDbContext db, int id) =>
                db.Customers
                  .SingleOrDefault(x => x.Id == id));

The query can then be executed as:

var customer = GetCustomer(db, customerId);

The key idea is that the query expression is compiled once and reused.

Why Benchmarking Is Necessary

It is tempting to look at compiled queries as a simple performance switch:

Normal LINQ -> slower
Compiled query -> faster

Real applications are more complicated.

The total request time may include:

Network latency.
Database execution time.
Index lookups.
Query result materialization.
Object allocation.
Application-side processing.
Connection acquisition.
Transaction handling.
EF Core query-processing overhead.

If the database takes 20 milliseconds to execute a query and compiled-query processing saves only a small fraction of a millisecond, the optimization may have little impact on end-to-end latency.

On the other hand, if the query itself is very fast and executes extremely frequently, EF Core overhead can become a meaningful part of the total cost.

That is why compiled-query benchmarks should isolate the right variables.

Benchmark the Right Workload

A production-style benchmark should not simply create a few hundred records and execute one query repeatedly.

Instead, construct a dataset that resembles the application's real access patterns.

For example, an order-processing system might contain:

Table	Approximate Records
Customers	1,000,000
Orders	10,000,000
OrderItems	30,000,000
Products	500,000
Payments	8,000,000

The exact numbers are not important. What matters is that the database is large enough to exercise realistic indexes, query plans, buffer usage, and result sets.

The benchmark should also use realistic distributions rather than generating completely uniform data.

Choose Representative Queries

Compiled queries are most interesting for queries that execute frequently.

Good candidates include:

var customer = await db.Customers
    .SingleOrDefaultAsync(x => x.Id == customerId);

or:

var orders = await db.Orders
    .Where(x => x.CustomerId == customerId)
    .OrderByDescending(x => x.CreatedAt)
    .Take(20)
    .ToListAsync();

Another example is a lookup involving several predicates:

var order = await db.Orders
    .Where(x =>
        x.CustomerId == customerId &&
        x.Status == OrderStatus.Pending)
    .OrderBy(x => x.CreatedAt)
    .FirstOrDefaultAsync();

These queries are suitable candidates because their shapes remain stable while their parameters change.

Compiled queries are generally less useful when the query structure itself changes dynamically for every request.

A Normal EF Core Query

Start with the conventional implementation.

public async Task<Order?> GetOrderAsync(
    AppDbContext db,
    long orderId,
    CancellationToken cancellationToken)
{
    return await db.Orders
        .AsNoTracking()
        .Where(x => x.Id == orderId)
        .SingleOrDefaultAsync(cancellationToken);
}

Using AsNoTracking() is intentional in this benchmark when the operation is read-only.

The benchmark should compare equivalent workloads. If one version uses tracking and another does not, the results do not isolate compiled-query performance.

Create the Compiled Query

The equivalent compiled query can be defined once:

private static readonly Func<
    AppDbContext,
    long,
    Order?> GetOrderCompiled =
        EF.CompileQuery(
            (AppDbContext db, long orderId) =>
                db.Orders
                  .AsNoTracking()
                  .SingleOrDefault(
                      x => x.Id == orderId));

The call site becomes:

var order =
    GetOrderCompiled(db, orderId);

For asynchronous execution, use the appropriate compiled asynchronous API:

private static readonly Func<
    AppDbContext,
    long,
    Task<Order?>> GetOrderCompiledAsync =
        EF.CompileAsyncQuery(
            (AppDbContext db, long orderId) =>
                db.Orders
                  .AsNoTracking()
                  .SingleOrDefault(
                      x => x.Id == orderId));

Then:

var order =
    await GetOrderCompiledAsync(db, orderId);

The normal and compiled versions should produce equivalent database behavior.

Keep the Database Constant

One of the most important benchmark rules is to avoid changing multiple variables simultaneously.

If the normal query uses:

Database A
.NET version X
EF Core version Y

and the compiled query uses:

Database B
.NET version X
EF Core version Y

the comparison is already compromised.

Use the same:

Database server.
Database version.
Dataset.
Indexes.
Connection configuration.
EF Core version.
.NET runtime.
Query parameters.
Hardware or virtual machine.
Network path.

Only change the query execution strategy.

Benchmark Against a Real Database

EF Core query performance should generally be measured against the database engine that the application actually uses.

An in-memory provider does not reproduce the behavior of a real relational database.

A production-style benchmark should therefore look more like:

Benchmark application
        |
        v
EF Core
        |
        v
Database provider
        |
        v
Real database

This allows the benchmark to include actual network communication, SQL execution, query planning, indexing, and result materialization.

Use BenchmarkDotNet Carefully

BenchmarkDotNet is useful for controlled performance experiments, but database benchmarks require additional discipline.

A simplified benchmark could look like:

[MemoryDiagnoser]
public class EfCoreQueryBenchmark
{
    private AppDbContext _db = null!;
    private long _orderId;

    [GlobalSetup]
    public void Setup()
    {
        _db = CreateDbContext();
        _orderId = 5000000;
    }

    [Benchmark(Baseline = true)]
    public async Task<Order?> NormalQuery()
    {
        return await _db.Orders
            .AsNoTracking()
            .SingleOrDefaultAsync(
                x => x.Id == _orderId);
    }

    [Benchmark]
    public async Task<Order?> CompiledQuery()
    {
        return await GetOrderCompiledAsync(
            _db,
            _orderId);
    }
}

However, this benchmark needs careful interpretation because database calls introduce external state, network latency, connection behavior, caching, and database-side variability.

For database benchmarks, it is often useful to complement BenchmarkDotNet with application-level load tests and database metrics.

Warm-Up Matters

The first execution of a query can behave differently from later executions.

Factors include:

Application initialization.
EF Core internal query processing.
Database plan preparation.
Connection establishment.
Database buffer cache.
Operating-system cache.

Therefore, do not compare only the first execution.

A useful test should include a warm-up phase followed by measured iterations.

Conceptually:

Initialization
     |
     v
Warm-up executions
     |
     v
Measured executions
     |
     v
Statistical analysis

This makes the results more representative of a long-running service.

Cold and Warm Performance Are Different Questions

A production application may care about both.

For example, a serverless or short-lived workload may repeatedly experience application startup.

A long-running ASP.NET Core service may execute the same query thousands of times after startup.

Therefore benchmark:

Scenario	What It Measures
Cold execution	Startup and first-use behavior
Warm execution	Steady-state query performance
Repeated execution	Throughput and consistency
Concurrent execution	Behavior under load

Do not combine these into a single number.

Measure More Than Average Time

Average execution time is useful but incomplete.

Capture at least:

Mean latency.
Median latency.
p95 latency.
p99 latency.
Throughput.
Allocations.
CPU usage.
Database CPU.
Database logical reads.
Error rate.

For example:

Metric	Normal LINQ	Compiled Query
Mean	Measure	Measure
Median	Measure	Measure
p95	Measure	Measure
p99	Measure	Measure
Allocations	Measure	Measure
DB logical reads	Measure	Measure
Throughput	Measure	Measure

Do not publish invented numbers simply to demonstrate an improvement. The benchmark should produce the numbers.

Database Execution Time Can Dominate

Suppose your application performs:

EF Core processing:     0.20 ms
Network + DB execution: 8.50 ms
Materialization:        0.40 ms

Even if compiled queries reduce EF Core processing significantly, the total request may change very little.

This is why query optimization should start with profiling.

If the SQL query itself is slow, investigate:

Indexes.
Query shape.
Joins.
Cardinality.
Query plan.
Database statistics.
Returned rows.

A compiled query should not be used to hide a poorly designed database query.

Use Large and Small Result Sets

Benchmark different result sizes.

For example:

Single row
10 rows
100 rows
1,000 rows
10,000 rows

This helps determine where EF Core overhead matters.

For a single-row indexed lookup, application-side overhead may represent a larger portion of total execution time.

For a query returning thousands of rows, database execution and materialization can dominate.

That difference is important when deciding whether compiled queries are worthwhile.

Test Different Parameter Values

A benchmark should not repeatedly query exactly the same record.

For example:

var random = Random.Shared.NextInt64(
    1,
    10_000_000);

Then use different IDs across iterations.

This can provide a more representative workload, although deterministic parameter sequences may be preferable for controlled benchmark comparisons.

The important thing is to understand whether the database workload is sensitive to parameter distribution.

Avoid Accidental Query Differences

A benchmark becomes meaningless if the two queries are not equivalent.

For example, this:

db.Orders
    .AsNoTracking()
    .Where(x => x.CustomerId == customerId)

should not be compared against:

db.Orders
    .Where(x => x.CustomerId == customerId)
    .Include(x => x.Items)

because the second query performs substantially different work.

Keep the query shape equivalent.

Check Generated SQL

Before interpreting benchmark results, inspect the SQL generated by the normal query.

You can use EF Core logging to examine the generated commands.

The goal is to verify that both approaches ultimately execute equivalent SQL.

For example:

SELECT ...
FROM Orders
WHERE Id = @p0

If the SQL differs, investigate why before attributing the performance difference to compiled queries.

Test Query Compilation Overhead Separately

One of the most useful experiments is to compare repeated execution against first-use behavior.

Conceptually:

Experiment A
Normal query, first execution

Experiment B
Normal query, repeated execution

Experiment C
Compiled query, first execution

Experiment D
Compiled query, repeated execution

This helps identify whether the optimization primarily affects initial query-processing overhead or produces a meaningful steady-state improvement.

Benchmark Concurrent Requests

A production API rarely executes one query at a time.

Consider:

100 concurrent requests
        |
        +-- Query A
        +-- Query B
        +-- Query C
        ...

Run concurrency tests such as:

1 concurrent request
10 concurrent requests
50 concurrent requests
100 concurrent requests
500 concurrent requests

The exact levels depend on the application's expected workload.

Measure both application and database behavior.

A compiled query that improves single-request latency but provides no meaningful throughput improvement under realistic concurrency may not justify additional complexity.

Watch Database Connection Pooling

Connection pooling can influence benchmark results.

If the benchmark repeatedly opens and closes contexts, make sure you understand what is happening with the underlying connections.

You should decide whether the experiment is intended to measure:

Query execution with warm connections

or:

End-to-end operation including connection acquisition

Both can be useful, but they answer different questions.

Benchmark With Tracking Disabled and Enabled

For read-heavy workloads, AsNoTracking() is common.

However, applications sometimes require tracked entities.

If the production workload uses tracking, benchmark tracking.

For example:

db.Orders
    .Where(x => x.Id == orderId)
    .SingleOrDefaultAsync();

and:

db.Orders
    .AsNoTracking()
    .Where(x => x.Id == orderId)
    .SingleOrDefaultAsync();

Do not optimize a no-tracking benchmark and then assume the result applies directly to a tracking-heavy workload.

Measure Memory Allocations

Use memory diagnostics where appropriate.

For BenchmarkDotNet:

[MemoryDiagnoser]
public class QueryBenchmark
{
    // Benchmark methods
}

This can help identify allocation differences.

However, do not assume that fewer allocations automatically mean better application performance.

Garbage collection pressure, database latency, CPU consumption, and throughput should be considered together.

Production-Style Dataset Design

A realistic benchmark dataset should contain meaningful relationships.

For example:

Customers
   |
   +---- Orders
           |
           +---- OrderItems
                    |
                    +---- Products

Populate the dataset with:

Different customer sizes.
Customers with no orders.
Customers with many orders.
Recent and old orders.
Different order statuses.
Realistic date distributions.
Null and optional values where applicable.

This helps expose query behavior that a synthetic dataset with identical rows may hide.

Common Mistakes

Benchmarking Only 100 Rows

A tiny database often does not represent production behavior.

Using an In-Memory Provider

It does not reproduce real relational database execution.

Measuring Only the First Request

Cold execution can distort conclusions about steady-state performance.

Measuring Only Average Latency

Tail latency can reveal problems hidden by averages.

Changing Multiple Variables

Do not simultaneously change indexes, database versions, query shape, and EF Core behavior.

Optimizing Before Profiling

If the database query takes most of the request time, compiled queries may have little impact.

Reusing One Parameter Forever

Repeatedly querying the same row may produce cache behavior that does not represent production.

Ignoring Concurrency

A single-threaded benchmark may not reveal the behavior of a high-throughput API.

Troubleshooting Unexpected Benchmark Results

If compiled queries show almost no improvement, that is not necessarily a failed experiment.

First determine where the time is being spent.

For example:

Application query processing
Database execution
Network
Materialization
Serialization

If database execution dominates, optimize the SQL and database design first.

If the application spends a measurable amount of time processing repeated query expressions, compiled queries may become more interesting.

If results vary significantly between runs, investigate:

Database cache state.
Concurrent workload.
Connection pooling.
CPU contention.
Network variability.
Garbage collection.
Parameter distribution.

Run multiple benchmark sessions rather than relying on a single execution.

When Compiled Queries Make Sense

Compiled queries are most interesting when all of the following are reasonably true:

The query is executed frequently.
The query shape is stable.
The database query itself is already efficient.
EF Core query-processing overhead is measurable.
The performance improvement matters to the application's SLA or throughput.
The additional code complexity is acceptable.

Typical candidates can include high-volume APIs, frequently executed lookups, hot-path data-access operations, and services where database queries are already highly optimized.

When They May Not Be Worth It

Compiled queries may provide limited practical value when:

The query executes infrequently.
Database latency dominates the request.
The query shape changes frequently.
The application already has sufficient performance.
The code becomes significantly harder to maintain.
The measured improvement is too small to matter.

A small benchmark improvement does not automatically justify introducing specialized data-access patterns throughout an entire codebase.

A Practical Benchmark Matrix

A useful benchmark plan might look like this:

Dimension	Values
Query strategy	Normal / Compiled
Dataset	Small / Medium / Large
Result size	1 / 10 / 100 / 1,000
Tracking	On / Off
Concurrency	1 / 10 / 50 / 100
Execution state	Cold / Warm
Parameter distribution	Fixed / Variable

This produces a much stronger performance picture than a single benchmark.

How to Interpret the Results

Suppose the benchmark shows:

Normal query:
Mean: 2.10 ms

Compiled query:
Mean: 1.95 ms

The difference is approximately:

0.15 ms

That may look useful.

But ask another question:

How often does this query execute?

If it runs 10 times per minute, the improvement is probably irrelevant.

If it runs 10,000 times per second, the same difference could become significant.

The business and operational context matters as much as the benchmark number.

Frequently Asked Questions

Are compiled queries always faster?

No. They are designed to reduce some query-processing overhead, but the overall benefit depends on the workload. Database execution, network latency, materialization, and other costs can dominate total time.

Should every EF Core query use compiled queries?

No. Applying compiled queries everywhere can add unnecessary complexity. They are better suited to stable, frequently executed hot-path queries where profiling demonstrates a measurable benefit.

Do compiled queries make SQL execution faster?

Not necessarily. The primary optimization is around EF Core's query processing. The database still has to execute the resulting SQL.

Are compiled queries useful for large result sets?

They can be, but the benefit may become relatively smaller as database execution and result materialization dominate the total operation.

Should compiled queries be used before adding database indexes?

Usually not. If a query is slow because the database lacks an appropriate index or has an inefficient query plan, fix the database-side problem first.

Can BenchmarkDotNet be used with EF Core?

Yes, but database benchmarks require careful setup because database state, caching, network latency, connection pooling, and external workload can influence measurements. BenchmarkDotNet should be combined with appropriate database and application-level measurements.

Conclusion

EF Core compiled queries are a targeted optimization, not a universal replacement for normal LINQ queries. Their value becomes clear only when they are measured against realistic workloads and the actual database behavior is understood.

A useful benchmark should use a production-style dataset, equivalent query shapes, a real database provider, warm and cold scenarios, realistic parameter distributions, and representative concurrency. It should measure latency, tail behavior, allocations, throughput, and database-side work rather than focusing on a single average number.

The most important outcome of the benchmark is not proving that compiled queries are faster. It is identifying whether EF Core query-processing overhead is significant enough in your application's hot paths to justify the additional complexity. When the database query is already optimized and the same query shape executes at high volume, compiled queries can be a useful tool. When database execution dominates the workload, indexes, query design, schema changes, and database tuning are usually more valuable places to start.