Database Performance

aaronontheweb/dotnet-skills/skills/database-performance

作者 aarononthewebe426ed93a9f3无许可证1.2K 个星标收录于 2026年10月8日更新于 2026年10月8日仓库3周前更新

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.

仅含说明Software DevelopmentData & Analytics
AI 生成的概览

提供 EF Core 与 Dapper 的数据库访问性能模式指导，涵盖读写分离、避免 N+1 查询与行数限制。

功能
该技能提供数据库访问性能模式的参考指导，面向使用 EF Core 和 Dapper 的 .NET 数据访问层。内容涵盖读写模型分离、行数限制与分页、使用 AsNoTracking、避免 N+1 查询、在 SQL 而非应用代码中做连接、防止笛卡尔积膨胀、约束列长度以及避免通用仓储。产出为书面模式、接口示例、代码片段和对比表格，而非可运行脚本。
适用场景
适用于设计数据访问层、优化缓慢查询，或在 EF Core 与 Dapper 之间做选择时。也适合审查现有数据访问代码中的常见性能陷阱。
运行要求
无需脚本或工具，仅为说明性内容。示例假定使用 .NET 环境并配合 EF Core 与 Dapper，但无需安装或执行任何内容。

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

  1. Separate read and write models - Don't use the same types for both
  2. Think in batches - Avoid N+1 queries
  3. Only retrieve what you need - No SELECT *
  4. Apply row limits - Always have a configurable Take/Limit
  5. Do joins in SQL - Never in application code
  6. 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 casespublic 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 valuespublic 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);}
// Implementationpublic 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 readsvar users = await _context.Users    .AsNoTracking()    .Where(u => u.IsActive)    .ToListAsync();
// DON'T: Track entities you won't modifyvar users = await _context.Users    .Where(u => u.IsActive)    .ToListAsync();  // Change tracking enabled - wasteful

Configure Default Behavior

csharp
// For read-heavy applications, consider this in DbContextprotected 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 queriesvar 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 joinvar orders = await _context.Orders    .AsNoTracking()    .Include(o => o.Items)    .ToListAsync();

Solution 2: Batch Query (Dapper)

csharp
// GOOD: Two queries, no N+1const 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 wastevar 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 queryvar 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 rowsvar 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 explosionvar 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 needvar 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 repositorypublic 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 storespublic 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            """;
        var row = await connection.QuerySingleOrDefaultAsync<UserRow>(            sql, new { Id = id.Value });
        return row?.ToUserProfile();    }
    // Internal row type for Dapper mapping    private sealed class UserRow    {        public Guid id { get; set; }        public string email { get; set; } = null!;        public string name { get; set; } = null!;        public string? bio { get; set; }        public DateTime created_at { get; set; }
        public UserProfile ToUserProfile() => new(            Id: new UserId(id),            Email: new EmailAddress(email),            Name: new PersonName(name),            Bio: bio,            CreatedAt: new DateTimeOffset(created_at, TimeSpan.Zero));    }}

When to Use EF Core vs Dapper

ScenarioRecommendation
Simple CRUDEF Core
Complex read queriesDapper
Writes with validationEF Core
Bulk operationsDapper or raw SQL
Reporting/analyticsDapper
Domain-heavy writesEF Core

You can use both in the same project - EF Core for writes, Dapper for reads.

Quick Reference

Anti-PatternSolution
No row limitAdd limit parameter to every read method
SELECT *Project only needed columns
N+1 queriesUse Include or batch queries
Application joinsDo joins in SQL
Cartesian explosionUse AsSplitQuery or projection
Tracking read-only dataUse AsNoTracking
Generic repositoryPurpose-built read/write stores
Unbounded stringsConfigure MaxLength in model

Resources

来源与署名

来源：aaronontheweb/dotnet-skills位于skills/database-performance提交e426ed9

许可证： 无许可证

内容归原作者所有。SourceWeft 从公开仓库中收录这些内容。

举报或申请下架

更多来自 aaronontheweb/dotnet-skills 的技能

Snapshot Testing

aaronontheweb

介绍在 .NET 中使用 Verify 进行快照测试，用于审批 API 接口、HTTP 响应、邮件和序列化输出。

Software Development1.2K3周前更新

Serialization

aaronontheweb

指导 .NET 开发者选择序列化格式，并迁移到基于架构、兼容 AOT 的序列化方案。

Software Development1.2K3周前更新

Microsoft Extensions Configuration

aaronontheweb

介绍 Microsoft.Extensions.Options 配置模式：强类型设置绑定、启动时验证以及选项生命周期。

Software Development1.2K3周前更新

Package Management

aaronontheweb

指导使用 dotnet CLI 命令和中央包管理（CPM）来管理 .NET 项目中的 NuGet 包。

Software Development1.2K3周前更新

Mjml Email Templates

aaronontheweb

构建可编译为跨客户端 HTML 的响应式 MJML 邮件模板，包含 .NET 渲染器与变量替换。

Communication1.2K3周前更新

Efcore Patterns

aaronontheweb

关于 Entity Framework Core 模式的指导：默认无跟踪查询、迁移、查询拆分和批量操作。

Software Development1.2K3周前更新

更多Software Development技能

Playground

anthropics

精选

构建自包含的交互式 HTML 演练场，包含控件、实时预览和可复制的提示词输出。

Software Development37K今天更新

M5 Onboard

anthropics

精选

通过 USB 检测 M5Stack ESP32 开发板，刷写 UIFlow 2.0 固件并安装 MicroPython 应用包。

Software Development37K今天更新

Cardputer Buddy

anthropics

精选

指导迭代 Cardputer-Adv 的 MicroPython 应用包：添加应用、通过 USB 串口推送文件、查看日志并运行 REPL 命令。

Software Development37K今天更新

Web Design Guidelines

vercel-labs

精选

依据从远程来源获取的 Web Interface Guidelines 审查 UI 代码，并以 file:line 形式报告问题。

Software Development32K5周前更新

Vercel React Native Skills

vercel-labs

精选

React Native 与 Expo 最佳实践规则，涵盖列表性能、动画、导航、UI 模式、状态与 monorepo 配置。

Software Development32K5周前更新

Vercel React Best Practices

vercel-labs

精选

Vercel 的 React 与 Next.js 性能指南，以 70 条规则的形式用于编写、审查和重构代码。

Software Development32K5周前更新