Query Optimization Comparison

See the dramatic performance difference between optimized and unoptimized queries

Test 1: In-Memory Filtering vs Database Filtering

Bad Approach

// Load ALL data first
var all = await context.Products
    .ToListAsync();

// Then filter in memory
var filtered = all
    .Where(p => p.Price > 500)
    .ToList();

179 ms

Loads entire dataset into memory

VS

2.36x Faster

Good Approach

// Filter in database
var filtered = await context.Products
    .Where(p => p.Price > 500)
    .ToListAsync();

76 ms

Only loads filtered results

Test 2: Multiple Queries vs Single GroupBy Query

Bad Approach

// Get categories first
var categories = await context.Products
    .Select(p => p.Category)
    .Distinct()
    .ToListAsync();

// Then count each category
foreach (var cat in categories)
{
    var count = await context.Products
        .CountAsync(p => p.Category == cat);
}

54 ms

N+1 queries (1 + 10 = 11 queries)

VS

3.18x Faster

Good Approach

// Single GroupBy query
var stats = await context.Products
    .GroupBy(p => p.Category)
    .Select(g => new
    {
        Category = g.Key,
        Count = g.Count()
    })
    .ToListAsync();

17 ms

Single optimized query

Key Takeaways

Why Database Filtering is Faster

Reduced Data Transfer: Only filtered data moves from database to application
Indexed Columns: Database uses indexes for fast WHERE clause execution
Less Memory: Application doesn't need to hold entire dataset
Optimized Engine: SQL Server is optimized for filtering operations

Why Single Queries are Faster

Reduced Round Trips: One network call instead of many
Connection Overhead: Avoid connection acquisition cost per query
Query Plan Reuse: Database can cache and reuse execution plan
Atomic Operations: Database handles aggregation efficiently

Always Do

Filter in database with WHERE
Use GroupBy for aggregations
Project early with Select()
Use AsNoTracking() for reads

Be Careful

Avoid ToList() too early
Watch for client evaluation
Monitor query count
Check execution plans

Never Do

Load all data then filter
Run queries in loops
Ignore N+1 problems
Skip performance testing

Back to Demos View Best Practices