ASP.NET Core Interview Question #193

How can database access affect ASP.NET Core performance?

Performance, Caching & HTTP Senior Advanced

Quick Interview Answer

Database operations are often a major source of application latency. Common problems include retrieving unnecessary columns or rows, missing indexes, N+1 queries, excessive SaveChanges calls, long transactions, and unnecessary change tracking.

Detailed Explanation

Use profiling and generated SQL to identify problems. Apply pagination, appropriate indexes, projections, AsNoTracking for read-only EF Core queries, batching where appropriate, and efficient query shapes.

Caching can reduce repeated reads, but it should not be used to hide fundamentally inefficient database access.

Code Example

var products = await db.Products
    .AsNoTracking()
    .Where(p => p.IsPublished)
    .OrderByDescending(p => p.Id)
    .Select(p => new ProductListDto
    {
        Id = p.Id,
        Name = p.Name
    })
    .Take(50)
    .ToListAsync(cancellationToken);