10 SQL Optimization Tips for Data Engineers

Optimizing SQL query performance is a core requirement in backend and data engineering roles. A query that takes 30 seconds on unindexed tables can often be tuned to run in under 50 milliseconds with proper indexing and predicate structuring.

1. Write SARGable Query Predicates

-- Non-SARGable (cannot use index):
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2026;

-- SARGable (uses B-Tree index scan):
SELECT * FROM Orders WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';

SARGable stands for Search Argument Able. Keeping indexed columns free of function wrappers allows the database engine to perform direct index seeks.

2. Replace Correlated Subqueries with JOINs or Window Functions

Correlated subqueries execute once for every row in the outer query. Transforming them into a single-pass JOIN or window aggregation reduces disk I/O significantly.

3. Use EXISTS Instead of IN for Large Subqueries

-- Faster for large result sets:
SELECT c.Name 
FROM Customers c
WHERE EXISTS (
  SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID
);

EXISTS stops searching as soon as a single match is found, whereas IN evaluates the entire subquery result set before filtering.