Optimizing SQL Performance: Expert Tips & Tricks

#1
Always order your JOINs from largest tables to smallest tables

#2
Always "GROUP BY" by the attribute/column with the largest number of unique entities/values

#3
Avoid subqueries in WHERE clause

#4
Use Max instead of Rank

#5
Use ‘regexp_like’ to replace ‘LIKE’ clauses

#6
Use ‘regexp_extract’ to replace ‘Case-when Like’

#7
Convert long list of IN clause into a temporary table

#8 Other Tips

● Use approx_distinct() instead of count(distinct) for very large datasets
● Use approx_percentile(metric, 0.5) for median
● Avoid UNIONs where possible
● Use WITH statements vs. nested subqueries