Replacing cursors using joins in sql server Part 64 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

welcome to prism technologies I am
venket this is part 64 of sequel server
video series in this video we'll discuss
about replacing cursors using joins in
part 63 we have discussed about cursors
the example in part 63 took around 43
seconds on my machine to complete if we
haven't watched part 63 I would strongly
encourage you to do so before proceeding
with this session in this video we'll
discuss about rewriting the same example
using a join so if you remember in part
63 we updated the unit price in TBL
product sales table to 55 when the
product name is product 55 if the
product name is product - 65 then we
updated the unit price to 65 on the
other hand if the product name starts
with product - 100 we changed or we
updated the unit price to 1000 let's see
have to achieve the same thing using
joins and see how fast that query will
be so what do we want to do we want to
update TBL product sales table within
that we want to update unit price so set
unit price is equal to I'm going to use
the case statement but we'll come to
that in just a bit
first let's specify the tables so in the
from class we specify products sales
table so I want to update TBL product
sales and I'm saying from TBL
product sales and to find out the name
the name is not present in TBL product
sales table its present in TBL products
table so I have to join with the TBL
products table and specify the join
condition what is the common column
between the two tables in TBL products
the column is ID and within ppl product
sales its product ID so that's the
common column between these two tables
based on which we are joining them and
look at this so what do I want to do I
want to update the unit price - so I'm
going to use a case statement here so
case fan
name of the product is equal to ok let's
copy it from the cursors you know query
so if the name is product 55 then what
we want to do then we want to update the
unit price to 55 along the same lines if
the product name is 65 then we want to
update the unit price to 65 and along
the same lines when name like if it
starts with product - 100 then we want
to set the unit price to 1000 okay so
that's our condition and then we
basically want to end the case statement
okay so it's a very simple case
statement we have discussed about case
statements in the previous session of
this video series if you haven't watched
that I would strongly encourage you to
do so okay so we have that there and in
the Vale clause we can specify what
products we want to update since we now
have access to the name column as we are
using a join we can also filter the rows
we don't want to retrieve all the
600,000 rows from TBL product sales
table we can filter them using the
bearclaw's so I'm going to specify where
name is equal to you know product - 55
or you know let me copy that or where
name you know name is equal to product
65 or finally name like product - 100
okay so whatever ROS matches these
criteria retrieve them and update their
unit price to you know fifty five the
product name is product - 55 or 65 you
know depending on these conditions so
let's go ahead and run this query and
see how long is that going to take now
remember in the previous session it took
around 43 seconds to update you know to
process the rules on a row by row basis
using cursors let me execute this now
look at this executing query it took
around two seconds and how many rules
are updated 1694 rows are updated now to
check our results we can make use of the
same query that used in the previous
session so when I execute this query we
should get all the rows and look at that
whatever has 100 you know name patent
product - 100 and then anything after
that the unit price is updated to 1000
let's check it for product 55 and 65
plus f5 look at that product 55 all of
them are updated to 55 and 65 sir
updated to 65 okay so the update query
worked as expected and look at this the
amount of performance varies 2 seconds
and varies 43 seconds there is a drastic
improving performance and that is
because we are using joins that's one
thing joints work much better because
they operate on drawers on a set basis
okay whatever rules meet this criteria
they are updated you know in in sets
rather than processing the rows you know
individually on a row by row basis and
another thing is since we are using the
where clause we are not retrieving
600,000 rose from TBL product sales
instead whatever rows matches whatever
are the common rows between these two
tables and then we also have a ver plus
so it reduces the rows - 1694 that's why
this query is very fast okay we can even
remove this Faircloth so if you if we
remove this where clause then what's
going to happen we get 600 thousand
records okay but still since we are
using a join it's going to be very fast
you know so but then if I you know if I
remove this where Plus then what's going
to happen we get 600,000 matching rows
so if we get 600,000 matching rows look
at this you are saying
that name unit price 255 and name
matters to product 55 otherwise 65
otherwise thousand if that row if the
name of the product doesn't match to any
of these rules okay then the unit price
will be updated to now and we want to
avoid that because since we don't use
this we get other you know if we are not
using this where clause we get the other
rows as well and if the name is not
matching any of these three things then
we don't want the unit price to be
updated to now that's why what you do is
have an else class if the name doesn't
match any of these three then what I
want I want back the unit price you know
just update with whatever unit price
that's already there okay but usually we
don't do this because we already have
access to the name column as we are
joining these two tables you know you
can simply filter the rows but even if
we don't filter even if we are
processing all the 600,000 rows and I'm
doing that using your joint let us see
how long is this query going to take
okay now when we execute this update
query it's going to update 600,000
records because in TBL product sales we
have 600,000 records and there is a
matching row for every row in TBL
product sales you know a matching row is
present in TBL products that's why this
is going to update 600,000 rows now let
me go ahead and run this
look at this execute in query it's
definitely more going to be more than 2
seconds remember the previous query
using cursors took 43 seconds but look
at this query completed successfully and
how long it took 6 seconds and how many
rows has is affected 600,000 rows okay
though it's processing 600,000 rows you
know we still have you know we still
have the data processed in 6 seconds but
whereas when we do it with cursors using
a row by row you know when we are
processing rows on a row by row basis it
took around 43 seconds ok now let's
quickly check if the data has been
updated as we expect okay
so first I want to see for product 55
and product 65 so that's the same 55 and
65 and let's check for the other things
where you know name pattern it's product
100 so that's 1,000 and let's check
anything that doesn't match these things
because we don't want that to be updated
to null so let me select 10 star look at
this
for the other products the unit prices
are the same they were not updated to
now now whenever you don't have the
where clause make sure you have this
else part otherwise what's going to
happen if that condition is not matching
look at look at what's gonna happen if I
execute this update query you know some
of the rows will not match these
conditions for all those rows the unit
price will become now because none of
these conditions are met so a null will
be returned and unit price will be
updated to now you know all these rules
will work as expected for 55 you know
they will be let's see you know for any
product that product - 100 that's okay
and for product 55 and product that 65
also it will be fine the data will be
fine but check you know top 10 maybe
from product sales table check the unit
price it will be now and we don't want
that to be happening that's why whenever
you use a case statement and if you are
expecting rows that may not meet this
criteria and if you want that data to be
updated
you know correctly make sure you have
your else part as well within the case
statement so when I execute this query
obviously you have seen that there is a
drastic improvement in performance okay
so this can you know you can imagine the
amount of impact that cursors is having
on performance cursor should always be
used as a last option if you don't have
any other way of achieving what you want
to achieve only then make use of cursors
or if it's a throwaway script you just
want to run it one time and if and if
performance it's really not a concern
maybe we can make use of curses but real
time production code you know you know
how bad the curses are for performance
though so they should be avoided as much
as we can on this slide you can find
resources for asp.net c-sharp and sequel
server interview questions that's it for
today thank you for listening have a
great