Timeout Exception in SQL Server using via LINQ Query

Question

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?

Which means, this is a maintenance problem, not a LINQ problem. LINQ isn't a replacement for SQL anyway. You shouldn't use it like this. Create proper entities and relations so you don't have to use JOINs to recreate them — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 8, 2018 at 9:05
This is often a parameter sniffing issue. Does the data change frequently in this table? Does the value of XXX change frequently? — Nick.Mc
– Nick.Mc, Commented Aug 8, 2018 at 9:06
@Nick.McDermaid yes XXX is parameter and that changes frequently, but the data of entity2 does not change frequently — Prashant Agrawal
– Prashant Agrawal, Commented Aug 8, 2018 at 9:14
If, for different values of XXX, the number of records in Entity1 varies wildly, this could be parameter sniffing — Nick.Mc
– Nick.Mc, Commented Aug 8, 2018 at 9:37

Harald Coppoolse · Accepted Answer · 2018-08-08 14:57:25Z

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

Collectives™ on Stack Overflow

Timeout Exception in SQL Server using via LINQ Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related