数据与分析
database-performance
Database access patterns for performance. Separate read/write models, avoid N+1 queries, use AsNoTracking, apply row limits, and never do application-side joins. Works with EF Core and Dapper.
Aaronontheweb · 已有 AI 审核记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add Aaronontheweb/dotnet-skills --skill database-performance技能说明
name: database-performance description: Database access patterns for performance. Separate read/write models, avoid N+1 queries, use AsNoTracking, apply row limits, and never do application-side joins. Works with EF Core and Dapper. invocable: false tags: [cqrs, performance, patterns]
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
// 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
// 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
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
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.
// DO: Disable tracking for reads
var users = await _context.Users
.AsNoTracking()
.Where(u => u.IsAct
阅读完整来源 ↗