Scribd
Upload
2 views17 pages

SQL Optimization

The document outlines various SQL optimization techniques, including using 'regexp_like' instead of 'LIKE', converting long IN clauses to temporary tables, and optimizing JOINs by ordering from largest to smallest tables. It emphasizes avoiding subqueries in WHERE clauses, using simple equi-joins, and applying best practices like filtering early and utilizing proper indexes. Additionally, it provides golden rules for SQL optimization and suggests using functions like approx_distinct() for large datasets.

Uploaded by

Manoj Kumar Shukla
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views17 pages

SQL Optimization

The document outlines various SQL optimization techniques, including using 'regexp_like' instead of 'LIKE', converting long IN clauses to temporary tables, and optimizing JOINs by ordering from largest to smallest tables. It emphasizes avoiding subqueries in WHERE clauses, using simple equi-joins, and applying best practices like filtering early and utilizing proper indexes. Additionally, it provides golden rules for SQL optimization and suggests using functions like approx_distinct() for large datasets.

Uploaded by

Manoj Kumar Shukla
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL

Optimization
#1
Use‘regexp_like’ to replace ‘LIKE’ clauses
#2
Use‘regexp_extract’ to replace ‘Case-when Like’
#3
Convert long list of IN clause into a temporary table
#4
Always order your JOINs from largest tables to smallest
tables
#5
Usesimple equi-joins
Two tables with date string e.g., ‘2020-09-01’, but one of the tables only has columns for year, month, day
values
#6
Always "GROUP BY" by the attribute/column with the
largest number of unique entities/values
#7
Avoid subqueries in WHERE clause
#8
Use Max instead of Rank
#9
Selecting Unnecessary Columns
#10
Using Functions on Indexed Columns
#11
OR Conditions Killing Indexes
#12
OR Conditions Killing Indexes
#13
Window Function Optimization
#14
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
#15
🎯 Golden Rules of SQL Optimization
✔ Avoid SELECT *
✔ Filter early
✔ Use proper indexes
✔ Avoid functions on indexed columns
✔ Use execution plan (EXPLAIN)
✔ Understand data distribution
✔ Reduce data movement
If You Found This
Helpful?

Follow for more amazing content


like this !

You might also like