December 10, 2015 at 3:23 pm
We have 4 procedures like below:
select * from article where
websiteid = @websiteid
isfeatured = 1
startdate >= @startdate
enddate <= @enddate
isactive = 1
select * from article where
websiteid = @websiteid
istopstory = 1
startdate >= @startdate
enddate <= @enddate
isactive = 1
select * from article where
websiteid = @websiteid
isbreakingnews = 1
startdate >= @startdate
enddate <= @enddate
isactive = 1
select * from article where
websiteid = @websiteid
islatestnews = 1
startdate >= @startdate
enddate <= @enddate
isactive = 1
From above we can see that 4 conditions are common in where clause of above procedures. Article table is currently having 1 lac records, how indexes should be created considering performance aspects ?
December 10, 2015 at 8:59 pm
Not sure if the website id is an identity column, if it is then create a clustered index on that
for the common ones create a non-clustered index and don't include websiteid as it will be inherited from clustered index i.e. On (IsActive,StartDate,EndDate)
and check the performance, if still it is not using Non clustered then you may create the above nonclustered index with including columns (islatestnews,isbreakingstory,isfeaturedstory..)
This might give you better performance, but always try to check the plans and come to conclusion.
December 10, 2015 at 9:54 pm
Without data there is not guarantee that this is the best index to create but I am pretty sure its close
Clustered index on wesbiteid
Nonclustered index on Startdate , enddate with included columns( isfeatured ,istopstory ,isbreakingnews ,islatestnews )
Not sure if isactive column has any reasonable carnality or distribution so you might not need an index on it .
December 10, 2015 at 11:20 pm
There is a separate "Website" table which has websiteid as a primary key and identity column.
and Article table has articleid as a primary key and identity column and in this table websiteid is stored.
So should I include websiteid in the index for table Article ?
December 11, 2015 at 2:00 am
Just based on those queries and no other:
Index ON Article (websiteid, IsActive, startdate, enddate) include (isfeatured ,istopstory ,isbreakingnews ,islatestnews)
Or, as an alternative, if the filter for IsActive is ALWAYS IsActive = 1 and the column isn't needed in the SELECT
ON Article (websiteid, startdate, enddate) include (isfeatured ,istopstory ,isbreakingnews ,islatestnews) WHERE IsActive = 1
SQL usually won't use more than one index per table per query, so if you put an index that only has some of the columns from the WHERE clause, you're forcing it to do a seek, then a lookup, then a secondary filter, which is inefficient.
I'd also suggest change those queries so they're not doing a SELECT * , and are just returning the columns that are needed.
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
December 11, 2015 at 6:59 am
I agree with everything Gail says (shocker).
However, if you don't already have a clustered index, then maybe using the same key structure she suggests will make for a good cluster. Then you don't have to sweat the INCLUDE since it'll already be there.
Assuming these are your most common paths for accessing the data, that's frequently the best place to put a clustered index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2015 at 10:08 am
Thank you for valuable suggestions, it worked !!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply