MCPInstall

database-performance

by aarononthewebData & Databases

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.

Installs
672
Last 24h
—
Trending rank
—
Security
Not audited

Install

Works with every agent the skills CLI supports.

npx skills add https://github.com/aaronontheweb/dotnet-skills --skill database-performance
  1. 01

    Install

    Run the command in your project. The CLI asks which agents to install for.

  2. 02

    Describe the task

    Ask your agent for something this skill covers. It loads database-performance automatically when relevant.

  3. 03

    Review the result

    The agent follows the SKILL.md steps, scripts and references bundled with the skill.

SKILL.md

The instructions your agent reads when the skill is triggered.

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

// 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.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

// For read-heavy applications, consider this in DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

Then explicitly enable tracking when needed:

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

// 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)

// GOOD: Single query with join
var orders = await _context.Orders
    .AsNoTracking()
    .Include(o => o.Items)
    .ToListAsync();

Solution 2: Batch Query (Dapper)

// 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#.

// 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.

// 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

// 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

// 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.

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.

// 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.

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

FAQ

What does the database-performance skill do?

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.

How do I install database-performance?

Run npx skills add https://github.com/aaronontheweb/dotnet-skills --skill database-performance in your project. The skills CLI detects the agents you use and copies the skill into the right folder. For Claude Code you can also place the skill folder at ~/.claude/skills/database-performance/ (personal) or .claude/skills/database-performance/ (project).

Which AI agents can use database-performance?

Any agent that supports the open Agent Skills format (a folder with a SKILL.md file), including Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, Windsurf, Cline, OpenCode and more.

Is database-performance safe to install?

No partner security audit is available yet. Skills can run commands and scripts, so read the SKILL.md and any bundled files before installing.

Who maintains database-performance?

It is published by aaronontheweb in aaronontheweb/dotnet-skills. It has 672 recorded installs.

More from aaronontheweb/dotnet-skills

View all →

modern-csharp-coding-standards

aaronontheweb/dotnet-skills

Write modern, high-performance C code using records, pattern matching, value objects, async/await, Span /Memory , and best-practice API design patterns. Emphasizes functional-style programming with C 12+ features.

Backend & APIs2.2K installs

csharp-concurrency-patterns

aaronontheweb/dotnet-skills

Choosing the right concurrency abstraction in .NET - from async/await for I/O to Channels for producer/consumer to Akka.NET for stateful entity management. Avoid locks and manual synchronization unless absolutely necessary.

Backend & APIs1.6K installs

efcore-patterns

aaronontheweb/dotnet-skills

Entity Framework Core best practices including NoTracking by default, query splitting for navigation collections, migration management, dedicated migration services, and common pitfalls to avoid.

Code Quality & Workflow1.6K installs

dependency-injection-patterns

aaronontheweb/dotnet-skills

Organize DI registrations using IServiceCollection extension methods. Group related services into composable Add methods for clean Program.cs and reusable configuration in tests.

Security676 installs

api-design

aaronontheweb/dotnet-skills

Design stable, compatible public APIs using extend-only design principles. Manage API compatibility, wire compatibility, and versioning for NuGet packages and distributed systems.

Backend & APIs667 installs

dotnet-project-structure

aaronontheweb/dotnet-skills

Modern .NET project structure including .slnx solution format, Directory.Build.props, central package management, SourceLink, version management with RELEASENOTES.md, and SDK pinning with global.json.

Backend & APIs666 installs

Popular data & databases skills

View category →

supabase-postgres-best-practices

supabase/agent-skills

Postgres best practices maintained by Supabase, for Postgres running anywhere. Load this skill BEFORE writing or changing anything that lives in a Postgres database: creating or altering tables and columns (including choosing column types), schema design, migrations and declarative schema files, RLS policies and the tests that verify them, indexes, triggers, database functions, queues and scheduled jobs (pgcron, pgmq), vector/semantic search (pgvector), and restoring dumps (pgrestore) or importing data. Also load it when diagnosing slow queries, high CPU, timeouts, EXPLAIN plans, connection exhaustion, locking, bloat, or rows visible to the wrong user or tenant. This is not just a performance guide — schema, migration, security, and SQL authoring tasks need these rules too, even for a one-column change or a single query.

OfficialData & Databases462.2K installs

prisma-client-api

prisma/skills

Prisma Client API reference covering model queries, filters, operators, and client methods. Use when writing database queries, using CRUD operations, filtering data, or configuring Prisma Client. Triggers on "prisma query", "findMany", "create", "update", "delete", "$transaction".

OfficialData & Databases393.5K installs

prisma-database-setup

prisma/skills

Deprecated compatibility name for prisma-orm-setup. Use only when a user or existing integration explicitly invokes prisma-database-setup; otherwise select prisma-orm-setup.

OfficialData & Databases392.3K installs

prisma-cli

prisma/skills

Prisma ORM 7 CLI reference for existing Prisma 7 applications or explicitly selected Prisma 7 workflows. Covers prisma init, generate, migrate, db, dev, studio, and mcp. For setup, connection repair, or Prisma 8, use prisma-orm-setup; not the Prisma Platform CLI.

OfficialData & Databases390.8K installs

prisma-postgres

prisma/skills

Deprecated compatibility name for prisma-postgres-setup. Use only when a user or existing integration explicitly invokes prisma-postgres; otherwise select prisma-postgres-setup.

OfficialData & Databases384.4K installs

prisma-driver-adapter-implementation

prisma/skills

Required reference for Prisma ORM 7 SQL driver adapter work. Use when implementing or modifying adapters, adding database drivers, or touching SqlDriverAdapter, Transaction, savepoint, result mapping, or DriverAdapterError behavior. Covers current transaction lifecycle, optional savepoint hooks, original database-error preservation, and verification.

OfficialData & Databases378.9K installs