Скачать или смотреть 001 - Index Optimization (Scan Seek) - View Builder - SQL Master Data Management

In this 3 part series within a series (001-003), I tackle my View builder code. It takes 1 minute and 29 seconds to build 3500+ views.. and i think thats too slow. So, I break apart the code and add NONCLUSTERED INDEXes to quite a few tables.

This video is about Seeks and Scans.

============= Partial Transcript ======================

all right so the first thing I can do or
I was trying to do is for all my data
sets I was trying to rebuild views all
five views so the base view and then the
select insert update and delete view now
my main query returns ooh my bad 726
datasets so I'm trying to do create over
3,500 views and it's taking a minute in
29 seconds but I think that's slow I
think we could improve that performance
so the first thing we want to do is
basically attack this query slowly we
can use the execution plan to do that
but really you know we can be more
specific on what's going on so when I'm
trying to filter by the type ID which is
what the in statement will do I get a
scan on the primary key so I have this
other instance open and basically the
rule of thumb for non-clustered indexes
is everything in the aware include
what's in the Select so when the where
it was the type ID include the primary
key which is the data set ID so I can do
type ID include data set ID and for the
storage options in my system I want to
make sure that my all my indexes are on
the index file group so then I can go
back to my query run the execution plan
again and boom I'm over in seek land so
same things and I just kind of extend it
and use the estimated execution planner
and I see I got a scan on the data set
again but this is probably because of my
other items key lookups a couple seeks
in here realm realm type
so this is coming from this table value
function now like I said whatever is in
the where clause followed by what's in
our Select so when we have the key name
both in the Select and the where it we
want to actually index on it and the
practice for indexing is fewest unique
values to most unique values so there
are less family IDs than key names so
the family ID will be the first in the
list what else do we have
tenant ID on the type so the type is a
little different now there are two ways
to do this one of them is we can create
two indexes or we can be conservative
about it
so assured leave for the data set type I
know that I need non-clustered index for
the data set type where I'm filtering by
the tenant and the key name so tenant
and the key name and my family ID and
the type of family IDs in a where clause
and the type ID is in a select statement
because the types family will go
backwards oh I made a mistake there
where's the fam there we go
so then we'll put this on the index so
we have the tenant ID in the family ID
because the family is being passed down
to another table via the cross supply
and the key names also being filtered on
and returned and this kind of helps us
out because the key name is no little
folks on the top two which is what we
want and the data set ID is the type ID
is just being returned so I can build
that and then I can script as create and
remove the tenant ID and remove the
tenant oh my messed up my naming here
include type ID included type ID so
because of our consistent pattern we can
actually replace a type we want to do it
backwards so family with class and type
with family and that'll basically get
our next one where the class ID is going
to be filtered by the class table and we
want the key name and the family name
family ID back and I'll just go back and
show you what I'm talking about right
here even though we have all three of
these in the Select statement the class
ID is actually being sent down farther
so it's becomes like almost an
artificial wear so using those patterns
we can do that execute it so now we have
one on our family we want to take class
and turn it in the realm we're gonna
take family and turn it into class
execute it we want to turn class into
realm and it will take this and actually
we're only filtering on the key name
but we are asking for the cluster ID
back so we can add that to the includes
so now if I go back to my code and run
it we get seeks across the board let's
see what that scan turned into no this
is seek also so sometimes you can run
into like this giant cluster of like
trying to figure out well this isn't
aware this isn't a select but
realistically anything that's being
returned but also being joined on or
filtered by needs to go into that
primary piece of the of the of the index
and then anything that's returned
additional to what's being filtered on
needs to go into the include

==================================================

SQL Complete Affiliate Link:

https://secure.2checkout.com/order/ca...