Part 4 — Fast at scale › Chapter 29

The queries that are always slow

A checklist of ten shapes. If a query is slow, it is almost certainly one of them.

From the advert

“Ottimizzazione e tuning delle query” — this chapter is the checklist. If a query is slow, it is almost certainly one of these ten shapes.

🧒 Simple

Most slow things are slow for boring reasons. Before you invent a clever explanation, check the ten boring ones — the same way a mechanic checks the fuel before rebuilding the engine.

🎓 Professional

The ten shapes below are not a miscellany — they fall into three families, and naming the family is usually enough to find the fix. Predicates the optimiser cannot use (1, 2, 7): the query could seek and does not. Row-at-a-time work (4, 5, 6): a set-based engine being asked to behave like a loop, once per row. Doing more than was asked (3, 8, 9, 10): reading columns, rows or groups that the caller never uses. A slow query is nearly always one of those three sentences, and saying which one — before touching the plan — is what makes the diagnosis quick.

1 · A function wrapping the filtered column

Pattern 1: a function wrapping the filtered column.
WHERE YEAR(placed_at) = 2026                                   -- scan: computed per row
WHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01'   -- seek: a range over stored values
The tell, and the fix
  • Tell: the indexed column appears inside a function call in the WHERE clause.
  • Fix: rewrite as a half-open range on the bare column. If that is genuinely impossible, index the expression itself.
  • Full treatment in chapter 07 — this list is for recognising it in ten seconds, not for learning it.

The commonest single cause. See chapter 07.

2 · Implicit conversion

Pattern 2: implicit conversion — the one with no visible symptom.
WHERE vat_number = 12345678901     -- column is VARCHAR: converts EVERY row, loses the index
WHERE vat_number = '12345678901'   -- text to text: no conversion, index seek
The tell, and the fix
  • Tell: a CONVERT_IMPLICIT in the SQL Server plan, or a seek you expected and did not get. The query is correct, which is why nobody notices.
  • Fix: match the literal's and the parameter's type to the column's. It is often the application's driver choosing NVARCHAR for a VARCHAR column, so the fix may be in the ORM's mapping, not in the SQL.

Invisible: the query is correct and returns the right answer, merely a hundred times slower. Very often introduced by an ORM binding NVARCHAR against a VARCHAR column.

3 · SELECT *

Three costs, ascending in importance: bytes nobody asked for; forced off-page LOB reads; and — the real one — it prevents a covering index from covering, turning an index-only scan into a seek plus a lookup per row. It also breaks silently when someone adds a column.

4 · N+1

Pattern 4: N+1 — one query to get the list, then one per item.
SELECT id FROM orders WHERE customer_id = 42;      -- 1 query, 500 rows back
-- then, in an application loop, 500 times:
SELECT * FROM order_lines WHERE order_id = ?;      -- 500 more queries, one per row
The tell, and the fix
  • Tell: the database log shows the same statement thousands of times with different parameters. Each is fast — a millisecond — and five hundred of them is half a second of pure network latency.
  • Fix: one query with a join, or one query with WHERE order_id IN (…) and grouping in the application. In an ORM it is .Include() / fetch join / selectinload (chapter 38).
  • This is the most common performance bug in applications built on an ORM, and it never appears in a query-level report because no single query is slow.
⚠ Why this one hides

Every individual query is fast, so nothing appears in a slow-query log and nothing looks expensive in a plan. It only surfaces when you sort the plan cache by total duration, or count round trips. At 0.5 ms of network per call, 500 calls is a quarter of a second of doing nothing at all. See chapter 38.

5 · A cursor or a row-by-row loop

Pattern 5: a cursor doing what one statement could do.
-- Anything shaped like this in T-SQL or PL/SQL:
DECLARE c CURSOR FOR                                    -- declare a row-at-a-time reader…
  SELECT id FROM orders WHERE status = 'confirmed';     -- …over this set
OPEN c;                                                 -- start it
FETCH NEXT FROM c INTO @id;                             -- read the first row
WHILE @@FETCH_STATUS = 0                                -- 0 means the fetch succeeded
BEGIN                                                   -- the loop body
  UPDATE orders SET total_cents = (...) WHERE id = @id; -- ONE statement per row
  FETCH NEXT FROM c INTO @id;                           -- next row
END                                                     -- …and repeat, thousands of times
The tell, and the fix
  • Tell: DECLARE … CURSOR, or a WHILE loop around a single-row UPDATE. Each statement carries its own parse, plan lookup, lock and log record.
  • Fix: one set-based UPDATE … FROM over the whole set. Two to three orders of magnitude is a normal improvement, not an exaggeration.
  • Cursors are not forbidden — maintenance scripts and genuinely sequential algorithms use them. They are simply almost never the reason a business query is slow, and almost always the reason a batch job is.

Ten thousand statements, ten thousand plans looked up, ten thousand log records. The set-based equivalent is one statement. The rare legitimate cursor: calling an external procedure per row, or deliberately batching a huge update to avoid lock escalation — and in that case say so in a comment, because the next reader will assume you did not know better.

6 · Scalar user-defined functions in the SELECT or WHERE

Pattern 6: a scalar user-defined function in the SELECT list.
SELECT id, dbo.fn_CalculateTax(total_cents) FROM orders;   -- ~1.8 million calls, one per row
The tell, and the fix
  • Tell: a dbo.fn_… call in the SELECT or WHERE. Before SQL Server 2019 these were invisible in the plan and ran once per row, single-threaded, blocking parallelism for the whole query.
  • Fix: rewrite as an inline table-valued function, a CROSS APPLY, or a plain expression. SQL Server 2019's scalar UDF inlining does this automatically — sometimes.
  • The same warning applies to a scalar function in a CHECK constraint or a computed column: it runs on every write instead.

Before SQL Server 2019, that function was executed once per row, with no costing, and it made the whole query serial. Inlining (2019+) helps, but only for functions simple enough to inline. The portable fix is to express the logic as an inline table-valued function used with CROSS APPLY, or as a plain expression.

7 · OR across different columns

Pattern 7: OR across different columns.
WHERE email = ? OR phone = ?                    -- no single index leads with both columns
-- becomes:
SELECT ... WHERE email = ?                      -- one seek, on the email index…
UNION                                           -- …combined with…
SELECT ... WHERE phone = ?;                     -- …one seek, on the phone index
The tell, and the fix
  • Tell: a scan on a table that has perfectly good indexes on both columns mentioned.
  • Fix: UNION of two seeks, which the optimiser can cost separately. Use UNION ALL if a row cannot match both sides — it skips the deduplication.
  • OR between values of the same column is fine, and is usually rewritten as an IN list of seeks.

8 · OFFSET deep in a result set

Pattern 8: OFFSET deep in a result set.
ORDER BY placed_at DESC OFFSET 100000 ROWS FETCH NEXT 20 ROWS ONLY;  -- page 5001
-- The engine produces 100 020 rows and throws 100 000 of them away.
The tell, and the fix
  • Tell: page one is instant and page five hundred takes eight seconds. The cost grows with the page number, which is the signature.
  • Fix: keyset pagination — WHERE (placed_at, id) < (?, ?) ordered by the same columns. Constant cost per page, at the price of losing “jump to page 37”. Chapter 30 is the long version.

See chapter 30 for keyset pagination.

9 · COUNT(DISTINCT …), several times, on a dashboard

Each one needs a sort or hash of all values and none of them parallelise well. Four in one query is four times the cost. Pre-aggregate, or use an approximate count for a dashboard tile.

10 · The optional-parameter WHERE

Pattern 10: the optional-parameter WHERE clause.
WHERE (@customer_id IS NULL OR customer_id = @customer_id)  -- "filter, if one was supplied"
  AND (@status      IS NULL OR status      = @status)       -- …and again, per optional filter
The tell, and the fix
  • Tell: a reporting screen with several optional filters, and a query that is fast for some combinations and disastrous for others depending on which one compiled the cached plan.
  • Fix: OPTION (RECOMPILE) for a fresh plan per execution, or genuinely dynamic SQL built with sp_executesql and parameters (chapter 34).
  • Naming this pattern in an interview is a strong signal: it means you have maintained a reporting screen.

One cached plan for every combination of supplied parameters. Fix with OPTION (RECOMPILE) or safe parameterised dynamic SQL.

The order to check them in

✔ Two minutes, before opening a plan
  1. Is the filtered column wrapped in anything? (1, 2)
  2. Is this one slow query, or many fast ones? (4) — sort by total duration.
  3. Is there a loop, a cursor or a scalar function? (5, 6)
  4. Does it return far more columns or rows than the caller uses? (3, 8)
  5. Only then: open the actual plan and look for the estimate gap (chapter 27).
⚠ The other four causes, which are not query shapes at all

If none of the ten fit, the problem is probably not the query: blocking (chapter 23), a bad cached plan (chapter 28), missing statistics (chapter 28), or the server — memory pressure, a saturated disk, another workload. Chapter 42 is how to tell which, and looking there before rewriting SQL saves a lot of wasted rewriting.

The classic “optimise this” exercise: four separate problems in six lines.
-- Rewrite this, then compare the two plans.
SELECT DISTINCT c.name,                                              -- problem 4: hides a fan-out
       (SELECT COUNT(*)         FROM orders o WHERE o.customer_id = c.id) AS n,      -- problem 2…
       (SELECT SUM(total_cents) FROM orders o WHERE o.customer_id = c.id) AS spend   -- …twice
FROM customers c                                                     -- the driving table
JOIN orders o2 ON o2.customer_id = c.id                              -- problem 3: a join to filter
WHERE strftime('%Y', o2.placed_at) = '2026';                         -- problem 1: not sargable
-- The answer is worked through in course/SOLUTIONS.md.
The four problems, in the order you would fix them
  • 1 · The date filter is not sargable. strftime('%Y', …) = '2026' computes a value per row. Replace with placed_at >= '2026-01-01' AND placed_at < '2027-01-01'.
  • 2 · Two correlated subqueries over the same rows. Aggregate once in a derived table and join to it (chapter 11).
  • 3 · A join that exists only to filter. It multiplies rows — a customer with forty orders appears forty times — which is why the DISTINCT is there. It should be EXISTS.
  • 4 · DISTINCT hiding the fan-out. It makes the row list look right. If the query ever grows a SUM over o2, the numbers will be forty times too high and still look plausible.
  • Also note the two aliases for the same table, o and o2: a reliable sign that a query has grown by accretion rather than been designed.
  1. Ten shapes cover almost every slow query. Check them before theorising.
  2. A function or a conversion on the filtered column is the single commonest cause.
  3. SELECT *'s real cost is that it stops an index covering the query.
  4. N+1 is invisible in every per-query report. Sort by total duration to see it.
  5. A cursor is ten thousand statements. If one is genuinely right, comment why.
  6. A scalar UDF in a predicate is a black box: no estimate, no parallelism, once per row.
  7. Deep OFFSET reads and discards everything before the page.
  8. Several COUNT(DISTINCT)s in one query multiply an already-expensive operation.
  9. If none of the ten fit, look at blocking, plans, statistics and the server before rewriting SQL.

Interview questions this chapter answers

Give me some common causes of slow queries.

Non-sargable predicates — a function or an implicit conversion on the filtered column; N+1 from the application; SELECT * defeating a covering index; cursors and scalar UDFs forcing row-at-a-time work; deep OFFSET pagination; and the optional-parameter WHERE that caches one plan for every shape. If none fit, it is usually blocking, a bad cached plan, or the server.

Why avoid SELECT * in production code?

It moves bytes nobody needs, can force off-page LOB reads, and — most importantly — prevents a covering index from covering, so a cheap index-only scan becomes a seek plus a lookup per row. It also silently changes behaviour when someone adds a column.

When is a cursor acceptable?

When each row genuinely requires a separate external action — calling a procedure, sending something — or when you are deliberately batching a huge update to avoid lock escalation. Both cases deserve a comment saying so, because the default assumption on reading a cursor is that the author did not know the set-based form.