Query tuning: temp tables, catch-all filters and paging

Tuning starts with measurement, not with rewriting. Turn on SET STATISTICS IO, TIME ON to see logical reads and CPU per statement, look at the actual plan for the operator where estimated and actual rows part ways, and only then change something. Logical reads are the most stable number to compare before and after; elapsed time moves with whatever else the server is doing.

Three patterns come up again and again.

Temp table, table variable or CTE

• A CTE stores nothing; it is part of the query that uses it.
• A temp table (#name) lives in tempdb for the session. It has statistics, can be indexed after creation, and takes part in transactions like a normal table.
• A table variable (@name) also lives in tempdb, but has no column statistics. Since SQL Server 2019 (compatibility level 150 and up), deferred compilation lets the optimizer see its row count at first execution, but it still knows nothing about the distribution of values. And it is not affected by ROLLBACK.

When the intermediate result is large and is joined or filtered further, prefer a temp table, whose statistics give the optimizer something to work with. A table variable's immunity to rollback has one well-known use: keeping a log of what happened in a transaction that is about to be rolled back.

1CREATE TABLE payments (id INT PRIMARY KEY, amount INT NOT NULL);

1DECLARE @log TABLE (msg NVARCHAR(50));2CREATE TABLE #log (msg NVARCHAR(50));3 4BEGIN TRAN;5  INSERT INTO payments VALUES (1, 100);6  INSERT INTO @log VALUES (N'attempted payment 1');7  INSERT INTO #log VALUES (N'attempted payment 1');8ROLLBACK;9 10SELECT (SELECT COUNT(*) FROM payments) AS payments_rows,11       (SELECT COUNT(*) FROM #log)     AS temp_table_rows,12       (SELECT COUNT(*) FROM @log)     AS table_variable_rows;

The catch-all search

A search form with optional filters is often written as:

WHERE (@customer_id IS NULL OR customer_id = @customer_id)
  AND (@status IS NULL OR status = @status)

It returns the right rows, but a cached plan must be correct for every future parameter value, including NULL, so the plan cannot seek on customer_id. Two fixes:

• OPTION (RECOMPILE) compiles the statement for the actual values on every execution, so the @customer_id IS NULL branch is removed and a seek becomes possible. You pay a compile per execution.
• Build the WHERE clause dynamically with only the filters supplied, and run it with sp_executesql and parameters. Each shape gets its own cached plan.

1CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT NOT NULL, total INT NOT NULL);2CREATE INDEX ix_customer ON orders (customer_id);3INSERT INTO orders VALUES (1, 5, 10), (2, 6, 20), (3, 5, 30);

1DECLARE @log TABLE (id INT, variant NVARCHAR(40), can_seek NVARCHAR(30));2DECLARE @q NVARCHAR(400);3 4SET @q = N'SELECT COUNT(*) FROM orders WITH (FORCESEEK) WHERE (@c IS NULL OR customer_id = @c)';5BEGIN TRY6  EXEC sp_executesql @q, N'@c INT', @c = 5;7  INSERT INTO @log VALUES (1, N'catch-all', N'yes');8END TRY9BEGIN CATCH10  INSERT INTO @log VALUES (1, N'catch-all', CONCAT(N'no, error ', ERROR_NUMBER()));11END CATCH;12 13SET @q = @q + N' OPTION (RECOMPILE)';14BEGIN TRY15  EXEC sp_executesql @q, N'@c INT', @c = 5;16  INSERT INTO @log VALUES (2, N'catch-all + RECOMPILE', N'yes');17END TRY18BEGIN CATCH19  INSERT INTO @log VALUES (2, N'catch-all + RECOMPILE', CONCAT(N'no, error ', ERROR_NUMBER()));20END CATCH;21 22SET @q = N'SELECT COUNT(*) FROM orders WITH (FORCESEEK) WHERE customer_id = @c';23BEGIN TRY24  EXEC sp_executesql @q, N'@c INT', @c = 5;25  INSERT INTO @log VALUES (3, N'only the filter supplied', N'yes');26END TRY27BEGIN CATCH28  INSERT INTO @log VALUES (3, N'only the filter supplied', CONCAT(N'no, error ', ERROR_NUMBER()));29END CATCH;30 31SELECT variant, can_seek FROM @log ORDER BY id;

Deep paging

OFFSET 100000 ROWS FETCH NEXT 20 ROWS ONLY still has to produce the first 100,000 rows in order and discard them, so each page is slower than the last. Keyset paging remembers the last row the client saw and seeks past it:

WHERE created_at > @last_created
   OR (created_at = @last_created AND id > @last_id)
ORDER BY created_at, id

SQL Server can answer that predicate with a seek on an index on (created_at, id), so fetching the next page does not get slower the deeper the client goes. The order key must be unique, which is why id is part of it.

Common mistakes

• Logging into a temp table inside a transaction you roll back. The log is rolled back too; the first example shows only the table variable keeping its row.
• Catch-all predicates with a cached plan. The second example shows the seek is impossible until OPTION (RECOMPILE) is added.
• Keyset paging on a non-unique key. created_at > @last alone skips every row that shares the last row's timestamp.

What interviewers ask

• Temp table or table variable? Temp tables have statistics and roll back; table variables have no column statistics and ignore rollback. Larger or further-filtered sets belong in temp tables.
• How would you write a search with optional parameters? Dynamic SQL via sp_executesql with only the needed predicates, or OPTION (RECOMPILE); explain the compile cost.