Clustered Index Scans
-
Being a stronger developer then an admin I'm a little green on this but I have been reading up and trying to make my queries more 'sargable'. One thing I did recently was check the execution plan of a new query that will live inside a stored proc I'm creating that was doing a table scan because of a missing index on a join column. I found that it was just in the dev environment and updated the structure to match QA/Prod which has the index. So no more table scan w00t! So now I have a clustered index scan. I know index seeks are better but wasn't sure how/if it was possible to improve the performance by somehow changing this index scan coming from a join to an index seek.
Any thoughts on this are appreciated!
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Clustered Index Scan on large tables are *bad* too. You will need to modify your where clause to target the columns in the index or reduce somehow the filter (join) to do it.
Cheers,
* Noel -
Hmmm...the only thing in my where clause that is non-sargable i know of would be my statement. Here's the query...any ideas?
select
a.CorrBD,
a.AccountNo,
a.SubsidiaryNo,
a.Name1,
a.Name2,
a.Name3,
a.Name4,
a.Name5,
a.Name6,
a.RepID,
a.AccountClass,
b.IRAType,
b.PartBirthDate,
c.Frequency,
c.SourceCode,
c.CycleBeginDate,
c.CycleEndDate,
c.Amount,
c.TransmissionDesc
from
table1 a with (nolock)
inner join table2 b with (nolock) on
a.AccountNo = b.AccountNo
inner join table3 c with (nolock) on
a.AccountNo = c.AccountNo
where
a.field1 ='I' and
b.field2 = 0 and
c.field3 'O'
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
What indexes do you have defined on table1 and table2.
Just post the output of
exec sp_helpindex 'table_name'
* Noel -
I think I found the issue...
table1 is actually a view so when I join to tables 2 and 3 since it's a non indexed view it would cause the clustered index scan versus a seek.
Does that sound right?
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Joining to a view has some repercusions but for your info SQL Server "expands" the views by default and tries to come up with a plan. Problem is that "usually" the views do *more* than what you may need and you end up getting extra overhead. In general views of views of views of ... is a *bad* idea.
See if you can get what you want from base tables
* Noel -
Thanks Noel...
I did just that (converted to using base tables) and am still getting a clustered index scan. I've checked and there are non-unique indexes on all join columns. One thing I notice that seems a miss w/ me is that the PK on 'table1' is non-clustered. I've updated it and here's where I'm at w/ the query...
select
col1,
...
from
table1 a with(nolock)
inner join table2 x with(nolock) on
a.otherid = x.id
inner join table3 b with(nolock) on
b.Account = a.Account
inner join table4 c with(nolock) on
a.Account = c.Account
where
a.field1='I' and
b.field2 = '0' and
c.field3 'O'
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Ben,
You mentioned that there are indexes on all join columns, but are there indexes on the fields in the WHERE clause?
Regards,
Rubes -
Nope...
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Different subject, but it is usually bad idea to use NOLOCK hints, because you can get data that is uncommitted or may eventually be rolled back.
-
Agreed...but this isn't data that is being updated frequently by an application or anything so I put nolock's on there to make sure I'm not waiting on someone else that may be locking that table up for some other reason...
Cheers,Ben Sullins
bensullins.com
Beer is my primary key...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply