The right way to manage database indexes with Entity Framework

Question

I'm building my application with Entity Framework (model first principle). I'm also using MS SQL Server 2008 to store all the data of my application.

After some time of developing I have the following code:

public partial class EventInfo
{
    #region Primitive Properties

    public virtual int Id
    {
        get;
        set;
    }

    public virtual string EventName
    {
        get;
        set;
    }

    public virtual string EventKey
    {
        get;
        set;
    }

    public virtual System.DateTime DateStamp
    {
        get;
        set;
    }

    #endregion
}

And Visual Studio database designer have created special chunk of sql code to map this entity to the database:

-- Creating table 'EventInfoSet'
CREATE TABLE [dbo].[EventInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [EventName] nvarchar(max)  NOT NULL,
    [EventKey] nchar(32)  NOT NULL,
    [DateStamp] datetime  NOT NULL
);

And of course, an index for Id property

-- Creating primary key on [Id] in table 'EventInfoSet'
ALTER TABLE [dbo].[EventInfoSet]
ADD CONSTRAINT [PK_EventInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);

The EventKey is string, and actually I use it to store md5 hash (in string representation). But the thing is that my main code looks like:

    int cnt = context.EventInfoSet.Where(e => e.EventKey == eventKey).Count();

and

    int cnt = context.EventInfoSet.Where(e => e.EventKey == eventKey && e.DateStamp >= dateFrom && e.DateStamp < dateTo).Count();

eventKey here is a string variable. As you can see, I often deal with EventKey property. But my table could contain huge amount of records (up to 5M). And I need this code to work as fast as possible. I didn't find any option to mark EventKey as index property in designer. And I want to know:

how can I make things faster? do I have to worry about it at all with my code?
is there any gentle way to force .NET development environment generate index field automatically?
if there is no gentle way, and I have to update it manually, how I can organize things better to do it automatically?
may be you give me a link to the article explaining all these things with indexes, cos my knowledge lacks here.

Thanks!

See these other [SO questions on EF model first](http://stackoverflow.com/questions/3333674/entity-framework-4-add-index-in-model-first-design) for a few ideas — marc_s, Mar 22 '11 at 06:31

score 5 · Accepted Answer · answered Mar 22 '11 at 06:30

5

And of course, an index for Id property

Why 'of course'? If your main access, as you yourself admit, is to count by EventKey, or EventKey and DateStamp then your best clustered key is (EventKey, DateStamp), not ID:

CREATE CLUSTERED INDEX cdx_EventInfoSet 
    ON [dbo].[EventInfoSet] ([EventKey], [DateStamp]);
ALTER TABLE [dbo].[EventInfoSet] 
   ADD CONSTRAINT [PK_EventInfoSet]     
   PRIMARY KEY NONCLUSTERED ([Id] ASC);

Remember, clustered key and primary key are two distinct, unrelated, concepts. You should read Designing Indexes first (including all the subtopics in the link).

answered Mar 22 '11 at 06:30

Remus Rusanu

288,378
40
442
569

I was just going to day - a compound clustered index on (EventKey,DateStamp) is probably not a very wise choice.... too wide, compound, possibly changing, and definitely not ever-increasing... – marc_s Mar 22 '11 at 06:33
Access pattern trumps index width and fragmentation. Altough both example queries are aggregates and could be served by a non-clustered index just as well, I would expect more similar access patterns that would require more and more included fields to offer coverage. Hence ultimatel I expect this to be the clustered index. – Remus Rusanu Mar 22 '11 at 06:37
2

but you're right, in lack of more information, a more conservative recommendation would be to just add a non-clustered index on `(EventKey, DateStamp)`. – Remus Rusanu Mar 22 '11 at 06:39

score 5 · Answer 2 · answered Mar 22 '11 at 08:26

Entity framework will not create index for you. Entity framework only creates skeleton of the database. If you want to tweak your database for performance (like adding indexes) you must do it at your own. After that you can switch either to database first or you can use Entity Designer Database Generation Power Pack to update your database instead of recreating it (you will need at least VS 2010 Premium).

The right way to manage database indexes with Entity Framework

2 Answers2