How to eliminate KeyLookUp in queryplan

Question

Post reply

How to eliminate KeyLookUp in queryplan

The Wisenut

Say Hey Kid

Points: 696
More actions
March 5, 2008 at 2:59 pm

#183459

Hi,

Facing a situation where the execution plan shows both an index seek as well as KeyLookUp. The cost for the Index seek is 50% where the rest 50% is for the KeyLookUp

SELECT

ph.ProductId

,ph.CLientID

, ph.PolicyType

FROM

PolicyHeaders ph

INNER JOIN Products pr

ON pr.ProductId = ph.ProductId

AND ph.ClientID = 'C0000818'

AND ph.PolicyType='PA'

The primary key of this table is PolicID and is not included in this select. I have a Non Clustered Index which is defined as follows

CREATE NONCLUSTERED INDEX [IX_ClientID] ON [PolicyHeaders]

(

[ClientID] ASC,

[PolicyType] ASC

)

If add the ProductID to the index I get a IndexScan. Please advice how I could remove the KeyLookup

Thanks

Vinoj

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

You'll need to include the ProductID in the index to do that...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

The Wisenut Say Hey Kid Points: 696 More actions · Answer 2

The Wisenut

Say Hey Kid

Points: 696

March 5, 2008 at 3:14 pm

#785800

Thanks. For some reason when i added the ProductID as the first column of the index and it gave an Index Scan. But when i added it as the last field in the index it gave me a full index seek. Thanks for the help

Vinoj

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

That's what "index tuning" is all about... getting the columns in the correct order to produce an Index Seek. Nice job.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

The index with ProductID first produces an index scan because ProductID is not one of the columns that are filtered on. A query can use an index seek if the columns in the where clause are a left-based subset of the columns in an index (a bit of a gneralisation, but true enough)

Edit: correctness.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

GilaMonster (3/6/2008)

The index with ProductID first produces an index scan because ProductID is not in the where clause.

Ummmm..... heh... What's that highlighted in red????

SELECT

ph.ProductId

,ph.CLientID

, ph.PolicyType

FROM

PolicyHeaders ph

INNER JOIN Products pr

ON pr.ProductId = ph.ProductId

AND ph.ClientID = 'C0000818'

AND ph.PolicyType='PA'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Perhaps I should have said 'not used in the filters' rather than not in the where clause (since there's no where clause)

The red highlight is a join to a lookup table on what I would assume is a foreign key relationship. Hence, not going to cut the rowset down, hence not useful for determining which rows in PolicyHeader are affected by the query.

Indexes are chosen to reduce the number of considered rows as fast as possible. The filters will do that, that join is highly unlikely to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

The join needs to be part of the covering index or you will only get a scan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

TheSQLGuru

SSC Guru

Points: 134017

March 7, 2008 at 8:30 am

#786716

The order of the columns is critical to optimal index performance. It is usually appropriate to put the columns in order of specificity, assuming all 3 columns (or at least the columns from left to right) are filtered on.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Jeff Moden (3/6/2008)

The join needs to be part of the covering index or you will only get a scan.

True, which is why I removed the suggestion about INCLUDE (and yes, I didn't see the join first time).

I would still suggest it as the third column in this case, because (and it's a guess without seeing the exec plan) I expect SQL to filter first and join second (since the filter reduces the rows which the join won't, if it's a join to a lookup table)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass