5 Ways to Improve Your SQL Queries

-----

Learn a few tips to improve performance and overall design of your SQL queries.

Timestamps:
0:00 - Intro
0:27 - Tip #1
2:20 - Tip #2
3:52 - Tip #3
4:28 - Tip #4
8:19- Tip #5

Title & Tags:
5 Ways to Improve your SQL Queries | Performance Tuning and Design Approach
#kahandatasolutions #SQL #dataengineering

[KahanDataSolutions]

---
After spending more time working with modern data stacks, I wanted to add an updated note to this video. Some of these tips are focused on writing SQL in the context of traditional row-based databases such as SQL Server, MySQL, etc. as opposed to columnar platforms such as Snowflake.

The main update is in regards to point #1: Filtering a data set --

This rule is not _necessarily_ always still the case with some of the modern databases, such as Snowflake as the query plans have become really efficient and a "select *" is not always a no-no. The query processors can handle an insane amount of data without much of a performance hit (if written responsibly).


I still suggest the filtered approach when working with any of the row-based databases mentioned above, and the other tips still hold true regardless of data stack. I mainly just wanted to add this caveat to point #1 as my viewpoint has evolved a bit as I've learned more!

[adnanghafoor]

#1 Use filter Data Set
#2 Use temp tables/ CTEs
#3 Don't repeat yourself (Dry)
#4 Use indexes
#5 Be consistent

[cdaugherty]

Something that would add value here for me would be showing examples. I'm a little newer to SQL, so actually looking at examples would be helpful! This video may have been aimed at more advanced users, so that makes sense, but this is just my input!

Thanks for taking the time to make this and listen to our feedback!

[ericrobbins]

Just finding your channel. Excellent content. It may exist already, but this should be a whole series with examples of do's, dont's and why's

[marcinbadtke]

Than you for the video.

I would like to add one thing: use partitions. Better than indexes for sets of identical values.

[stopthink]

Sounds like great tips but as a beginner / intermediate it would be great to see some actual before / after code examples. It's easy to get lost without visuals.

[SAURABHKUMAR-ukgg]

This is the best indexing explanation I have seen anywhere on the internet. Period!

[sheshkumarbhombore]

This is just a theory! Wanted to see some examples :(

[Cog-pdqn]

I am newer to SQL, but for #1, wouldn't the query optimizer first pare down your data set by any WHERE clause found, regardless of whether that clause appears at the top to filter rows into a temp table, or down a ways in a longer query? Or -- is the optimizer really that useless?

[onethousandyearsofcultivat]

I dont understand the point 2# use temp tables.
Other advice i read says avoid temp tables like the plague.

So what gives?

[jonspell]

Thanks for this! I also like to put indexes on my temp tables if they get a little bigger (combining tips 2 and 4) And lots of comments!

[miguelvictoria]

Thanks :) went from more than 2 minutes query down to 750 ms average :D

[hananmostafa]

Could you please explain how can I make a loop in Presto sql?

[baloney_sandwich]

So u read from someone else blog and made a video outta it

[christheone]

#1, So valid, it's not even funny! I work on a data warehouse and let me tell you, this is rule number one for a reason!

[notanenglishperson]

#3
Let's say I have a query with 2 exact same subqueries, is it possible to write a subquery once and use it again somehow?

[jlbciriaco]

excellent information but boring as hell. Add some music or images man

[microscorpin]

This would work as a podcast instead of a video. No visual not examples

[AmericanPatriotsApparel-eu]

This video tells you to do things but it doesn't tell you how to do the things.

[Mustaq]

If you used examples when it would be better