5 Ways to Improve Your SQL Queries 

►► The Starter Guide for Modern Data → bit.ly/starter-mds Simplify “modern” architectures + better understand common tools & components --- 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). Research around topic this is covered really well in this article - discourse.getdbt.com/t/ctes-are-passthroughs-some-research/155. 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!

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

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!

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

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.

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

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!

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

#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!

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?

Many thanks for this great content

Informative.

Useful advice

Excellent.

Thanks, very well said.

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

Nice, thx

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

#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?

Appendix or index in a book?

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

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

no examples

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

you should have given samples man

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

If you used examples when it would be better