Database Optimization

name: database-optimization description: "[DevOps & Infra] 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.

Summary

Goal: Optimize database queries, indexes, and data access patterns for MongoDB, SQL Server, and PostgreSQL in EasyPlatform.

• N+1 queries — Use eager loading (loadRelatedEntities) or batch load with GetByIdsAsync
• Select projections — Fetch only needed columns, not entire entities
• Parallel queries — Run independent queries with Util.ParallelAsync() instead of sequential awaits
• Indexing — Add indexes for frequently filtered/sorted columns; use compound indexes for multi-field queries
• Pagination — Always use PlatformCqrsPagedQuery for list endpoints

Key Principles:

• Profile before optimizing — identify actual bottlenecks with evidence
• Use platform repository patterns (expressions, projections) over raw queries
• Batch related entity loads to eliminate N+1; never query inside loops

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

// GOOD: Batch load related entities
var employees = await repo.GetAllAsync(e => e.CompanyId == companyId, ct);
var deptIds = employees.Select(e => e.DepartmentId).Distinct().ToList();
var departments = await deptRepo.GetByIdsAsync(deptIds, ct);
var deptMap = departments.ToDictionary(d => d.Id);
employees.ForEach(e => e.Department = deptMap.GetValueOrDefault(e.DepartmentId));

Select Only Needed Columns

// BAD: Fetching entire entity when only ID needed
var employee = await repo.GetByIdAsync(id, ct);
return employee.Id;

// GOOD: Projection to fetch only needed data
var employeeId = await repo.FirstOrDefaultAsync(
    query => query
        .Where(Employee.UniqueExpr(userId, companyId))
        .Select(e => e.Id),  // Only fetch ID column
    ct);

Parallel Independent Queries

// BAD: Sequential queries that could run in parallel
var count = await repo.CountAsync(filter, ct);
var items = await repo.GetAllAsync(filter, ct);
var stats = await statsRepo.GetAsync(companyId, ct);

// GOOD: Parallel tuple queries
var (count, items, stats) = await (
    repo.CountAsync((uow, q) => queryBuilder(uow, q), ct),
    repo.GetAllAsync((uow, q) => queryBuilder(uow, q).PageBy(skip, take), ct),
    statsRepo.GetAsync(companyId, ct)
);

Query Optimization Patterns

GetQueryBuilder for Reusable Queries

protected override async Task<Result> HandleAsync(Query req, CancellationToken ct)
{
    // Define query once, reuse for count and data
    var queryBuilder = repo.GetQueryBuilder((uow, q) => q
        .Where(Employee.OfCompanyExpr(RequestContext.CurrentCompanyId()))
        .WhereIf(req.Statuses.Any(), e => req.Statuses.Contains(e.Status))
        .WhereIf(req.DepartmentId.IsNotNullOrEmpty(), e => e.DepartmentId == req.DepartmentId)
        .PipeIf(req.SearchText.IsNotNullOrEmpty(), q =>
            fullTextSearch.Search(q, req.SearchText, Employee.SearchColumns())));

    // Parallel execution
    var (total, items) = await (
        repo.CountAsync((uow, q) => queryBuilder(uow, q), ct),
        repo.GetAllAsync((uow, q) => queryBuilder(uow, q)
            .OrderByDescending(e => e.CreatedDate)
            .PageBy(req.SkipCount, req.MaxResultCount), ct)
    );

    return new Result(items, total);
}

Conditional Filtering with WhereIf

// Builds efficient query with only needed conditions
var query = repo.GetQueryBuilder((uow, q) => q
    .Where(e => e.CompanyId == companyId)  // Always applied
    .WhereIf(status.HasValue, e => e.Status == status)  // Only if provided
    .WhereIf(deptIds.Any(), e => deptIds.Contains(e.DepartmentId))
    .WhereIf(dateFrom.HasValue, e => e.CreatedDate >= dateFrom)
    .WhereIf(dateTo.HasValue, e => e.CreatedDate <= dateTo));

Full-Text Search Optimization

// Define searchable columns in entity
public static Expression<Func<Employee, object?>>[] DefaultFullTextSearchColumns()
    => [e => e.FullName, e => e.Email, e => e.EmployeeCode, e => e.FullTextSearch];

// Use full-text search service
.PipeIf(searchText.IsNotNullOrEmpty(), q => fullTextSearch.Search(
    q,
    searchText,
    Employee.DefaultFullTextSearchColumns(),
    fullTextAccurateMatch: true,  // Exact phrase match
    includeStartWithProps: [e => e.FullName, e => e.EmployeeCode]  // Prefix matching
));

Index Recommendations

MongoDB Indexes

// Single field index - for equality queries
{ "CompanyId": 1 }

// Compound index - for filtered queries
{ "CompanyId": 1, "Status": 1, "CreatedDate": -1 }

// Text index - for full-text search
{ "FullName": "text", "Email": "text", "EmployeeCode": "text" }

// Sparse index - for optional fields
{ "ExternalId": 1, sparse: true }

SQL Server / PostgreSQL Indexes

-- Covering index for common query
CREATE INDEX IX_Employee_Company_Status
ON Employees (CompanyId, Status)
INCLUDE (FullName, Email, CreatedDate);

-- Filtered index for active records
CREATE INDEX IX_Employee_Active
ON Employees (CompanyId, CreatedDate)
WHERE Status = 'Active' AND IsDeleted = 0;

-- Full-text index
CREATE FULLTEXT INDEX ON Employees (FullName, Email)
KEY INDEX PK_Employees;

Pagination Best Practices

// GOOD: Keyset pagination for large datasets (cursor-based)
var items = await repo.GetAllAsync(q => q
    .Where(e => e.CompanyId == companyId)
    .Where(e => e.Id > lastId)  // Cursor
    .OrderBy(e => e.Id)
    .Take(pageSize), ct);

// GOOD: Offset pagination for moderate datasets
var items = await repo.GetAllAsync(q => q
    .Where(filter)
    .OrderByDescending(e => e.CreatedDate)
    .PageBy(skip, take), ct);  // Platform helper

// BAD: Skip without limit (fetches all then skips)
var items = await repo.GetAllAsync(q => q.Skip(1000), ct);

Bulk Operations

// Bulk insert
await repo.CreateManyAsync(entities, ct);

// Bulk update (with optimization flags)
await repo.UpdateManyAsync(
    entities,
    dismissSendEvent: true,  // Skip entity events for performance
    checkDiff: false,        // Skip change detection
    ct);

// Bulk delete by expression
await repo.DeleteManyAsync(e => e.Status == Status.Deleted && e.DeletedDate < cutoffDate, ct);

Performance Analysis Workflow

Phase 1: Identify Slow Queries

1. Check application logs for slow query warnings
2. Review query patterns in handlers
3. Look for N+1 patterns (loops with DB calls)

Phase 2: Analyze Query Plan

// MongoDB - Check indexes used
db.employees.find({ companyId: "x", status: "Active" }).explain("executionStats")

// SQL Server - Check execution plan
SET STATISTICS IO ON
SELECT * FROM Employees WHERE CompanyId = 'x' AND Status = 'Active'

Phase 3: Optimize

1. Add missing indexes
2. Use eager loading for related entities
3. Add projections for partial data needs
4. Parallelize independent queries
5. Implement caching for frequently accessed data

Optimization Checklist

• N+1 queries identified and fixed?
• Eager loading for related entities?
• Projections for partial data needs?
• Parallel queries for independent operations?
• Proper indexes for filter/sort columns?
• Pagination implemented correctly?
• Full-text search for text queries?
• Bulk operations for batch processing?

Anti-Patterns

• Loading entire collections: Always filter and paginate
• Fetching unused data: Use projections
• Sequential independent queries: Use parallel tuple queries
• Index on every column: Only index frequently queried fields
• Skip without ordering: Always order before pagination

IMPORTANT Task Planning Notes

• Always plan and break many small todo tasks
• Always add a final review todo task to review the works done at the end to find any fix or enhancement needed