January 14, 2008 at 11:27 am
I have a sproc thats taking some time to complete. I have run the execution plan on it and found the part taking the most time is a RID Lookup on a particular table. The table is joined upen a few times within a query in the sproc:
Here is a mockup of the code:
select ....... from tableA as A join tableB as B on A.col1 = B.col1
and A.col2 = B.col2
join TableC as c on ACCol3 = A.Col3
I want to create an index perhaps on table a, which is the problematic table with the RID lookup.
Do i need to create two indexes, one for each join tableA is included in, , eg : an index on col1 and Col2, and a seperate index on Col3,or one index, covering all columns tableA is joined on, eg: index on col1,col2 and col3? is it more effective creating indexes on columns in the where clause than in the join part of the query?
Note, tableA is not mentioned in the where clause
January 14, 2008 at 12:27 pm
First question I'd ask is, what are the additional search criteria? That will affect the index. Also, do you have clustered indexes already on these tables? If so, what columns?
Barring additional information, first blush, I'd put a compound index on Col1 & Col2.
However, you could still get a lookup depending on what columns are being selected. It's going to have to go & get those columns apart from the index unless the index being used is the cluster or the index being used is a covering index (has all the columns referenced).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2008 at 12:28 pm
AND, can't believe I forgot this, you need to look at the selectivity of the data in those columns to be sure an index will work well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply