How to Write Efficient Query? – Interview Question of the Week #300

Question: How do I write an efficient SQL query? Return the correct answer with appropriate resource use for the workload. Start by defining the required rows and columns, then measure the plan, logical reads, CPU and elapsed time. A list of preferred keywords is not enough.

I hear this question from interview candidates and blog readers all the time. Everyone wants an efficient query. My original list came from changes I see during client work; I still start with the simple question: are we asking SQL Server to return more data than the application needs?

Make the request precise, then measure it

• List the needed columns rather than using SELECT * for an application result. Filter unwanted rows at the database. SELECT * inside an EXISTS subquery does not request a wide result for the application.
• Use a predicate that can work with the intended index. A date range is often more useful than applying a formatting function to every stored date.
• Inspect an actual plan, STATISTICS IO and STATISTICS TIME. Compare the same result, representative parameters and similar conditions.
• Keep transactions short enough for the business operation, and investigate blocking and row-by-row work. Use set-based alternatives where they express the task clearly.

For example, this read-only sample requests January’s invoices with a half-open date range. It demonstrates the measurement setup, not a benchmark result that every server must reproduce:

USE WideWorldImporters;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT InvoiceID, CustomerID, InvoiceDate
FROM Sales.Invoices
WHERE InvoiceDate >= '20160101' AND InvoiceDate < '20160201';
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Some of my old shortcuts need a qualification

EXISTS is a clear existence test, but it is not automatically faster than IN. Semantically equivalent queries can receive equivalent plans; NULL behavior must be correct before performance comparisons are meaningful. Do not choose one by slogan.

A trigger can add work, yet removing it without preserving its business rule is not an optimization. My OUTPUT discussion explains one specific alternative, not a universal trigger replacement.

Likewise, parallelism, views and cursors deserve workload-specific review. They are not inherently wrong. SET NOCOUNT ON can reduce row-count messages in procedures with many statements; it does not remove the query’s underlying work. Standards and readable SQL help maintenance but do not guarantee a fast plan.

Try my performance challenge, and add your measured example in the comments. Microsoft’s subquery examples show both EXISTS and IN formulations; correctness and the actual workload decide the comparison.

A milestone from the original publication

When I published this question, it marked 14 years of daily blogging and my 5,286th blog post. I was happy to reach that milestone, and grateful to the readers who kept sending practical questions. That personal note belongs to the original publication date; it is not a new claim about today’s count.

Want to try this yourself? Here is how to install WideWorldImporters.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.