Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Avoid KeyLookup Expand / Collapse
Author
Message
Posted Wednesday, January 29, 2014 6:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 15, 2015 4:24 AM
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
Post #1535867
Posted Wednesday, January 29, 2014 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:52 PM
Points: 14,189, Visits: 37,086
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!
Post #1535873
Posted Wednesday, January 29, 2014 6:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:30 PM
Points: 44,477, Visits: 42,082
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

Post #1535876
Posted Thursday, January 30, 2014 2:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 15, 2015 4:24 AM
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.
Post #1536202
Posted Thursday, January 30, 2014 2:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:30 PM
Points: 44,477, Visits: 42,082
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

Post #1536216
Posted Monday, February 17, 2014 9:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 26, 2016 3:38 AM
Points: 76, Visits: 185
Don't use the *, only select the columns you need.
Post #1542188
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse