CODEWITHSUNDEEP
.netsql-serverperformanceentity-frameworklinq
Prashant Agrawal
Prashant Agrawal

Reputation: 179

Timeout Exception in SQL Server using via LINQ Query

select Max(ma.Coloumn2), mcp.column2 from Entity1 mr 
    JOIN Entity2 mcp on mr.column1 = mcp.column1 
    JOIN Entity3 ma on mcp.column1 = ma.column1 
    where mr.column3 = XXX GROUP by mcp.column2

I have a query mentioned above. In my Entity1 there are around 200,000 rows, in entity 3 there are 20k rows, in entity 2 there are only 40 rows.

This query executed in around 4 minutes in SQL, in LINQ it gives timeout exception. But when I rebuild the indexes of entity2 then it gives the result in micro seconds, and after 2-3 days problem occurs again and then I rebuild indexes then problem got away.

I am not able to determine the root cause of this issue. Can anyone help me with this?

Upvotes: 4

Views: 206

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30502

Don't forget to inform Entity Framework about your indexes. In your DbContext.OnModelCreating:

modelBuilder.Entity<Person>()
   .Property(person => person.Name)
   .IsRequired()
   .HasColumnAnnotation(IndexAnnotation.AnnotationName, 
        new IndexAnnotation(
            new IndexAttribute("IndexPersonNames", 0)));

This is fairly unreadable. Because I had to do this for a lot of properties, I decided to create an extension function:

static PrimitivePropertyConfiguration HasIndex(
    this PrimitivePropertyConfiguration property,
    string indexName,
    int columnOrder,
    bool uniqueIndexValues = false)
{
    var indexAttribute = new IndexAttribute(indexName, columnOrder)
    {
        IsUnique = uniqueIndexValues,
    };
    var indexAnnotation = new IndexAnnotation(indexAttribute);

    return property.hasColumnAnnotation(IndexAnnotation.AnnotationName, indexAnnotation);
}

The above modelBuilder statement will be:

 modelBuilder.Entity<Person>()
   .Property(person => person.Name)
   .IsRequired()
   .HasIndex("indexPersonNames", 0)));

or if you want to index on two columns:

.IsRequired()
.HasIndex("indexSurName", 0)         // first index by SurName
.HasIndex("indexFirstName", 1)       // then by FirstName

Upvotes: 1

Related Questions