Although Entity Framework Core greatly simplifies database access for.NET applications, ease of use does not guaranty optimal query execution. Compiled queries are one improvement that frequently surfaces while examining EF Core query speed. Prior to executing the necessary database operation, EF Core typically needs to perform a LINQ query. Compiled queries might lessen some of the repetitive query-processing cost for frequently conducted queries, particularly in high-throughput systems.
Whether compiled queries are theoretically quicker is not the crucial question. Whether they have a discernible impact on the real workload of your application is a worthwhile question. A suitable benchmark is necessary for it.On a development laptop, a test with 100 rows can demonstrate an improvement that vanishes when the program is linked to a production-sized database. On the other hand, when a query runs hundreds or thousands of times per second, a tiny per-call savings may become significant.
This article describes how EF Core compiled queries operate, how to use big production-style datasets to compare them against standard LINQ queries, what measures are important, and when it makes sense to introduce the optimization.
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.
HostForLIFE.eu ASP.NET Core 10.0 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.
