database-optimization

name	database-optimization
description	Use when optimizing database queries, indexes, N+1 problems, slow queries, or analyzing query performance. Triggers on keywords like "slow query", "N+1", "index", "query optimization", "database performance", "eager loading".
allowed-tools	Read, Write, Edit, Bash, Grep, Glob, Task, TodoWrite
infer	true

Database Optimization

Expert database performance agent for EasyPlatform. Optimizes queries, indexes, and data access patterns for MongoDB, SQL Server, and PostgreSQL.

Common Performance Issues

N+1 Query Problem

// BAD: N+1 queries - one query per employee's department
var employees = await repo.GetAllAsync(e => e.CompanyId == companyId, ct);
foreach (var emp in employees)
{
    var dept = await deptRepo.GetByIdAsync(emp.DepartmentId, ct);  // N queries!
}

// GOOD: Eager loading with loadRelatedEntities
var employees = await repo.GetAllAsync(
    e => e.CompanyId == companyId,
    ct,
    loadRelatedEntities: e => e.Department);  // Single query with join


 employees =  repo.GetAllAsync(e => e.CompanyId == companyId, ct);
 deptIds = employees.Select(e => e.DepartmentId).Distinct().ToList();
 departments =  deptRepo.GetByIdsAsync(deptIds, ct);
 deptMap = departments.ToDictionary(d => d.Id);
employees.ForEach(e => e.Department = deptMap.GetValueOrDefault(e.DepartmentId));