How I Reduced a SQL Operation from 55 Seconds to 7 Seconds

A slow operation is rarely fixed reliably by changing one line and hoping for the best. The useful question is not simply “Which query is slow?” It is “What complete application path causes the user to wait?”

The symptom

A production workflow was taking roughly 55 seconds. It combined application-side filtering, database access and repeated processing over a large candidate set. The delay was visible to users and too expensive to dismiss as a background concern.

Establishing a baseline

Before changing code, I captured:

• the SQL generated by the application;
• execution plans and logical reads;
• indexes used and indexes ignored;
• row counts at each stage;
• time spent in the database versus application code.

This prevented a common mistake: optimising the most visually complicated query rather than the operation responsible for most elapsed time.

The main changes

1. Preserve query composition

Part of the data flow crossed from IQueryable to IEnumerable too early. That moved filtering work from SQL Server into application memory. Keeping the expression as IQueryable allowed SQL Server to perform the filtering before materialisation.

2. Rewrite the predicate around actual access patterns

The original predicate prevented the optimiser from using the available index effectively. Reshaping it reduced the candidate rows earlier and made the intended access path clearer.

3. Add a supporting index

The final index matched the selective filter columns and included values required by the operation. The goal was not to add indexes indiscriminately; it was to support this verified workload while controlling write overhead.

Result

The operation fell from approximately 55 seconds to around 7 seconds in the measured production-like path.

The broader lesson

Performance work should produce an evidence chain:

1. reproduce the delay;
2. separate database time from application time;
3. inspect generated SQL and plans;
4. change one bottleneck at a time;
5. measure again under representative data.

A faster query is useful. A repeatable method for finding the next bottleneck is more valuable.