How I Made a 21-Second Query Run in 0.07 Seconds

Summary of the video (SQL performance optimization: 21s → 0.07s)

• Problem: A report-generation process in a system took up to ~60 seconds. The author identified a specific SQL cause: a query/procedure took ~21 seconds.
• Core lesson: Don’t guess. Use a workflow of measurement → narrowing → execution-plan analysis → move filtering earlier to find the true bottleneck.

Step-by-step process used

1. Measure where the time goes (SSMS profiling approach)

   • The report relied on SQL Server stored procedures.
   • The author ran the same stored procedure calls in a new query editor, adding PRINT statements with timestamps between procedure calls.
   • They checked actual timings via SSMS “Messages” tab output.

2. Narrow to the worst stored procedure

   • One stored procedure accounted for ~half of the report’s total elapsed time (about 30 seconds).

3. Instrument inside that stored procedure

   • The author converted the stored procedure into a script form (not actually creating/calling it) to insert additional PRINT statements.
   • Inside it, one particular internal query consumed ~21 of the 30 seconds.

4. Use the Actual Execution Plan (not estimated)

   • The author enabled the Actual Execution Plan and observed runtime slightly above 21 seconds.
   • Key execution-plan observations:
     • There were index seeks on the large transactions table (a good sign, but not sufficient).
     • A sort step was the most expensive operation.
     • The most important clue was row-count flow:
       • Millions of rows entered early stages (joins / cross apply / grouping),
       • then the rowset was reduced to only ~500 rows late by a filter.
     • Net effect: processing millions to return 500.

What caused the slowdown

• The query had a parameterized account filter, but it was applied too late in the query structure.
• Even though the outer query filtered by the target account number, the inner subquery that retrieved transaction data still pulled a massive set because it didn’t filter by the account parameter inside the subquery.
• Data volumes mentioned:
  • transactions: ~38 million rows
  • accounts: ~90,000 rows
  • a third table: ~17,000 rows
  • final output: ~500 rows for a specific account

The fix

• Add the account filter inside the subquery that reads the transaction data.
  • Specifically: ensure the subquery includes a condition matching:
    • transaction.account_number = the outer account_number parameter.
• Result: runtime dropped from ~21 seconds to ~0.07 seconds (about ~300× faster).

Why the execution plan improved

• The plan’s structure looked similar (same general operators like joins/cross apply/grouping), but:
  • early row counts dropped dramatically
  • therefore later operations (including the costly sort) became much cheaper
  • output stayed correct (still ~500 rows)

Generalizable principles / guidance

• Filtering “late” is often the hidden performance killer, even when the query is logically correct.
• Optimizers may not always push filters down automatically, especially with:
  • complex query structure,
  • CROSS APPLY,
  • grouping/joins/subquery nesting.
• This pattern applies beyond subqueries too (e.g., CTEs, derived tables, multi-step procedures):
  • Stop unnecessary rows as early as possible.
• The author’s reusable troubleshooting workflow:
  1. Measure baseline runtime
  2. Narrow the bottleneck by profiling stored procedures first
  3. Use Actual Execution Plan
  4. Trace actual row counts from right-to-left
  5. Move filters earlier where possible and verify correctness + measure again

Resources mentioned

• A free SQL performance checklist (link in description).
• A follow-up video recommendation: analyzing another slow query from scratch and showing attempted changes that didn’t work.

Main speakers / sources

• Primary speaker: the video author/host (no specific name given in the subtitles).
• Technical source/tooling: SQL Server (SSMS) including Stored Procedures and Actual Execution Plans.