Post reply

Avoid KeyLookup

  • January 29, 2014 at 6:23 am

    #282892

    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

    #1684280

    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!

  • January 29, 2014 at 6:35 am

    #1684283

    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

  • January 30, 2014 at 2:11 am

    #1684551

    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

    #1684564

    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

  • February 17, 2014 at 9:11 am

    #1689577

    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