db-optimization

Files (1)

• SKILL.md

SKILL.md (first lines)

# Database optimization

## When to use

- You suspect N+1 (loop with DB queries)
- Slow endpoints that list data
- Missing indexes on filtered/sorted fields
- Loading full entities when only a few fields are needed
- Missing pagination on an endpoint returning a collection

## Analysis procedure

1. Find all loops (`foreach`, `for`) that call `await _db.` inside -> N+1.
2. Find `.ToListAsync()` without pagination (missing `.Skip().Take()`) -> missing pagination.
3. Find `.Include()` when endpoint returns DTO -> replace with `.Select()`.
4. Find readonly queries without `.AsNoTracking()`.
5. Check migrations - whether new fields used in `WHERE`/`ORDER BY` have `HasIndex`.

## Remediation patterns

### N+1 -> Include or projection

```csharp
// [BAD] N+1
var recordings = await _db.Recordings.ToListAsync();
foreach (var r in recordings)
    r.Tags = await _db.Tags.Where(t => t.RecordingId == r.Id).ToListAsync();

// [OK] Eager loading
var recordings = await _db.Recordings.Include(r => r.Tags).AsNoTracking().ToListAsync();

// [BEST] Projection (best - only required columns)
var dtos = await _db.Recordings
    .AsNoTracking()
    .Select(r => new RecordingDto(r.Id, r.Name, r.Tags.Select(t => t.Name)))
    .ToListAsync();
```

### Mandatory pagination

```csharp
// [BAD] Missing pagination
var all = await _db.Recordings.ToListAsync();

// [OK]
var page = await _db.Recordings
    .AsNoTracking()
    .Where(r => r.UserId == userId)
    .OrderBy(r => r.CreatedAt)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .Select(r => new RecordingDto(...))
    .ToListAsync(ct);
```

### AsNoTracking for readonly

```csharp
// [OK] Always for queries that do not modify entities
var dto = await _db.Recordings
    .AsNoTracking()
    .FirstOrDefaultAsync(r => r.Id == id, ct);
```

### Indexes in migration

```csharp
// In entity configuration (OnModelCreating / IEntityTypeConfiguration)
builder.HasIndex(r => r.CreatedAt);                        // ORDER BY
builder.HasIndex(r => new { r.UserId, r.Status });         // composite WHERE
builder.HasIndex(r => r.Name).HasMethod("gin");            // PostgreSQL full-text (optional)
```

### AnyAsync instead of Count > 0

```csharp
// [BAD] Loads COUNT(*)
if (await _db.Recordings.CountAsync(r => r.Id == id) > 0)

// [OK]
if (await _db.Recordings.AnyAsync(r => r.Id == id, ct))
```

## When to add an index

| Situation                         | Action                            |
| --------------------------------- | --------------------------------- |
| Field in `.Where()` on large table | `HasIndex(r => r.Field)`         |
| Field in `.OrderBy()`             | `HasIndex(r => r.Field)`          |
| Combination of fields in WHERE    | `HasIndex(r => new { r.A, r.B })` |
| FK - verify EF did not skip it    | Verify in migration               |

## Checklist

- [ ] No loops with DB queries (N+1)
- [ ] Readonly queries use `.AsNoTracking()`
- [ ] Lists have pagination (`.Skip().Take()`)
- [ ] New filtered/sorted fields have index in migration
- [ ] `.Select()` used instead of `.Include()` when endpoint returns DTO
- [ ] `AnyAsync` instead of `Count() > 0`

From ArkadiuszSieracki/mastermind-agentic-sdlc-vscode-copilot, Apache-2.0.

More from ArkadiuszSieracki/mastermind-agentic-sdlc-vscode-copilot