Asynchronous vs Synchronous Queries

Understanding the importance of async/await for database operations

Synchronous (Blocking)

91 ms

Thread blocked during execution

// ❌ BAD - Synchronous blocking call
var products = _context.DemoProducts
    .Where(p => p.IsActive)
    .OrderBy(p => p.Name)
    .Take(1000)
    .ToList(); // Blocks thread!

// Thread is blocked waiting for database

Problems:

Blocks the current thread
Wastes thread pool resources
Reduces application scalability
Can cause thread starvation
Poor performance under load

Asynchronous (Non-Blocking)

62 ms

Thread freed during I/O

// ✅ GOOD - Asynchronous non-blocking call
var products = await _context.DemoProducts
    .Where(p => p.IsActive)
    .OrderBy(p => p.Name)
    .Take(1000)
    .ToListAsync(); // Returns thread to pool

// Thread available for other work

Benefits:

Frees thread during I/O operations
Better thread pool utilization
Improved application scalability
Handles more concurrent requests
Essential for web applications

Scalability Improvement

0.00% Better

Using async/await for database operations (measured on response time)

How It Works

❌ Synchronous Flow

1. Request arrives
Thread assigned from pool

2. Execute query (ToList)
Thread BLOCKED ⏸️

3. Wait for database...
Thread doing nothing but waiting

4. Data returned
Thread continues processing

5. Response sent
Thread returned to pool

Problem: Thread tied up entire time, can't handle other requests

✅ Asynchronous Flow

1. Request arrives
Thread assigned from pool

2. Execute query (ToListAsync)
Thread RELEASED ✅

3. Database processes query
Thread handles other requests

4. Data ready (await completes)
Thread (possibly different) resumes

5. Response sent
Thread returned to pool

Benefit: Thread freed during I/O, can handle many more concurrent requests

Common Async Patterns

Query Operations

// Get single entity
var product = await _context.Products
    .FirstOrDefaultAsync(p => p.Id == id);

// Get list
var products = await _context.Products
    .Where(p => p.IsActive)
    .ToListAsync();

// Check existence
bool exists = await _context.Products
    .AnyAsync(p => p.SKU == sku);

// Count
int count = await _context.Products
    .CountAsync(p => p.Category == "Electronics");

// Get specific value
decimal maxPrice = await _context.Products
    .MaxAsync(p => p.Price);

Modify Operations

// Add
_context.Products.Add(newProduct);
await _context.SaveChangesAsync();

// Update
var product = await _context.Products
    .FindAsync(id);
product.Price = 99.99m;
await _context.SaveChangesAsync();

// Delete
var product = await _context.Products
    .FindAsync(id);
_context.Products.Remove(product);
await _context.SaveChangesAsync();

// Bulk operations
await _context.Products
    .Where(p => p.IsActive == false)
    .ExecuteDeleteAsync();

Real-World Impact on Scalability

Scenario: Web API handling concurrent requests

Metric	Synchronous	Asynchronous	Improvement
Thread Pool Size	100 threads	100 threads	-
Avg Query Time	50ms	50ms	Same
Thread Blocking Time	50ms (blocked)	~0ms (freed)	100%
Max Concurrent Requests	~100	~10,000+	100x
Thread Starvation Risk	High	Low	Much Better
CPU Utilization	Low (waiting)	High (productive)	Better

Key Insight: Async doesn't make individual queries faster, but it allows your application to handle many more concurrent requests with the same resources. This is critical for scalability!

Async/Await Best Practices

✅ DO

Use async/await for ALL database operations
Use ToListAsync(), FirstAsync(), etc.
Use SaveChangesAsync() for updates
Make controller actions async Task<IActionResult>
Use await keyword - don't block with .Result
Propagate async all the way up the call stack

❌ DON'T

Never use .Result or .Wait() - causes deadlocks
Don't use synchronous methods (ToList(), First())
Don't mix sync and async code unnecessarily
Don't forget await keyword (creates fire-and-forget)
Don't use async void (except event handlers)
Don't block async code in synchronous context

Common Async Mistakes

❌ Deadlock with .Result

// DEADLOCK in ASP.NET!
public IActionResult GetProducts()
{
    // Blocks thread waiting for async operation
    var products = _context.Products
        .ToListAsync()
        .Result; // DEADLOCK!
    
    return View(products);
}

// ✅ CORRECT
public async Task<IActionResult> GetProducts()
{
    var products = await _context.Products
        .ToListAsync();
    
    return View(products);
}

❌ Async Void

// BAD - Can't catch exceptions
public async void LoadData()
{
    var products = await _context.Products
        .ToListAsync();
    // Exception here is lost!
}

// ✅ CORRECT
public async Task LoadDataAsync()
{
    var products = await _context.Products
        .ToListAsync();
    // Exceptions properly propagated
}

Back to Demos Best Practices