January 29, 2014 at 6:23 am
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
January 29, 2014 at 6:33 am
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
January 29, 2014 at 6:35 am
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
January 30, 2014 at 2:11 am
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.
January 30, 2014 at 2:36 am
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
February 17, 2014 at 9:11 am
Don't use the *, only select the columns you need.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply