SQL Optimizations

Once you know the order of execution in sql works:
https://wetrustindata.com/sql_order_of_execution/

You want to go for more optimizations in case your query takes so much time. Here some tips from beginner to advanced:

• BEGINNER

1. Select the columns that you need instead of SELECT *.

2. Use the correct type of joins. Also, instead of joining the whole table, join only the amount of columns that you will need for the final dataset.

3. Be aware of the LIKE operator in the WHERE clause as it has to parse and match the pattern against the whole row.

4. Avoid SELECT DISTINCT if is not neccesary.

5. Use UNION ALL instead of UNION clause if you know 100% that you will not have duplicates by making the UNION between two tables.

6. Allocate the correct datatype to the column. If you know that your record will have 5 CHARS. Assign a VARCHAR(5) instead of VARCHAR(255) or VARCHAR(MAX). It starts to be beneficial when the table starts to grow.

7. Use of indexes where the columns are more used in the WHERE and ORDER BY clauses.

• INTERMEDIATE

1. Instead of spaguetti code, try to refactor it with subqueries and CTE’s. For most cases using CTE’s will be more efficient than using subqueries.

2. If possible, use integer instead of string columns to join tables. The best would be to join it with hash keys and hash diffs as part of the data vault methodology.
   https://wetrustindata.com/data_vault_with_snowflake/

3. Take care with the MERGE scripts and the NOT EXIST clause

4. Analyze the query history in Snowflake or the query optimizer in other SQL platforms to know where you might have bottlenecks in the queries. Check in the diagram of joins where the query may struggle.

5. If the query is performed in the cloud, increase the number of CPU’S running. Scale up the computing power in Snowflake if every optimization that you tried in the query does not make faster the query.