Smart choice for primary key and clustered index on a table in SQL 2005 to boost performance of selecting single record or multiple records

Question

EDIT: I have added "Slug" column to address performance issues on specific record selection.

I have following columns in my table.

Id Int - Primary key (identity, clustered by default)
Slug varchar(100)
...
EntryDate DateTime

Most of the time, I'm ordering the select statement by EntryDate like below.

Select T.Id, T.Slug, ..., T.EntryDate
From (
    Select Id, Slug, ..., EntryDate,  
        Row_Number() Over (Order By EntryDate Desc, Id Desc) AS RowNum
    From TableName
    Where ...
) As T
Where T.RowNum Between ... And ...

I'm ordering it by EntryDate and Id in case there are duplicate EntryDates.

When I'm selecting A record, I do the following.

Select Id, Slug, ..., EntryDate
From TableName
Where Slug = @slug And Year(EntryDate) = @entryYear 
    And Month(EntryDate) = @entryMonth

I have a unique key of Slug & EntryDate.

What would be a smart choice of keys and indexes in my situation? I'm facing performance issues probably because I'm ordering by a column that is not clustered indexed.

Should I have Id set as non-clustered primary key and EntryDate as clustered index?

I appreciate all your help. Thanks.

EDIT:

I haven't tried adding non-clustered index on the EntryDate. Data inserted from back-end, so performance for insert isn't a big deal for me. Also, EntryDate is not always the date when it is inserted. It can be a past date. Back-end user picks the date.

Answer 1

Have you tried simply adding a non-clustered index on the entrydate to see what kind of performance gain you get?

Also, how often is new data added? and will new data that is added always be >= the last EntryDate?

Answer 2

You want to keep ID as a clustered index, as you will most likely join to the table off your id, and not entry date.

A simple non clustered index with just the date field would be fine to speed things up.

Answer 3

Clustering is a bit like "index paging", the index is "chunked" instead of simply being a long list. This is helpful when you've got a lot of data. The DB can search within cluster ranges, then find the individual record. It makes the index smaller, therefore faster to search, but less specific. Once if finds the correct spot in the cluster it then needs to search within the cluster.

It's faster with a lot of data, but slower with smaller data sets.

If you're not searching a lot using the primary key, then cluster the date and leave the primary key non-clustered. It really depends on how complex your queries are with joining other tables.

Answer 4

Based on the current table layout you want some indexes like this.

CREATE INDEX IX_YourTable_1 ON dbo.YourTable
(EntryDate, Id)
INCLUDE (SLug)
WITH (FILLFACTOR=90)

CREATE INDEX IX_YourTable_2 ON dbo.YourTable
(EntryDate, Slug)
INCLUDE (Id)
WITH (FILLFACTOR=80)

Add any other columns you are returning to the INCLUDE line.

Change your second query to something like this.

Select Id, Slug, ..., EntryDate
From TableName
Where Slug = @slug 
    AND EntryDate BETWEEN CAST(CAST(@EntryYear AS VARCHAR(4) + CAST(@EntryMonth AS VARCHAR(2)) + '01' AS DATE) AND DATEADD(mm, 1, CAST(CAST(@EntryYear AS VARCHAR(4) + CAST(@EntryMonth AS VARCHAR(2)) + '01' AS DATE))

The way your second query is currently written the index will never be used. If you can change the Slug column to a related table it will increase your performance and decrease your storage requirements.

Answer 5

A clustered index will only make any difference at all, if you are returning a bunch of records, and some the fields you return are not part of the index. Otherwise there's no benefit.

You need first to find out what the query plan tells you about why your current queries are slow. Without that, it's mostly idle speculation (which is usually counterproductive when optimizing queries.)

I wouldn't try anything (suggested by me or anyone else) without having a solid queryplan to compare with, to at least know if you're doing good or harm.