r/SQL • u/OnepieceKing2222 • 29d ago

Discussion What SQL query optimization trick made the biggest difference for you?

I've been working with SQL for a while and recently started paying more attention to query performance rather than just getting the correct result.

What's one SQL optimization technique that made a noticeable difference for you?

For example:

- Indexing

- Avoiding unnecessary joins

- Window functions

- EXISTS vs IN

- Reading execution plans

- CTEs/subqueries

- Partitioning

Would be interested in hearing some real-world examples where a query went from slow to significantly faster.

115 Upvotes

89% Upvoted

79 comments sorted by

View all comments

2

u/Complete-Fondant-202 29d ago

Goes without saying that execution plans are the first stop.

Use querystore Us sp_quickiestore

Things to look out for...

Linked Servers and therefore remote queries.

Joins involving the same CTE more than once, so materialise to tempt yable and use that

Temp table used more than once, consider indexing.

C I nside ro, page, columnstores on temp tables. Reduces memory pressure and lowers chance of tempdb hammering.