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