Benchmark with Database Execution Plans, Query Tags, Realistic Data Volumes, and End-to-End Latency

Learning outcomes

1. A benchmark is an experiment, not a stopwatch screenshot

Performance engineering asks a causal question: which phase is expensive, and what evidence changes after the intervention? A useful report records application/runtime/EF/provider/database versions, build configuration, machine/container resources, database location, dataset size and skew, indexes/statistics, tracking/query shape, warmup, cache state, concurrency, connection pooling, logging, and network topology.

The final chapter lab keeps SQLite local and free, so network latency is near-zero; that limitation must be disclosed. A production SQL Server/PostgreSQL benchmark across a network may have entirely different dominant costs.

2. Tag the query so the evidence chain has a stable name

var query = db.WorkOrders    .TagWith("ch17.queue.high-priority.v1")    .AsNoTracking()    .Where(w => w.Priority == WorkOrderPriority.High)    .OrderByDescending(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id)    .Take(100)    .Select(w => new    {        w.Id,        w.WorkOrderNumber,        w.CustomerName,        w.Priority    });Console.WriteLine(query.ToQueryString());var rows = await query.ToListAsync(ct);

The tag appears as a SQL comment and helps match a LINQ operation to command logs and database tooling. Do not put secrets, user-entered values, tenant identifiers, or unbounded high-cardinality request data in tags.

3. Start with a semantically correct but planner-hostile predicate

The mapping already contains a composite index on priority/work-order number from Chapter 04. A prefix wildcard search on the number can make that access path less useful.

var needle = "184";var slowShape = db.WorkOrders    .TagWith("ch17.number-search.contains")    .AsNoTracking()    .Where(w => w.Priority == WorkOrderPriority.High             && w.WorkOrderNumber.Contains(needle))    .OrderByDescending(w => w.WorkOrderNumber)    .Take(100)    .Select(w => new { w.Id, w.WorkOrderNumber });

Whether the engine scans or uses an index depends on the exact translation, collation, data distribution, SQLite version and indexes. Do not declare “Contains causes a scan” before asking the database.

4. Ask SQLite for its plan

Copy the generated SQL, substitute representative literal values only in a disposable diagnostic shell, and run EXPLAIN QUERY PLAN. Do not use literal substitution in application code.

EXPLAIN QUERY PLANSELECT work_order_id, work_order_numberFROM work_ordersWHERE priority = 3  AND instr(work_order_number, '184') > 0ORDER BY work_order_number DESCLIMIT 100;

Representative outputs contain terms such as SCAN or SEARCH ... USING INDEX. SQLite’s plan format is not SQL Server’s execution plan XML or PostgreSQL EXPLAIN (ANALYZE, BUFFERS); provider/engine chapters use their own plan tools.

5. Repair query semantics before adding random indexes

If the business requirement is actually “numbers beginning with a known prefix,” express that narrower requirement and verify the plan. If arbitrary substring search is required, a B-tree may not be the right index/search technology. The performance fix follows product semantics, not an index superstition.

var prefix = "WO-2026-184";var repaired = db.WorkOrders    .TagWith("ch17.number-search.prefix")    .AsNoTracking()    .Where(w => w.Priority == WorkOrderPriority.High             && w.WorkOrderNumber.StartsWith(prefix))    .OrderByDescending(w => w.WorkOrderNumber)    .ThenBy(w => w.Id)    .Take(100)    .Select(w => new { w.Id, w.WorkOrderNumber });

Generate SQL and inspect EXPLAIN QUERY PLAN again. The acceptance criterion is not that the plan text “looks better”; it is that the plan/data-read evidence and end-to-end benchmark improve for the real distribution without changing required results.

6. Use BenchmarkDotNet for repeatable client-side measurement

BenchmarkDotNet 0.15.8 is the current stable release checked for this chapter and is free/open source. It is optional because database benchmarks are sensitive to process isolation and external state; a controlled console harness is also acceptable. Never run the benchmark in Debug configuration.

dotnet new console -n ServiceHub.PerfLab -f net10.0cd ServiceHub.PerfLabdotnet add package BenchmarkDotNet --version 0.15.8dotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 10.0.11dotnet run -c Release

[MemoryDiagnoser]public class QueueBenchmarks{    private IDbContextFactory<ServiceHubContext> _factory = null!;    [GlobalSetup]    public async Task Setup()    {        _factory = PerfLabFactory.Create();        await using var db = await _factory.CreateDbContextAsync();        await db.WorkOrders.AsNoTracking().Select(w => w.Id).Take(1).ToListAsync();    }    [Benchmark(Baseline = true)]    public async Task<int> ContainsShape()    {        await using var db = await _factory.CreateDbContextAsync();        return await BuildContains(db).CountAsync();    }    [Benchmark]    public async Task<int> PrefixShape()    {        await using var db = await _factory.CreateDbContextAsync();        return await BuildPrefix(db).CountAsync();    }}

If the workload is dominated by SQLite file cache state, BenchmarkDotNet’s statistical rigor cannot make the external database state disappear. Record that state and complement it with database-side evidence.

7. Keep cold and warm cache experiments separate

Do not combine a cold-start improvement and a warm-query improvement into one percentage. They affect different phases and user experiences.

8. Decompose end-to-end latency instead of blaming “EF”

A command log duration includes provider/command execution around the database call; an application stopwatch also includes query construction, cache lookup/translation, context acquisition, materialization, serialization and surrounding code. A database execution plan explains access paths but not client materialization. Treat these as complementary measurements.

Experiment: ch17.number-search.prefixApp: Release, .NET 10.0.11, EF Core/Sqlite 10.0.11DB: SQLite file local SSD, 100,000 rows, deterministic 20% High priorityIndexes: IX_work_orders_priority_number presentTracking: AsNoTracking projection { Id, WorkOrderNumber }Rows returned: <=100Warmup: 5; measured iterations: 30Connection pooling: TrueEF tag: ch17.number-search.prefixArtifacts: ToQueryString, EF command log, EXPLAIN QUERY PLAN, raw timingsConclusion: <fill from measured evidence; do not pre-write a percentage>

9. Failure case: optimize a microbenchmark while production waits on locks

A compiled query can shave client CPU while users still wait seconds on a database lock or network hop. A projection can reduce allocation while a missing/ineffective index reads most of the table. Conversely, an excellent database plan can still return too many rows and overload serialization. A production decision needs end-to-end correlation.

10. Lab acceptance criteria

1. Seed a deterministic dataset large enough to expose plan/cardinality differences.
2. Tag and capture SQL for baseline and repaired query shapes.
3. Record SQLite EXPLAIN QUERY PLAN for both.
4. Run controlled Release-mode measurements with disclosed warmup/cache/pooling settings.
5. Verify result semantics are identical where the repair claims equivalence; if semantics changed, document the business requirement that permits it.
6. Keep raw result files and machine/tool versions with the benchmark.
7. Write a one-paragraph bottleneck conclusion that identifies what the evidence proves and what remains unmeasured.

11. Production judgment and bridge to observability

Performance engineering is complete only when the experiment can be repeated and the evidence points to a phase. Projection, compiled queries/models, pooling, indexing, and batching are workload-specific tools. Do not turn any of them into a global switch. Chapter 18 converts this laboratory discipline into production observability: structured EF logs, activities/metrics, interceptors, query tags, slow-query correlation, and dashboards without leaking sensitive data.

Authoritative references

Performance behavior is workload-, provider-, and version-sensitive. Re-check these primary sources before carrying a result into production.

• Performance Diagnosis - EF Core — logs, query correlation and bottleneck diagnosis
• Query Tags - EF Core — TagWith and TagWithCallSite
• Efficient Querying - EF Core — indexes, projections, result limits and query efficiency
• Advanced Performance Topics - EF Core — compiled queries/models and pooling evidence
• SQLite EXPLAIN QUERY PLAN — SQLite-specific execution-plan diagnostics
• BenchmarkDotNet 0.15.8 — optional free benchmark harness version checked for this chapter