The best method to use Entity Framework to manage database indexes

entity-framework indexing sql-server

Question

I'm using Entity Framework to create my application (model first principle). Additionally, I'm utilizing MS SQL Server 2008 to house all of my application's data.

I finally have the following code after some hours of development:

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
}

Additionally, a unique section of SQL code was written by the Visual Studio database designer to map this object 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
);

naturally, an index for the 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 a string that I use to store md5 hash values (in string representation). However, my primary code is as follows:

    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 a string variable is present. You can see that I often work with the EventKey property. However, my table can have a vast number of records (up to 5M). Additionally, I need this code to run as quickly as feasible. In the designer, I couldn't discover a way to designate EventKey as an index attribute. And I'm curious about:

  1. How can I speed up the process? Does my code make me liable in any way?
  2. Is there a tactful method to have the.NET development environment automatically produce index fields?
  3. How can I arrange things better such that it updates automatically if there isn't a nice way to do it?
  4. Given my lack of understanding, could you perhaps provide me with a link to an essay outlining all of these concepts with indexes?

Thanks!

1
6
3/22/2011 6:28:04 AM

Accepted Answer

And of course, an index for Id property

"Of course"—why? If, as you concede, counting by is your primary method of accessEventKey , orEventKey and DateStamp then () is your ideal clustered key.EventKey , DateStamp ), notID :

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

Keep in mind that the ideas of a clustered key and a main key are separate and unrelated. You should first read Making Indexes (including all the subtopics in the link).

5
3/22/2011 6:30:38 AM

Popular Answer

Your index will not be created by Entity Framework. The database is merely created as a skeleton by entity framework. You must modify your database on your own if you wish to improve performance (for example, by adding indexes). After that, you can either switch to databases right away or utilize Database Generation Power Pack for Entity Designer to update your databases rather than starting from scratch (you will need at least VS 2010 Premium).



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow