Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Thursday, July 24, 2014 4:35 AM
Points: 14, Visits: 87
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 @ 12:02 PM
Points: 12,890, Visits: 31,853
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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 @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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: Thursday, July 24, 2014 4:35 AM
Points: 14, Visits: 87
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 @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:29 AM
Points: 73, Visits: 154
Don't use the *, only select the columns you need.
Post #1542188
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse