SkillAgentSearch skills...

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.

Install / Use

npx skills add Aaronontheweb/dotnet-skills --skill database-performance

Installs into whichever agent you are using.

About this skill
📄

SKILL.md

Installable skill definition

Quality Score

93/100

Supported Platforms

Universal

Our assessment of database-performance

database-performance scores 93/100 on our quality scale, 137th of 608 Data & Analytics skills we index (top 23%).

Its SKILL.md is 14 KB long, well organised into 26 sections with 18 code examples: a thorough specification that gives an agent plenty to work with.

With 1,183 GitHub stars, it is one of the more widely adopted skills in the catalogue.

Substance
30/30
Structure
20/20
Description
15/15
Adoption
13/20
Freshness
15/15

Maintenance, license and trust

  • The repository was last updated about 2 months ago, so database-performance is actively maintained.
  • Our last check on 2026-11-17 found the source still online.
  • It is released under the MIT license, a permissive license that allows use, modification and commercial use with attribution.
  • Its trust signals score 100/100, with no cautions. These come from repository metadata, not a code audit — read the skill file before letting an agent act on it.

Safety scan

No issues found

Our scan of the whole file found no instruction hijacking, hidden characters, credential access, data exfiltration or destructive commands. An AI review of the same text found nothing harmful.

AI review by kimi-k2.7-code on 2026-10-04. Automated pattern scan on 2026-10-04. It catches known dangerous patterns, not every risk — read a skill before letting an agent act on it.

database-performance compared with similar skills

All 4 of these similar skills score higher than database-performance; compare them before choosing.

SkillScoreStarsUpdatedFormat
database-performance (this skill)by Aaronontheweb931.2k2mo agoSKILL.md
taste-skillby Leonxlnx100117.6k9d agoCLAUDE.md
Claude-Code-Game-Studiosby Donchitos10039.9k8d agoCLAUDE.md
x402by browser-use100121.0k4d agoSKILL.md
algorithmic-artby anthropics100177.9k56d agoSKILL.md

Frequently asked questions

How do I install database-performance?
Run npx skills add Aaronontheweb/dotnet-skills --skill database-performance. The install tabs above show the steps for each supported agent.
Which AI agents does database-performance work with?
It is written for Universal, as a SKILL.md file. Other agents that read the same format can often use it too.
Is database-performance safe to use?
Our scan of the whole file found no instruction hijacking, hidden characters, credential access, data exfiltration or destructive commands. An AI review of the same text found nothing harmful. It is MIT-licensed and scores 100/100 on trust signals. Skills are instructions an agent will follow, so read the file before installing it and do not approve commands you do not understand.
Is database-performance still maintained?
The repository was last updated about 2 months ago, so database-performance is actively maintained.

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

  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 =

Truncated for display — read the full file on GitHub.

Related Skills

View on GitHub
GitHub Stars1.2k
CategoryData
Updated2mo ago
Forks109

Languages

Shell

Trust signals

100/100

From repository metadata: license, adoption, age and documentation. Not a code audit — see the Safety scan above for what the skill file itself contains.

No cautions