When the Query Is Slow

The slow query in an analyst's life is rarely exotic. It is a report that took ten seconds in spring and takes four minutes by fall, because the table grew and a pattern that never mattered started to matter. Tuning at that level does not require becoming a DBA. Most analyst-written slowness comes from a short list of causes you can see from the analyst's chair: a filter the engine can't use an index for, a join carrying far more rows than it should, or a query asking for more work than the question needs.

Demos below use WorkOrders from CellarDB (setup page), which at roughly 5,000 rows is the biggest table in the dataset and still small enough that everything returns instantly. That is fine, and it teaches the right reflex: at teaching scale you read plans, not stopwatches. The scan you can't feel at five thousand rows is the timeout at five million, and the plan looks the same at both.

Sargability: leave the column alone

The most useful word in this post is one almost nobody outside database work has heard: a predicate is sargable (from "search argument") when the engine can use an index to satisfy it. The rule that makes predicates sargable is short. Do your arithmetic to the constant, never to the column.

The classic violation looks completely innocent:

-- not sargable: every row's date passes through the function
SELECT WorkOrderID, WineLotID, WorkType, ScheduledDate
FROM WorkOrders
WHERE YEAR(ScheduledDate) = 2024;

An index on ScheduledDate stores dates in date order. YEAR(ScheduledDate) is a value that doesn't exist in the index, so the engine has to compute it for every row in the table, which means reading every row in the table. Wrap a filtered column in a function and you have hidden it from its own index. The fix is to move the work across the comparison:

-- sargable: the column is bare, the constants do the work
SELECT WorkOrderID, WineLotID, WorkType, ScheduledDate
FROM WorkOrders
WHERE ScheduledDate >= '2024-01-01'
  AND ScheduledDate <  '2025-01-01';

Same rows, and now the engine can seek straight to January 1st and read forward until the range ends. The half-open form (>= and <) is worth adopting as your only date-range idiom; it also never argues with anyone about whether the boundary day is included.

The same disguise has other costumes, all fixed the same way:

WHERE ISNULL(CellarHand, 'none') = 'none'          -- rewrite: CellarHand IS NULL
WHERE DATEDIFF(DAY, ScheduledDate, GETDATE()) > 30 -- rewrite: ScheduledDate < DATEADD(DAY, -30, GETDATE())
WHERE WorkType LIKE '%Rack%'                       -- a leading wildcard cannot seek; if you need this often, the column design is the problem

What an index actually is

You can use indexes well with one paragraph of theory. An index is a copy of one or more columns, stored in sorted order, with a pointer from each entry back to its full row. A seek uses that ordering to jump to the first interesting entry and read until the entries stop being interesting. A scan reads everything. The clustered index is the special case where the sorted structure is the table itself, which is why a table gets exactly one of those, usually on the primary key, and any number of the other kind.

On the teaching database you can build one and watch the plan change:

CREATE NONCLUSTERED INDEX IX_WorkOrders_ScheduledDate
    ON WorkOrders (ScheduledDate);

Run the two queries above with the actual plan on and compare: the YEAR() version scans the table no matter what indexes exist; the range version seeks. In most jobs you will not be the person who creates indexes on the warehouse, and that's as it should be. The reason to understand them anyway is that every filter you write is a bet on an index someone else already built, and sargability is how you stop losing the bet.

The plan in sixty seconds

Execution plans look like they require a course. For analyst purposes they require four checks. In SSMS, Ctrl+M turns on the actual plan (Ctrl+L shows the estimated one without running the query); execute, open the plan tab, and read it right to left, because the rightmost operators run first.

First: on the tables you know are big, are you looking at seeks or scans? A scan of a large table under a query that filters to a sliver is usually a sargability casualty.

Second: arrow thickness, which is row counts. Hover any arrow and read the number. The shape you are looking for is wide on the right and narrowing leftward as filters and joins do their work. An arrow that balloons mid-plan to many times the source rows is a fan-out happening live, and the series' flagship lesson applies to performance too: a join that inflates your SUM is also dragging ten times the rows through every operator after it. Wrong and slow are often the same bug.

Third: estimated versus actual rows on the busiest operators. When the optimizer guessed 40 rows and got 40,000, it planned the wrong strategy for the data it actually met, and everything downstream of the bad guess is improvised.

Fourth: yellow warning triangles. Hover them; they say things like an implicit type conversion (which can cost you a seek without raising any error) or a sort spilling to disk.

That is the whole sixty seconds, and it diagnoses most of what an analyst will ever cause. One era note: SQL Server 2022 turns Query Store on by default for new databases, which means the server keeps a history of plans and runtimes. When a query is fast on Tuesday and slow on Thursday with no edits in between, that history is the right place to look, and looking is a reasonable moment to involve whoever runs the server.

The EXISTS myth, and the real lesson

Somewhere in your first year of SQL, someone will tell you to rewrite IN (subquery) as EXISTS for performance. On SQL Server, this advice is folklore. The optimizer compiles a positive IN and the equivalent EXISTS to the same plan; put both forms of a membership test side by side with actual plans on and you will get two identical diagrams. Anyone who claims a speedup from that rewrite alone is remembering a different database or a different decade.

The negative case is where the real lesson lives, and it is worth learning properly because both halves of it bite. Say you want fruit lots that never became a wine lot:

-- looks right, returns zero rows
SELECT fl.FruitLotID, fl.PickDate, fl.Tons
FROM FruitLots AS fl
WHERE fl.FruitLotID NOT IN (
    SELECT wl.FruitLotID
    FROM WineLots AS wl
);

WineLots.FruitLotID is NULL for purchased bulk wine, and one NULL in a NOT IN list vetoes every row — the three-valued logic trap from the NULL post, returning a confidently empty result. But there is a second cost that post didn't cover: even when you patch the NULL away, the optimizer must still plan for the possibility of NULLs in that column, and the null-handling machinery it adds gets in the way of the cleanest strategy for this shape of question, the anti semi join, which checks each outer row for the absence of a match and moves on. NOT EXISTS has no such problem, because EXISTS only ever asks whether a matching row is there, a question NULLs cannot confuse:

-- correct, and the plan is a clean anti semi join
SELECT fl.FruitLotID, fl.PickDate, fl.Tons
FROM FruitLots AS fl
WHERE NOT EXISTS (
    SELECT 1
    FROM WineLots AS wl
    WHERE wl.FruitLotID = fl.FruitLotID
);

So the rule I actually follow: for positive membership, IN and EXISTS are a style choice, pick the readable one. For absence, NOT EXISTS every time, no exceptions, because it is the version that is both right and fast. The folklore got the pairing backwards: the rewrite that matters is the negative one.

When to stop

The stopping rule is about cadence, not pride. A monthly report that runs in forty seconds is finished; a dashboard query that runs in forty seconds is a problem. Fast enough for how often it runs and who is waiting is the entire standard, and the only honest way to apply it is to measure once before you touch anything (the SSMS timer in the status bar is plenty) and once after each change. Optimization without a before number is superstition.

And when the number says fast enough, stop, even though the plan still shows things you could improve. Past that point, every clever rewrite trades away the readability the last post argued for, and the hour you'd spend shaving seconds off a monthly job is an hour better spent breaking the number the way the proving post describes. A query that is fast, wrong, and unreadable has optimized the only property nobody asked about.

The first post claimed the syntax takes an afternoon, and ten posts later I'll stand by it, with the obvious second clause: the afternoon was never the point. What the rest of this series tried to transfer is a short list of questions asked stubbornly: what is the grain, where are the NULLs, what did the join multiply, how would I know if this number were wrong, and now, what is the engine actually doing. Asking them is mechanical. Asking them every time is the skill. The dataset stays up. Keep breaking it; the habit transfers to every database that matters more.

— Lo