Database & I/O Tuning
10 min readDatabase & I/O Tuning
TL;DR
In almost every business application the database is the bottleneck, and the top three causes are always the same: N+1 queries, a missing index, and fetching far more data than the operation needs. All three are invisible in the C# source — an ORM makes a round trip look like a property access — which is why the diagnostic skill is reading the generated SQL and the execution plan rather than reading the C#. Network calls follow the same pattern one level up: sequential when they could be concurrent, and unbounded when they should have timeouts.
How it works
N+1 — the defect that ships every time
// 1 query for the orders, then 1 MORE per order for its customer.
// 100 orders = 101 round trips. Each is a network hop plus a query plan lookup.
var orders = await db.Orders.Where(o => o.IsActive).ToListAsync();
foreach (var order in orders)
Console.WriteLine(order.Customer.Name); // lazy load fires here
// One query with a join.
var orders = await db.Orders
.Where(o => o.IsActive)
.Include(o => o.Customer)
.ToListAsync();It ships because it is fast on a 10-row development database and because the C# gives no visual signal that a network round trip is happening. The detection is to log the generated SQL in development and count queries per request — EF Core will log every one, and a request issuing 200 queries announces itself immediately.
Note the opposite failure too: Include chains across several collections produce a cartesian explosion, where a parent with 20 children and 20 tags returns 400 rows. AsSplitQuery() is the fix there — a trade of one round trip for a much smaller result set.
Indexes and why yours is not being used
An index is a B-tree over one or more columns, turning an O(n) table scan into an O(log n) seek. The rules that decide whether the planner can use it:
SARGability — the predicate must be expressible as a range over the indexed column. Wrapping the column in a function destroys that:
-- Cannot use an index on CreatedAt: the column is inside a function.
WHERE YEAR(CreatedAt) = 2026
-- SARGable: a plain range the B-tree can seek.
WHERE CreatedAt >= '2026-01-01' AND CreatedAt < '2027-01-01'The same applies to LIKE '%foo' (leading wildcard — no usable prefix), implicit type conversions, and WHERE column + 0 = x.
Column order in a composite index — an index on (TenantId, CreatedAt) serves WHERE TenantId = x, and WHERE TenantId = x AND CreatedAt > y, but not WHERE CreatedAt > y alone. It is a phone book sorted by surname then first name: useless for finding everyone called "James".
Selectivity — an index on a column with three distinct values over a million rows will usually be ignored, because a scan is cheaper than seeking and then looking up a third of the table.
Covering indexes — if the index contains every column the query needs, the database never touches the table at all. That is often the difference between a fast query and a slow one with an otherwise identical plan.
The cost is that every index slows writes and consumes storage, so indexes are a read-versus-write trade, not free.
Fetch less
SELECT */ returning full entities when three columns are needed — project to a DTO instead.- No pagination — an endpoint that returns "all" works until the table grows.
- Tracking queries for read-only work:
AsNoTracking()skips building EF's change-tracking graph, which for large read-only result sets is a substantial saving in both time and allocation. - Filtering in memory:
.ToList().Where(...)fetches the whole table and filters in C#. Keep theWherebefore the materialisation so it becomes SQL.
Batching, round trips, and connection pooling
Latency per round trip dominates. 1,000 individual inserts at 1 ms each is a second of pure waiting; one batched insert is a few milliseconds. SaveChanges already batches, AddRange beats a loop of Add, and for bulk work a dedicated bulk-copy path beats the ORM by an order of magnitude.
Connection pooling means new SqlConnection is cheap — you get a pooled connection rather than a new TCP handshake and authentication — which is why the correct pattern is to open late, close early, and never hold a connection across a long operation. Pool exhaustion presents as timeouts on connection acquisition rather than on the query, and the usual cause is undisposed connections or long-held transactions.
Concurrent I/O
// Sequential: total time is the SUM of the calls.
var user = await GetUserAsync(id);
var orders = await GetOrdersAsync(id);
var prefs = await GetPreferencesAsync(id);
// Concurrent: total time is the MAX. Only valid when they are independent.
var userTask = GetUserAsync(id);
var ordersTask = GetOrdersAsync(id);
var prefsTask = GetPreferencesAsync(id);
await Task.WhenAll(userTask, ordersTask, prefsTask);Two caveats: a single EF Core DbContext does not support concurrent operations, so this pattern needs separate contexts or a factory; and unbounded concurrency against a dependency is how you cause an outage downstream, so fan-out needs a limit.
Every outbound call needs a timeout, and ideally a circuit breaker. Without one, a slow dependency does not just make your endpoint slow — it holds your threads and connections until the whole service stops responding, converting someone else's degradation into your outage.
A: It is issuing one query to fetch a collection and then one additional query per item to fetch a related entity, so a hundred rows become a hundred and one round trips, each paying network latency and query overhead. It reaches production because the C# gives no visual signal — accessing order.Customer looks like a property read, not a network call — and because it performs perfectly well against a development database with ten rows and no latency. Detection is to log the generated SQL and count queries per request in development. The fix is eager loading with a join, or projecting exactly the fields needed, taking a hundred and one round trips down to one.
A: SARGable means "search-argument-able" — the predicate can be expressed as a range over an indexed column, so the database can seek within the B-tree instead of scanning the whole table. Wrapping the column in a function destroys it: WHERE YEAR(CreatedAt) = 2026 must evaluate the function for every row, whereas the equivalent range WHERE CreatedAt >= '2026-01-01' AND CreatedAt < '2027-01-01' is a direct seek. The same applies to a leading wildcard in LIKE, to arithmetic on the column, and to implicit type conversions caused by comparing a column to a differently-typed parameter. It matters because it is the difference between O(log n) and O(n), and because the index exists and simply is not being used, which makes it invisible unless you read the execution plan.
A: Because the index is sorted by the first column, then within that by the second, and so on — exactly like a phone book ordered by surname and then first name. An index on (TenantId, CreatedAt) therefore serves queries filtering on TenantId alone or on TenantId plus a CreatedAt range, but is useless for a query filtering only on CreatedAt, just as the phone book cannot efficiently find everyone named James. The practical rule is to lead with the column used for equality filtering in the most queries, and to place range predicates after equality predicates, since everything after a range column loses its ordering benefit.
A: First the execution plan, to see whether the index is actually being used or whether the planner chose a scan. Common causes are a non-SARGable predicate wrapping the column in a function or an implicit type conversion, a composite index whose leading column is not in the predicate, or low selectivity where the planner correctly decides a scan is cheaper than seeking and then looking up a large fraction of the table. If the index is being used, the remaining cost is often key lookups — the index locates the rows but the query needs columns it does not contain, forcing a trip to the table per row — which a covering index fixes. Stale statistics are the other classic cause, since the planner is optimising against a distribution that no longer exists.
A: An index that contains every column the query touches — in the key or as included columns — so the database can answer entirely from the index and never reads the table at all. It transforms queries where the index correctly locates a large number of rows but each row then requires a lookup into the table to fetch columns the index lacks; those lookups are random reads and frequently dominate the query cost. Adding the missing columns as included columns converts many random reads into one contiguous index scan. The cost is a larger index and slower writes, so it is a targeted fix for a known hot query, not a default.
A: Start them all before awaiting any, then await them together with Task.WhenAll, so the total time is the slowest call rather than the sum of all of them. Two caveats matter: the calls must genuinely be independent, since one that depends on another's result cannot be parallelised, and a single EF Core DbContext does not support concurrent operations, so parallel database work needs separate contexts from a factory. I would also bound the concurrency rather than fanning out without a limit, because unbounded parallel requests are how you turn your own optimisation into someone else's outage, and I would set a timeout on each call so one slow dependency cannot hold the whole set open.
A: Because without one, a slow or hung dependency does not merely make one request slow — it holds a thread and a connection indefinitely, and under load those accumulate until the pool is exhausted and the service stops responding to everything, including requests that never touch that dependency. That converts a partial degradation somewhere else into a total outage of your own, and it is the standard cascading-failure mechanism. A timeout bounds the damage to the requests that actually needed the dependency, and a circuit breaker goes further by failing fast once a dependency is known to be unhealthy, so you stop spending resources discovering the same thing repeatedly.
A: For any read-only query whose entities will not be modified and saved, which is most read paths in a typical service. By default EF builds a change-tracking graph for every materialised entity, taking a snapshot so it can compute a diff later — work and allocation that is pure waste when nothing will be saved, and which grows with the result set. AsNoTracking skips it, measurably reducing both time and memory on large reads. The corollary is that you must not use it when you intend to modify and persist the entities, because there is nothing tracking the changes. Projecting directly to a DTO gives the same benefit and additionally avoids fetching columns you do not need.
Key takeaways
- The database is usually the bottleneck, and it is usually N+1, a missing index, or over-fetching.
- N+1 is invisible in C# and fast on dev data — log the SQL and count queries per request.
- SARGability: a function around the column, a leading
LIKEwildcard, or an implicit conversion kills the index. - Composite index order is a phone book — leading column must be in the predicate.
- Covering indexes remove key lookups, often the actual cost in an otherwise "indexed" query.
- Round trips dominate: batch writes, project to DTOs, paginate, and use
AsNoTrackingfor read-only work. - Independent calls →
Task.WhenAll(bounded, and not on oneDbContext). Every outbound call needs a timeout.