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

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

• Check application logs for slow query warnings

• Review query patterns in handlers

• 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

• Add missing indexes

• Use eager loading for related entities

• Add projections for partial data needs

• Parallelize independent queries

• 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