Large-Scale Database Performance

This case describes performance investigation and data-access work within an existing platform. Dataset size describes the environment, not a system I built alone or a measured improvement attributable to me.

The system was an ecommerce platform that had grown organically with the business. Order volumes and order-line volumes had reached the kind of scale where query cost stops being a theoretical concern and starts showing up in page-load times, background-worker lag, and the database's own CPU. The work documented here is a senior-engineer performance investigation of that database layer: understanding which queries were doing real work, which were silently expensive, and where the right combination of indexes, projections, and caching could remove the bottleneck without changing the data model.

Three scale figures frame the rest of the case study. They are approximate, drawn from the production dataset at the time of the investigation rather than from a benchmark, and they exist to set context - not to claim any specific performance result:

Database performance mattered in this system because the backend APIs, the reporting layer, and the operational background workers all depended on the same set of large tables. The combination - large tables, complex queries, multiple joins across orders and order lines, and a steadily growing data volume - meant that the cost of a single query was no longer bounded by the row count it returned, but by the row count it had to touch in order to return that result.

The investigation was not chasing a single obvious slow query. Performance was spread across many queries, each of which had become a small bottleneck on its own. Identifying the actual cost centres required looking at the queries that mattered, understanding why each was slow, and choosing the cheapest effective lever for each - an index in one case, a query rewrite in another, a projection in a third, a cache where staleness was acceptable.

• High-CPU queries that were running during reporting windows and degrading other workloads.
• Expensive joins across orders and order lines, especially when combined with broad filter ranges.
• Inefficient data retrieval - queries returning more rows or more columns than the application actually used.
• N+1 query patterns, where a single logical operation generated many small database round-trips.
• Missing or inappropriate indexes, leaving the database to scan large ranges for results that an index could serve directly.
• Repeated work that could be served from a projection, a denormalised view, or a cache.

A performance investigation is rarely a single-layer problem. A request enters the application as a method call, becomes a query through EF Core's LINQ translation, becomes SQL at the database boundary, and only then hits the indexes and the large tables that determine its real cost. The same logical operation can have very different performance characteristics depending on what happens at each of those layers - and the only way to tell which layer is the bottleneck is to inspect the actual plan, not to assume.

The investigation followed a deliberate sequence rather than a trial-and-error optimisation. The first step was to understand where the bottleneck actually existed before changing any code or database structure. Changing the wrong thing - adding an index the planner would not use, rewriting a query whose cost was already low - produces noise without progress.

• Observe - read the symptoms from the right places. Slow page loads, lagging background workers, and database CPU during reporting windows are signals, not diagnoses.
• Identify the bottleneck - narrow down to the candidate queries that could plausibly be responsible. The investigation is per query, not per system.
• Inspect the query - read the actual execution plan against the real dataset, not the LINQ that produced it. Which indexes are used, which are not, and where the cost concentrates.
• Understand the data access shape - what rows are being touched, what fields are being read, what the row counts actually are at each step of the plan.
• Test the improvement - try the cheapest effective lever first: an index, a query rewrite, a projection, a cache. Compare the new plan to the old.
• Verify - confirm the improvement on the same workload it was meant to fix, and confirm it has not regressed any other workload that shares the table or the index.

Database performance cannot always be solved only inside SQL. The performance characteristics of a query are the product of every layer that contributes to it: the controller that receives the request, the application method that composes the query, EF Core's translation of the LINQ expression, the SQL the database receives, the indexes it can use, and the tables it has to touch. Treating any one of those layers as the sole cause - or the sole fix - is what produces the optimisations that help one workload and quietly hurt another.

The practical consequence is that the same performance problem can have several valid fixes, and the right one depends on the rest of the path. An N+1 in the application is best fixed in the application; a scan that the planner could not avoid is best fixed with an index; a repeated full-table read is best fixed with a projection or a cache. Choosing the wrong layer wastes effort and often regresses something else.

Decisions were made per query rather than as a single project. For each candidate query, the cheapest effective lever was chosen first - index, query rewrite, projection, or cache - and the others were kept in reserve. The subsections below describe the areas considered during investigation; the appropriate changes depended on each query and its access pattern.

Indexes

Indexes were considered against the actual access patterns of the queries that were doing the most work. Where a query was scanning a large range to find a small result set, the index was designed around the columns the query actually used - covering the predicates and the join keys rather than the obvious primary key. The principle was the same as everywhere else: let the plan choose the index that matches the query, not the index that matches the table.

N+1 Queries

N+1 patterns were investigated as a category of cost that is easy to introduce in code review and easy to miss in production until the data volume makes it visible. The detection was deliberately about reading the SQL the application was producing, not the LINQ that produced it. The fix was to express the relationship in a single query (with an explicit join, an Include, or a projection) rather than to leave it as a sequence of small round-trips.

Projections

Projections were used to retrieve only the fields the application actually needed. At the scale involved - millions of order-line records per scan - materialising full entities to read a handful of columns was a measurable cost, both in the database and on the way out. The projection was treated as a query-shape decision: what does this caller need, and what is the smallest shape that satisfies it?

Query Structure

Query structure was reviewed as part of the same pass. Joins were checked for selectivity, for the order in which the planner would apply them, and for the row counts they would actually produce against the real dataset. The fix was sometimes to change the join order in the LINQ, sometimes to introduce an explicit join key the planner could use, and sometimes to split a single query into two where the second was smaller and more cacheable.

Caching

Caching was considered for repeated work that was expensive to compute and tolerant of staleness. The decision to cache was per query, not per system: the same dataset might be cached for a reporting endpoint that runs every minute and queried directly for a transaction path that needs to see fresh data. Cache invalidation and staleness windows were treated as part of the design, not as a follow-up.

• Index maintenance and storage - more indexes, covering more query shapes, in exchange for fewer large scans. Each new index carries a write-time cost that has to be accepted.
• Caching complexity and invalidation - caches reduce repeated work, but the invalidation rules and the staleness window are part of the design. A cache that serves the wrong data is worse than no cache.
• Query complexity - some queries became more structured, with explicit joins and projections, in exchange for a clearer execution plan. The LINQ was less compact, the SQL was less surprising.
• Fetching less data versus API requirements - projections reduced what came back from the database, but the API contract still had to be satisfied. The choice was between returning what the caller needed and returning what the table naturally had.
• Optimising one query without negatively affecting another workload - every change was checked against the other queries that shared the same table or the same index. A fix that helps one reporting query at the cost of an OLTP query is not a fix.

The investigation and the optimization work that followed improved the application's ability to work with these data volumes. Bottlenecks that were concentrated in a small number of queries were replaced with index-friendly, projection-shaped, and where appropriate cached access patterns. N+1 patterns were considered when reducing unnecessary database round-trips.

The work addressed the query and data-access bottlenecks identified during investigation. Before-and-after response-time, CPU, and throughput benchmarks are unavailable, so no quantified improvement is claimed.

At scale, performance problems should be investigated across the entire data-access path rather than assumed to belong to any one of its layers. The database, the ORM, and the application code are not independent sources of performance - they are parts of the same path, and a problem at one layer can look like a problem at another. The investigation has to follow the path.