Performance Optimization

Level: Intermediate

ℹ️ Where This Fits

Unoptimized queries scale poorly. Understanding lazy loading, change tracking, and query translation prevents performance bottlenecks as data grows.

ℹ️ What You'll Learn

• AsNoTracking: Disable change tracking for read-only queries (performance boost)
• Lazy loading: Access related data causes extra queries (danger in loops)
• Eager loading: Include related data upfront (solves N+1)
• Query translation: LINQ translated to SQL (check generated SQL)
• ExecuteUpdate/ExecuteDelete: Bulk operations without loading entities (EF Core 7+)
• Indexes: Create database indexes on frequently filtered columns
• Select projection: Load only needed columns (not entire entity)
• Split queries: Execute separate queries for better performance (SplitQuery)
• Query caching: Entity Framework caches compiled queries
• ChangeTracker.Clear: Clear tracking for memory efficiency (large datasets)
• Profiling: Enable SQL logging to see generated queries
• Common mistakes: Tracking read-only data, lazy loading in loops, complex includes

AsNoTracking for Read-Only

// Bad - tracks 10,000 entities (memory intensive)
var students = await _context.Students
    .ToListAsync();

// Good - no tracking (read-only API response)
var students = await _context.Students
    .AsNoTracking()
    .ToListAsync();

Bulk Update - ExecuteUpdate (EF Core 7+)

// Bad - loads all, modifies, saves (slow for large datasets)
var studentsToUpdate = await _context.Students
    .Where(s => s.Status == StudentStatus.Inactive)
    .ToListAsync();

foreach (var student in studentsToUpdate)
{
    student.Status = StudentStatus.Graduated;
}
await _context.SaveChangesAsync();

// Good - single SQL UPDATE statement
var rowsAffected = await _context.Students
    .Where(s => s.Status == StudentStatus.Inactive)
    .ExecuteUpdateAsync(s => s
        .SetProperty(x => x.Status, StudentStatus.Graduated));

Split Queries - Multiple Queries Better Than Complex Joins

// Single large query (can be slow with large datasets)
var students = await _context.Students
    .Include(s => s.Exams)
    .Include(s => s.FeeAccounts)
    .ToListAsync();

// Split into multiple queries (often faster)
var students = await _context.Students
    .Include(s => s.Exams)
    .Include(s => s.FeeAccounts)
    .AsSplitQuery()
    .ToListAsync();

Projection - Load Only Needed Columns

// Bad - loads entire Student object (all columns)
var students = await _context.Students
    .Where(s => s.ClassName == "10-A")
    .ToListAsync();  // Gets Id, Name, ClassName, DateOfBirth, Status, etc

// Good - loads only Name and Roll Number
var studentNames = await _context.Students
    .Where(s => s.ClassName == "10-A")
    .Select(s => new
    {
        s.Name,
        s.RollNumber
    })
    .ToListAsync();

Disable Change Tracking for Large Imports

// Importing 100,000 students
using (var context = new SmsDbContext())
{
    context.ChangeTracker.AutoDetectChangesEnabled = false;

    var students = GetStudentsToImport();  // 100,000
    
    foreach (var batch in students.Chunk(1000))
    {
        _context.Students.AddRange(batch);
        await _context.SaveChangesAsync();
        
        _context.ChangeTracker.Clear();  // Clear to free memory
    }
}

Enable SQL Logging to Profile

protected override void OnConfiguring(DbContextOptionsBuilder options)
{
    options
        .UseSqlServer(connString)
        .LogTo(
            logger: Console.WriteLine,
            filter: (category, level) => 
                category == DbLoggerCategory.Database.Command.Name && 
                level == LogLevel.Information,
            logBehavior: DbContextLogBehavior.Instant
        )
        .EnableSensitiveDataLogging();  // Log parameter values
}

Best Practices

✓ Use AsNoTracking for read-only queries ✓ Include related data to prevent N+1 ✓ Project only needed columns ✓ Use ExecuteUpdate for bulk changes ✓ Profile queries with SQL logging

💡 When to Optimize

1. Write simple, correct code first
2. Profile and identify bottleneck
3. Optimize based on data: AsNoTracking, Projection, Indexes
4. Don't premature optimize

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on Performance Optimization. Try these prompts:

• "When should you use AsNoTracking?"
• "What's the difference between Include and SplitQuery?"
• "How do you do bulk updates without loading entities?"
• "When is Select projection important for performance?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> Dapper 01. Dapper Micro-ORM Fundamentals