Index sugggestion...

  • Hi All,

    I have below query and the table has 933575 rows in it. I would like to create a useful index to improve query performance.
    Below is my create index statement to satisfy the below query. Please suggest me if I have created the right index.
    One thing, I want to mention is, "isactive" columns has one and only value i.e. 1 for the entire table. If that is the case, is there any value keeping that "isactive" column
    in the index? please share your thoughts.

    -- sql query
    SELECT TOP(@ResultCount) [sample].sampleid,
             [sample].samplenumber,
             [sample].datedrawn, 
             [sample].equipmentlifeunitid,
             [sample].lubricantlifeunitid, 
             [sample].componentid
    FROM [sample]
    WHERE componentid = @componentId
       AND [sample].samplenumber <> @SampleNumber
       AND [sample].datedrawn <= @dateDrawn
       AND [sample].isactive = 1
       AND [sample].samplestatusid IN ( 13, 14, 15, 16,
                  17, 1 )

    --- index definition
    create index nc_sample_idx1 on sample (componentid,samplenumber ,datedrawn,isactive,samplestatusid) include (equipmentlifeunitid,lubricantlifeunitid);

    Thanks,

    Sam

  • Equality predicates first (ComponentID, IsActive, SampleStatusID), then inequality predicates.

    If ISActive is the same everywhere, better to remove it from the query, or make the index filtered. If you remove it from the index, you'll no longer have a covering index and will get lookups (or scans)

    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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply