December 16, 2017 at 9:17 pm
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
December 17, 2017 at 1:37 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply