Database Performance Patterns
When to Use This Skill
Use this skill when:
- Designing data access layers
- Optimizing slow database queries
- Choosing between EF Core and Dapper
- Avoiding common performance pitfalls
Core Principles
- Separate read and write models - Don't use the same types for both
- Think in batches - Avoid N+1 queries
- Only retrieve what you need - No SELECT *
- Apply row limits - Always have a configurable Take/Limit
- Do joins in SQL - Never in application code
- AsNoTracking for reads - EF Core change tracking is expensive
Read/Write Model Separation (CQRS Pattern)
Read and write models are fundamentally different - they have different shapes, columns, and purposes. Don't create a single "User" entity and reuse it everywhere.
- Read models are denormalized, optimized for query efficiency, and return multiple projection types (UserProfile, UserSummary, UserDetailForAdmin)
- Write models are normalized, validation-focused, and accept strongly-typed commands (CreateUserCommand, UpdateUserCommand)
Architecture
src/
MyApp.Data/
Users/
# Read side - multiple optimized projections
IUserReadStore.cs
PostgresUserReadStore.cs
# Write side - command handlers
IUserWriteStore.cs
PostgresUserWriteStore.cs
# Read DTOs - lightweight, denormalized
UserProfile.cs
UserSummary.cs
# Write commands - validation-focused
CreateUserCommand.cs
UpdateUserCommand.cs
Orders/
IOrderReadStore.cs
IOrderWriteStore.cs
(similar structure...)
Read Store Interface
csharp
// Read models: Multiple specialized projections optimized for different use cases
public interface IUserReadStore
{
// Returns detailed profile for single-user view
Task<UserProfile?> GetByIdAsync(UserId id, CancellationToken ct = default);
// Returns lightweight info for lookups
Task<UserProfile?> GetByEmailAsync(EmailAddress email, CancellationToken ct = default);
// Returns paginated summaries - only what the list view needs
Task<IReadOnlyList<UserSummary>> GetAllAsync(int limit, UserId? cursor = null, CancellationToken ct = default);
// Boolean query - no entity needed
Task<bool> EmailExistsAsync(EmailAddress email, CancellationToken ct = default);
}
Write Store Interface
csharp
// Write model: Accepts strongly-typed commands, minimal return values
public interface IUserWriteStore
{
// Returns only the created ID - caller doesn't need the full entity
Task<UserId> CreateAsync(CreateUserCommand command, CancellationToken ct = default);
// Update validates command, returns void (success or throws)
Task UpdateAsync(UserId id, UpdateUserCommand command, CancellationToken ct = default);
// Delete is simple and explicit
Task DeleteAsync(UserId id, CancellationToken ct = default);
}
Key structural differences illustrated:
- Read store returns multiple different DTOs (UserProfile, UserSummary, bool flag)
- Write store returns minimal data (just UserId on create) or void
- Read queries are stateless projections - no tracking needed
- Write operations focus on command validation, not retrieving data afterwards
- Different databases/tables can back read vs write (eventual consistency pattern)
Always Apply Row Limits
Never return unbounded result sets. Every read method should have a configurable limit.
Pattern: Limit Parameter
csharp
public interface IOrderReadStore
{
// Limit is required, not optional
Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
CustomerId customerId,
int limit,
OrderId? cursor = null,
CancellationToken ct = default);
}
// Implementation
public async Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
CustomerId customerId,
int limit,
OrderId? cursor = null,
CancellationToken ct = default)
{
await using var connection = await _dataSource.OpenConnectionAsync(ct);
const string sql = """
SELECT id, customer_id, total, status, created_at
FROM orders
WHERE customer_id = @CustomerId
AND (@Cursor IS NULL OR created_at < (SELECT created_at FROM orders WHERE id = @Cursor))
ORDER BY created_at DESC
LIMIT @Limit
""";
var rows = await connection.QueryAsync<OrderRow>(sql, new
{
CustomerId = customerId.Value,
Cursor = cursor?.Value,
Limit = limit
});
return rows.Select(r => r.ToOrderSummary()).ToList();
}
EF Core with Pagination
csharp
public async Task<PaginatedList<OrderSummary>> GetOrdersAsync(
CustomerId customerId,
Paginator paginator,
CancellationToken ct = default)
{
var query = _context.Orders
.AsNoTracking()
.Where(o => o.CustomerId == customerId.Value)
.OrderByDescending(o => o.CreatedAt);
var totalCount = await query.CountAsync(ct);
var orders = await query
.Skip((paginator.PageNumber - 1) * paginator.PageSize)
.Take(paginator.PageSize) // Always limit!
.Select(o => new OrderSummary(
new OrderId(o.Id),
o.Total,
o.Status,
o.CreatedAt))
.ToListAsync(ct);
return new PaginatedList<OrderSummary>(
orders,
totalCount,
paginator.PageSize,
paginator.PageNumber);
}
AsNoTracking for Read Queries
EF Core's change tracking is expensive. Disable it for read-only queries.
csharp
// DO: Disable tracking for reads
var users = await _context.Users
.AsNoTracking()
.Where(u => u.IsActive)
.ToListAsync();
// DON'T: Track entities you won't modify
var users = await _context.Users
.Where(u => u.IsActive)
.ToListAsync(); // Change tracking enabled - wasteful
Configure Default Behavior
csharp
// For read-heavy applications, consider this in DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}
Then explicitly enable tracking when needed:
csharp
var user = await _context.Users
.AsTracking() // Explicit - we intend to modify
.FirstOrDefaultAsync(u => u.Id == userId);
Avoid N+1 Queries
The N+1 problem: fetching a list, then querying for each item's related data.
The Problem
csharp
// BAD: N+1 queries
var orders = await _context.Orders.ToListAsync();
foreach (var order in orders)
{
// Each iteration hits the database!
var items = await _context.OrderItems
.Where(i => i.OrderId == order.Id)
.ToListAsync();
}
Solution 1: Include (EF Core)
csharp
// GOOD: Single query with join
var orders = await _context.Orders
.AsNoTracking()
.Include(o => o.Items)
.ToListAsync();
Solution 2: Batch Query (Dapper)
csharp
// GOOD: Two queries, no N+1
const string sql = """
SELECT id, customer_id, total FROM orders WHERE customer_id = @CustomerId;
SELECT oi.* FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.customer_id = @CustomerId;
""";
using var multi = await connection.QueryMultipleAsync(sql, new { CustomerId = customerId });
var orders = (await multi.ReadAsync<OrderRow>()).ToList();
var items = (await multi.ReadAsync<OrderItemRow>()).ToList();
// Join in memory (acceptable - data already fetched)
foreach (var order in orders)
{
order.Items = items.Where(i => i.OrderId == order.Id).ToList();
}
Never Do Application-Side Joins
Joins must happen in SQL, not in C#.
csharp
// BAD: Application join - two queries, memory waste
var customers = await _context.Customers.ToListAsync();
var orders = await _context.Orders.ToListAsync();
var result = customers.Select(c => new
{
Customer = c,
Orders = orders.Where(o => o.CustomerId == c.Id).ToList() // O(n*m) in memory!
});
// GOOD: SQL join - single query
var result = await _context.Customers
.AsNoTracking()
.Include(c => c.Orders)
.ToListAsync();
// GOOD: Explicit join (Dapper)
const string sql = """
SELECT c.id, c.name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
""";
Avoid Cartesian Explosions
Multiple Include calls can cause Cartesian products.
csharp
// DANGEROUS: Can explode into millions of rows
var product = await _context.Products
.Include(p => p.Reviews) // 100 reviews
.Include(p => p.Images) // 20 images
.Include(p => p.Categories) // 5 categories
.FirstOrDefaultAsync(p => p.Id == id);
// Result: 100 * 20 * 5 = 10,000 rows transferred!
Solution: Split Queries
csharp
// GOOD: Multiple queries, no Cartesian explosion
var product = await _context.Products
.AsSplitQuery()
.Include(p => p.Reviews)
.Include(p => p.Images)
.Include(p => p.Categories)
.FirstOrDefaultAsync(p => p.Id == id);
// Result: 4 separate queries, ~125 rows total
Solution: Explicit Projection
csharp
// BEST: Only fetch what you need
var product = await _context.Products
.AsNoTracking()
.Where(p => p.Id == id)
.Select(p => new ProductDetail(
p.Id,
p.Name,
p.Description,
p.Reviews.OrderByDescending(r => r.CreatedAt).Take(10).ToList(),
p.Images.Take(5).ToList(),
p.Categories.Select(c => c.Name).ToList()))
.FirstOrDefaultAsync();
Constrain Column Sizes
Define maximum lengths in your EF Core model to prevent oversized data.
csharp
public class UserConfiguration : IEntityTypeConfiguration<User>
{
public void Configure(EntityTypeBuilder<User> builder)
{
builder.Property(u => u.Email)
.HasMaxLength(254) // RFC 5321 limit
.IsRequired();
builder.Property(u => u.Name)
.HasMaxLength(100)
.IsRequired();
builder.Property(u => u.Bio)
.HasMaxLength(500);
// For truly large content, use text type explicitly
builder.Property(u => u.Notes)
.HasColumnType("text");
}
}
Don't Build Generic Repositories
Generic repositories hide query complexity and make optimization difficult.
csharp
// BAD: Generic repository
public interface IRepository<T>
{
Task<T?> GetByIdAsync(int id);
Task<IEnumerable<T>> GetAllAsync(); // No limit!
Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate); // Can't optimize
}
// GOOD: Purpose-built read stores
public interface IOrderReadStore
{
Task<OrderDetail?> GetByIdAsync(OrderId id, CancellationToken ct = default);
Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(CustomerId id, int limit, CancellationToken ct = default);
Task<IReadOnlyList<OrderSummary>> GetPendingAsync(int limit, CancellationToken ct = default);
}
Problems with generic repositories:
- Can't optimize specific queries
- No way to enforce limits
- Hide N+1 problems
- Make it easy to fetch too much data
- Encourage lazy thinking about data access
Dapper for Read-Heavy Workloads
For complex read queries, Dapper with explicit SQL is often cleaner and faster.
csharp
public sealed class PostgresUserReadStore : IUserReadStore
{
private readonly NpgsqlDataSource _dataSource;
public PostgresUserReadStore(NpgsqlDataSource dataSource)
{
_dataSource = dataSource;
}
public async Task<UserProfile?> GetByIdAsync(UserId id, CancellationToken ct = default)
{
await using var connection = await _dataSource.OpenConnectionAsync(ct);
const string sql = """
SELECT id, email, name, bio, created_at
FROM users
WHERE id = @Id
""";