Summary of "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.

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.
Narrow to the worst stored procedure
- One stored procedure accounted for ~half of the report’s total elapsed time (about 30 seconds).
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.
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.

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

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).

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)

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

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.

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.