hi everyone hope you're all doing well in this video i wanted to switch it up a bit and just
offer some advice on things that i've seen in my career specifically as it relates to sql queries
i've worked in the past on sql server postgres sequel my sequel most recently on snowflake and
some of the cloud providers but i think a lot of these concepts apply and hopefully i can
share some of my experiences to help you know maybe you solve a problem or approach something
a slightly different way so number one start with a filtered data set i can't tell you how many
times a query has been performing really slow the user complains you know it takes forever to load
and the first thing i notice when hopping into the procedure or the query is that everything is being
done at the same time there's no separation of data and you're looking at full tables
there's no filtering whatsoever which means that every single join that's happening
every s case statement every transformation is happening on every record in every table
and a lot of times even if it's not everything it's too much and every extra record especially
on these row-based databases like sql server for example that can really blow up your query time
so the first thing i always suggest and that i always do is to start with a filtered set at the
very top you have to understand what is the actual granularity that you're concerned with if you're
writing a query to return something on a customer level you need to figure out the first thing what
are the customers you actually care about are you only looking at something in a specific time
frame are you only looking at certain customers in a region for this query isolate just those
customers in that example or whatever it is at the top whether it's an attempt table or something
and just start from there and then everything down as your query goes on you you reference that set
but that way you're only working with what you really need and what you care about you're not
processing anything on data you don't care about if in a particular query you only care about
customers you know in the west region why would you spend any processing power or any query time
running it against ones from the east or the south that you don't care about so something to think
about that's usually the number one thing i look for when looking to performance tune any query
number two kind of falls in line with the first one i mentioned use temp tables and ctes which
if you're not familiar stands with common table expressions at a high level i think using either
of these allows you to organize your query in a much better way similar to the first point isolate
just the data that you care about you could put that in a temp table and then you reference that
temp table in any future join so then everything else is you know you're joining on that you're not
doing any extra work another benefit of isolating things into temp tables is that it gives you the
ability to focus on maybe specific logic separate from everything else for example another culprit
of really slow performing queries is oftentimes people are trying to do way too much in just one
huge select statement those operations are going to happen on every single record
you can isolate that into a temp table one thing i really like about temp tables as well is debugging
you know if you have you know six temp tables you can fill the first three see where you're
at there's duplicates or there's an error you can figure out where it's happening along your query
again as opposed to having it all together it's really difficult to figure out where the problem
is another thing to consider here when working with these temp tables is only return the columns
that you need you don't need to return everything anything else other than what the final select
is actually going to need you're just wasting resources and cluttering up your query with things
that you don't actually need if you need to debug it and see them add it while you're debugging but
then remove it when you save it number three don't repeat yourself this is applicable to
every programming language you've probably heard it many times to the point where it's kind of
annoying but it's true you know if you're reading code and you see that you're doing the same thing
more than once that should be a sign that you probably can simplify this some way maybe
you're doing the same join multiple times or maybe you're converting a column type
multiple times you probably shouldn't need to do that so keep an eye on that if you find yourself
doing things more than once definitely hone in on that and figure out how you can simplify it
number four use indexes especially especially on the road based databases like sql server
use indexes where you can if you don't feel comfortable creating them yourself understand
what the indexes are on the tables you're working with the primary keys you know clustered indexes
non-clustered indexes which are more of like a reference point foreign key relationships
understanding these things will help the execution plan find what it needs to much quicker
and ultimately improve your performance i've seen some queries you know that were taking
several minutes to run jump down to a second or a couple of seconds just by adding indexes correctly
it's important that you understand how to do it because you can add indexes and make the problem
worse if you don't know what you're doing getting into the weeds and understanding how this works
and using them effectively will really improve your your query performance the two big ones
that you'll often see are clustered indexes and non-clustered indexes i think the best way to
understand the difference is through the example of a book a book has two
ways to reference information you have the table of contents and you have an appendix
the table of contents lists things in sequential order it says chapter one starts on page one
chapter two is on page 10 et cetera et cetera et cetera and they're all ordered by a certain
criteria in the case of a book they're all ordered by a page in the case of sql world
you can think of the index as the ordering of the table most of the time this is on an id and if
you're saying the id is the index that's kind of like saying the book aka the table is ordered by
id so you start from the lowest to the highest or whatever the order is and that's how the book is
structured or the table is structured so when a query comes through it says okay i need to find
id number 10 it knows exactly where to look i can find out very quickly where this record
where this row exists based on the id because i know the order of it i know how it's structured
number two non-clustered indexes those are more like an appendix you can create non-clustered
indexes on multiple columns and different ways but really it's more of a reference it's not saying
you know the book or the table is is structured by this way but you can find what you need by
by using it as a reference point let's say a book as the topic of sales if you were someone
who wanted to figure out where all the sales pages were you would go to the appendix and
you would see those pages and then you would look in those and see if that's what you need
in a similar way creating a non-clustered index creates those appendix-like reference points
for your table let's say you have an orders table and you create a non-clustering index on
customer that is basically saying we're going to create a separate reference guide for the
execution plan to go through anytime you see that joint it's going to know quickly you know where it
should probably be looking and find it much faster so those are the ways you can incorporate that i i
encourage you to learn more about those if you're not comfortable with them they can really really
improve your performance time you know in the example of using a temp table i mentioned you can
use indexes if you just add a generic temp table to your query and it's it's sitting there and you
insert records technically it's an unstructured table of data there's no organization there's no
referencing so the joins may not be super fast depending on what you know what you're
doing you may have a smaller result set because maybe you filter that temp table down in terms
of execution plan there you know there's nothing really helping it out but adding indexes such as
clustered or non-clustered on temp tables will help that as well but you know test it around
yourself there's no one-size-fits-all sometimes you add an index and it slows it down sometimes it
helps significantly you know so just play around with it and and hopefully that'll help you out
and number five the last one be consistent and this is maybe just a pet peeve of mine
but i think it's really important to be organized and consistent in what you're creating whether
it's indentation spacing naming structure there's obviously a ton of ways to to do the same thing to
accomplish the same output but whatever you decide do it the same way throughout don't be you know
adding three indentations on one place and one on the other you know putting things on separate
lines one part of your query but not in the other i just thinks it keeps it much cleaner it's easier
to read and i think it's it's like night and day when you open up somebody's procedure that
is very consistent and i'm structured well and someone's who is just all over the place
it's like walking into a room that's completely unorganized compared to somebody who's got
all the things in the right places and tucked away and you know you know exactly where things are
so i think that's a very subtle detail that can really separate you in terms of how your work
is viewed and ultimately just don't be lazy i think that i think that's what it comes down to
so that's it for this video i know it's a little different a lot of information hopefully you guys
found some value out of this i'm really curious to hear maybe some other things that you guys
have found in your experience in addition to these things and you know feel free to leave a comment
and uh you know let's talk about it i'll likely do some more of these if you find these helpful
everybody's got their own opinions on their own ways of doing things but one way to get
better is to just talk about it and bounce ideas off people so so yeah if you found this
helpful give it a thumbs up subscribe comment be very much appreciated thanks for watching