Master SQL Query Execution Order and Performance Optimization Tips

1. Logical Execution Order of SQL

The standard SQL parser processes clauses in this sequence: (1) FROM – assemble data from sources, (2) WHERE – filter rows, (3) GROUP BY – create groups, (4) aggregate functions, (5) HAVING – filter groups, (6) compute expressions, (7) ORDER BY – sort the result set.

2. Physical Execution Steps

When the engine executes a query, it follows these steps, creating temporary virtual tables (vt1‑vt11):

FROM – Cartesian product of the first two tables → vt1 ON – apply join condition, keep rows where join_condition is true → vt2 OUTER JOIN – preserve unmatched rows from the preserved table, producing vt3 WHERE – filter rows with where_condition → vt4 GROUP BY – group rows according to the list → vt5 CUBE|ROLLUP – insert super‑groups → vt6 HAVING – keep groups satisfying having_condition → vt7 SELECT – evaluate the select list → vt8 DISTINCT – remove duplicate rows → vt9 ORDER BY – sort rows → cursor vc10 TOP – return the requested number or percentage of rows →

vt11

3. Return Only the Data You Need

Avoid SELECT *; explicitly list required columns. Use table aliases and prefix column names to reduce parsing time and avoid ambiguity. Example:

SELECT A.ID, A.col1, B.col2
FROM table1 A INNER JOIN table2 B ON A.ID = B.ID
WHERE …

4. Minimize Redundant Work

Control repeated execution of the same statement, combine multiple UPDATE statements into one, and avoid converting an UPDATE into a DELETE + INSERT pair, which is far slower.

-- Bad: two separate updates
UPDATE EMPLOYEE SET FNAME='HAIWER' WHERE EMP_ID='VPA30890F';
UPDATE EMPLOYEE SET LNAME='YANG'   WHERE EMP_ID='VPA30890F';

-- Better: single update
UPDATE EMPLOYEE SET FNAME='HAIWER', LNAME='YANG' WHERE EMP_ID='VPA30890F';

5. Use Temporary Tables and Table Variables Wisely

Guidelines:

A) For very complex queries with many joins, break the work into temporary tables or table variables.

B) Cache frequently reused subsets of a large table.

C) Aggregate data from multiple tables step‑by‑step.

D) Limit usage in simple scenarios.

E) Choose between temporary tables and table variables based on data volume: large sets often run faster with temporary tables; small sets may benefit from table variables.

F) Prefer CREATE TABLE + INSERT INTO over SELECT INTO in high‑concurrency environments, because SELECT INTO locks system tables in tempdb.

6. Subquery Best Practices

Subqueries can be replaced with joins or EXISTS for better performance. Examples:

-- NOT IN rewritten as LEFT JOIN
SELECT PUB_NAME FROM PUBLISHERS A
LEFT JOIN TITLES B ON B.PUB_ID = A.PUB_ID AND B.TYPE='BUSINESS'
WHERE B.PUB_ID IS NULL;

-- NOT EXISTS rewritten as LEFT JOIN
SELECT TITLE FROM TITLES T
LEFT JOIN SALES S ON S.TITLE_ID = T.TITLE_ID
WHERE S.TITLE_ID IS NULL;

Avoid using COUNT(*) in subqueries to test existence; use EXISTS or a join instead.

7. Index Usage Guidelines

Ensure the optimizer can use indexes:

Do not apply functions or arithmetic to indexed columns; rewrite expressions so the column appears alone on the left side.

Prefer range predicates (e.g., WHERE year=2010 AND month=10) over calculated expressions.

Use LIKE 'ABC%' instead of LEFT(name,3)='ABC'.

Avoid concatenating indexed columns in the WHERE clause; compare each column separately.

8. Join Conditions and Index Selection

Write complete join predicates, preferably using clustered indexes. The order of evaluation is ON → WHERE → HAVING. Using ON filters rows before the join, which is most efficient.

Common join types:

INNER JOIN

LEFT JOIN (use instead of RIGHT JOIN)

CROSS JOIN

9. Additional Tips

Place the most frequent values first in an IN list.

Prefer UNION ALL when duplicates are acceptable.

Use DISTINCT only when necessary.

Know the difference between TRUNCATE TABLE and DELETE.

Reduce the number of round‑trips to the database.

Mark long stored procedures with region‑like comments for readability.

When debugging on production, use explicit transaction blocks with BEGIN TRAN, ROLLBACK, and COMMIT to avoid side effects.

BEGIN TRANSACTION;
-- Your statements here
IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION;
    RAISERROR('Error occurred',16,1);
    RETURN;
END
COMMIT TRANSACTION;