From “it works” queries to senior-level SQL
Junior SQL is about getting the right rows. Senior SQL adds predictability: consistent performance, stable plans, and clear behavior under load.
1. Look at the execution plan first
When a query is slow, I open the execution plan before touching the SQL. It tells me:
- Which operations are expensive (sorts, hash joins, key lookups).
- Where we’re scanning large tables unnecessarily.
- Whether our indexes are actually being used.
2. Fix the access pattern, not just add indexes
A common anti-pattern: add a new index for every slow query. Instead, I ask:
- Can we change the WHERE/ORDER BY to match an existing index?
- Can we avoid wildcards at the start of LIKE?
- Can we precompute or denormalize a small part of the data?
3. Be aware of parameter sniffing
In SQL Server, the first set of parameter values used with a query can influence the cached plan. A plan that’s great for “small” input might be terrible for “large” input.
Mitigations include:
- Using
OPTION (RECOMPILE)for very sensitive queries. - Using local variables inside stored procedures.
- Splitting into separate queries for “small” and “large” paths.
4. Avoid row-by-row work in application code
Instead of looping in C# and sending one query per row, I try to:
- Use table-valued parameters or bulk inserts.
- Do set-based updates in a single statement.
- Batch work into chunks (e.g., 500–2000 rows at a time).