SSIT Performance Optimization

Overview

A set of API endpoints in an internal application were timing out under normal load. Requests that should complete in under a second were taking 8–15 seconds. This impacted the user-facing features built on top of them.

What I Found

Using SQL Server Profiler and EF Core query logging, I traced the slowdowns to a classic N+1 query pattern — LINQ queries that appeared fine in code were generating hundreds of individual SQL round-trips per request.

The specific issues:

• Lazy-loaded navigation properties inside loops, causing one query per iteration
• Missing .Include() calls forcing repeated database hits for related data
• Unfiltered .ToList() calls pulling entire tables into memory before filtering in C#

What I Fixed

• Replaced lazy-loaded patterns with explicit .Include() and .ThenInclude() chains
• Pushed filtering into the query (.Where() before .ToList()) so SQL Server handles it
• Rewrote two particularly bad queries as raw SQL for cases where EF couldn't produce an efficient plan

Result

Response times dropped from 8–15 seconds to under 1 second — roughly a 10x improvement. The fix required no schema changes and no infrastructure changes.