What to choose for performance SubQuery or Joins Part 62 Video Lecture

hello welcome to prism technologies I am
venket this is part 62 of sequel server
video series in this video we'll discuss
about what to choose for performance sub
queries are joints let's flip to sequel
server management studio I have two
tables here TBL products and TBL product
sales tables in TBL products table I've
got around 400,000 and TBL product sales
table I've got 600 thousand records I
have inserted this huge amounts of data
into these tables using automated sequel
scripts we have discussed about how to
do that in a previous session of this
video series if you haven't watched that
I would strongly encourage you to do so
before continuing with the session now
according to MSDN in most cases there's
usually no performance difference
between queries that use the sub queries
and equivalent queries that are formed
using joins let's understand this with
an example let's flip to sequel server
management studio now let's say we want
to write a query which lists the list of
all products that we have sold at least
once let's say I want ID name and
description so how do we write that
query so look at this select ID name
description from TBL products table
where ID in select Product ID from TBL
product sales table so if the ID is
present in TBL product sales table then
we know for sure we have sold that
product in which case we are retrieving
ID name and description okay now if you
look at this this query is formed using
a sub-query okay now let's go ahead and
execute this query and see how long is
that going to take
so I'm executing now look at this the
query is executing still it's already
three seconds four five it should take
around nine seconds I have oh it's
actually taking around seven seconds
look at this how many rows we have we
have around three hundred six thousand
one hundred ninety nine rows in how much
time it took around seven seconds okay
so seven seconds and we have three
hundred six thousand one hundred ninety
nine rows okay now let's see how to form
the same query but using joins no I want
the same result but I want to do that
using joints so if you look at this here
we are
electing the product ID name and
description from TBL product stable by
joining with TBL product sales paper on
the common column what is the common
column ID in TBL products and product ID
in TBL product sales table and we are
using distinct here because you know for
every match we get a row from the left
table so but we don't want duplicates so
we are using the distinct keyword there
so let me go ahead and run this look at
actually let me stop this we want to
clear the query cache otherwise this
query cache is going to impact the
numbers that we get okay so let me drop
this query cache okay so what is this
query cache you know when we execute a
query a query execution plan will be
generated we don't want that plan to be
reused you know just to make sure that
our our performance testing numbers are
right so I'm cleaning the query cache
and the execution plan cache here okay
so once we have done that now let's go
ahead and run this query and see how
long this will actually take to run so
it's executing now it's already two
seconds still executing four seconds
let's see how long is this going to take
so this has also taken around seven
seconds and look at that the same number
of rows three hundred six thousand one
hundred and ninety nine rows and that's
what we have got using the first query
so here whether you use sub query or
whether you're using a join it is not
really making a difference okay but in
some cases again this is present on MSDN
in some cases where existence must be
checked a join produces better
performance otherwise the nested query
must be processed for each result of the
outer query in such cases a joint
approach would actually yield better
results let's understand this with an
example
look at this here now let's say I won't
retrieve all the products which we have
not sold at least once okay how do we
write that using the sub query look at
this we are we are doing that using a
sub query here select ID name
description from TBL products we're not
exist' remember the exists function this
exists function will actually return
true or false
okay when will it return true if there
is you know if this query returns at
least one row then it's going to return
true otherwise this exist function is
going to return false and look at the
sub query here select star from TBL
product sales table where Product ID is
equal to TBL products dot ID now if you
look at this query it is a correlated
sub-query and we have discussed about
correlated subqueries in the previous
sessions of this video series if you
haven't watched that I would strongly
encourage you to do so okay now look at
this this query will now have to be
executed for every row that is retrieved
by the outer query so in these cases
look at this according to MSDN in some
cases where existence must be checked so
here we are checking for the existence
using exist keyword and now you know
here the nested query must be processed
for each results of the outer query in
this case a joint approach would yield
better results let's see if that's the
case so here since the inner query
that's our sub query has to be executed
for every row that is produced by the
outer query you know a join would
actually produce better results that's
what MSDN says but let's see what
actually happens when you execute this
query let's see how long it's going to
take before that let's clean the query
cache and execution plan cache and let's
go ahead and execute this query now so
let's see how many seconds is this going
to take so it's running now one second
two seconds three seconds four seconds
so it has taken the sub query has taken
around four seconds look at this we have
got 90 3801 records okay so four seconds
and it has taken an hour 90 3801 records
okay so that's what the number of flows
retrieve now we have an equivalent query
but formed using joints so we have a
very trimming ID name description from
TBL products we are doing a left join
with TBL product sales table on the
common column which is ID in TBL
products and product ID in TBL products
sales table and look at this we are
saying where TBL products sales that
product ID is now okay now we have
discussed about joints and intelligent
joints again in this video series if you
don't know how to do that please watch
those videos before proceeding with this
video so this left join with this check
where TBL products sales to product IDs
now this is going to return all the non
matching rows from the left table in
this case from TBL products table so we
are going to get a list of products that
we have not sold at least once so let me
go ahead and execute this query see how
many seconds is this going today so it
took one second two seconds look at this
it actually took two seconds now okay so
the join here actually produced within
two seconds and huh and look at this it
took around 93 thousand it retrieved
around ninety three thousand eight
hundred root 1 rows now I'm not sure if
we have deleted the cache let's go ahead
and delete the cache and run that query
again because I don't think I have done
that okay let me try this now and now
let's execute this okay look at this now
it's taking two seconds three seconds
okay so this the join has actually
executed in three seconds and it has
given me the same number of rows
ninety-three thousand eight hundred and
one record okay now let me again clear
the cache let's execute the query once
again that uses sub query and see what's
going to happen okay so one two three
and look at this eat this time it has
actually retrieved those records in
three seconds itself so it's a sub query
okay actually you know I have tested
this several times
but you know though here we you know
this query is actually checking for
existence and this query is executed
every time for the outer query it
actually produced the same amount of
time on my machine look at that three
seconds ninety-three thousand eight
hundred and one records and even the
query that uses joins is producing the
same result now but what does MSDN say
according to MSDN in some cases where
existence must be checked a join
produces better performance otherwise
the nested query must be processed for
each result of the outer query in such
case a joint approach would yield better
results but our tests prove that both of
the queries are producing the same
result and they are taking the same
amount amount of time okay that is
because in general you know if you look
at this if you are some if somebody asks
you in an interview which is better for
performance joins or sub queries in
general it's actually joins join so much
faster than sub queries but in reality
it all depends on the execution plan
that is generated by sequel server it
does not matter how we have returned the
query did we use sub queries or did we
use joins to write that query doesn't
really matter because sequel server will
always transfer that query based on an
execution plan if it is smart enough and
if if the sequel server generates the
same plan for both the queries then we
are going to get the same result and
that's what is exactly happening here
now though I might have written this
query using a sub query you know sixel
server has actually transformed it you
know to you know produce an execution
plan you know which is same as that of
you know the query that uses joints
that's why I'm getting the same result
look at this let me run this once again
we get the same result so I'm going to
clear the cache but if it doesn't
produce the same result then definitely
in that case a join will be much better
than a sub query in general that's the
common notion you know joints work much
faster than sub queries look at this the
sub query retrieved the same number of
rows ninety-three thousand eight hundred
and one in three seconds
that look at the joint but before that
make sure we delete the cache okay and
let's run this this is the query using
joints look at that one two and three
seconds okay same number of rows so in
this case you know the sequel server is
actually generating the same plan for
both the queries that's why we're
getting the same result okay now instead
of going by theory okay somebody says
joints are much better than sub queries
instead of simply going by that theory
in fact we should actually be testing
queries for performance and then tuning
them as needed okay so I would say
rather than going by this theory you
know that joins a much better than sub
queries turn on client statistics and
execution plan to see the performance of
each option for ourselves and then let's
make a decision so how do we turn on you
know the client statistics and execution
plan it's very easy to do in sync
receiver management studio all you have
to do is look at this button this is
called you know when you click on this
button it's going to include the client
statistics and there is another button
here include actual execution plan when
I click that button I'm going to get the
actual execution plan as well
so now if I execute this query for
example the query that's making use of
sub query I get the result and then I
should also get the execution plan and
client statistics okay now how to read
these client statistics and execution
plans and then make a decision we'll
talk about this in a later video session
okay
on this slide you can find resources
phase B dotnet c-sharp and sequence of
interview questions that's it for today
thank you for listening
have a great day