SQL Optimization | Infographic

Here are some SQL optimization Tips.

1. Use ‘regexp_like‘ to replace ‘LIKE‘ clauses
2. Use ‘regexp_extract‘ to replace ‘Case-when Like’
3. Convert a long list of In clauses into a temporary table.
4. Always order your JOINs from the largest tables to the smallest tables.
5. Use simple equi-joins.
6. Always “GROUP BY” the attributes/column with the largest number of unique entities/values.
7. Avoid subqueries in the WHERE clause.
8. Use Max instead of Rank.
9. 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