Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Avoid KeyLookup


Avoid KeyLookup

Author
Message
sivaramakrishnan G
sivaramakrishnan G
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 92
Dear All,
Find the following table and Query and index.I am Getting Keylookup in Query on Execution Plan.How to avoid this.Due to this when ever i have use this take Query Plan showing Key lookup.

If the index has to be modified kindly give your views.

CREATE TABLE SALARY_FIXED(
[SALARY_FIXED_ID] numeric(18,0) IDENTITY(1,1) NOT NULL,
[EMPLOYEE_ID] numeric(18,0) NOT NULL,
[COMPONENT_ID] numeric(10, 0) NOT NULL,
[AMOUNT] NUMERIC(23,3),
[START_DATE] datetime NULL,
[END_DATE] datetime NULL,
CONSTRAINT [PK_SALFIXID] PRIMARY KEY CLUSTERED
(
[SALARY_FIXED_ID] ASC
),
CONSTRAINT [UQ_COMPEMPID] UNIQUE NONCLUSTERED
(
[COMPONENT_ID] ASC,
[EMPLOYEE_ID] ASC
))

INSERT INTO SALARY_FIXED(EMPLOYEE_ID,COMPONENT_ID,AMOUNT)
SELECT 1,100,200
UNION
SELECT 1,200,500
UNION
SELECT 1,300,1000

SELECT * FROM SALARY_FIXED WHERE EMPLOYEE_ID=1 AND COMPONENT_ID=100

Regards
Siva
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38958
the key lookup, just by itself, is not a bad thing.

the query is using the unique index, but has to lookup the other values (due to the SELECT *)

to do that, it does a key loookup of the clustered index to return the other columns of data(COMPONENT_ID,AMOUNT,START_DATE,END_DATE).

any query that is doing SELECT * would most likely have that kind of lookup;
if you were returning a specific subset of columns, you might add an index with an include of those columns.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47243 Visits: 44377
sivaramakrishnan G (1/29/2014)
I am Getting Keylookup in Query on Execution Plan.How to avoid this.Due to this when ever i have use this take Query Plan showing Key lookup.


Why do you want to avoid it? Is it causing a problem? Is the query performance unacceptable?


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


sivaramakrishnan G
sivaramakrishnan G
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 92
Thanks Gila.One of the forum i Read need to avoid Keylook up.So that I have created scenario against my real case.I need one more information while updating one table its having clustered as well as non cluested index.If the update cost will be Higher or lower which one is adviceable.The update Query Index Seek is coming.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47243 Visits: 44377
Test your query against representative data volumes. Does it perform acceptably? If so, don't waste your time trying to fix something which isn't a problem.

If it doesn't perform acceptably, then identify the actual problem (which may or may not be related to the key lookup) and make the appropriate fixes.


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


chris.rogers00
chris.rogers00
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 187
Don't use the *, only select the columns you need.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search