Entity Framework query timeout but SQL is instant

Question

I have a couple of expressions that I run a Count() against in EF6. The generated SQL query winds up a somewhat complex one hitting a few tables with a few parameters passed in. However it runs in less than a second if I copy the SQL into SSMS.

In EF the Linq Query takes over 30 seconds and often simply throws a Connection Timeout exception.

Looking at the activity monitor I can see it appears as if the same query is being ran many thousands (if not millions) of times.

What appears to be hundreds of thousands of reads

The code triggering the queries is made up of a couple of expressions that are combined using LinqKit AsExpandable() and Invoke().

//properties is an IQueryable<Property> and checkDate a DateTime
int propertyCount = FilterCompliantOnDate(properties, checkDate).Count();



public IQueryable<Property> FilterCompliantOnDate(IQueryable<Property> properties, DateTime checkDate)
{
    // SelectedComplianceCategory is a local property (int?)
    return properties.AsExpandable().Where(p=>PropertyIsCompliant.Invoke(p, checkDate, SelectedComplianceCategory));
}



public static readonly Expression<Func<Property, DateTime, int?, bool>> PropertyIsCompliant = (p, checkDate, complianceCategory) =>
    CategoryComplianceRatings.Invoke(p, complianceCategory, checkDate).Any() &&
    CategoryComplianceRatings.Invoke(p, complianceCategory, checkDate)
        .All(cr => cr.ComplianceRating == ComplianceRating.Compliant);



private static readonly Expression<Func<Property, int?, DateTime, IQueryable<PropertyComplianceRating>>> CategoryComplianceRatings =
    (p, categoryId, checkTime) => p.ComplianceRatings.AsQueryable()
    .Where(cr =>
        cr.ComplianceCategory != null &&
        (
            categoryId == null ||
            (categoryId != null && cr.ComplianceCategory.Id == categoryId)
        )
    )
    .GroupBy(cr => cr.ComplianceCategory)
    .Select(g => g
    .Where(cr => cr.Date < checkTime)
    .OrderByDescending(cr => cr.Date)
    .FirstOrDefault()
);

It's not in a loop or anything. The above code sample causes a huge processing spike and takes over 30s to complete. However if I extract the SQL it's self it runs in <1s. — Ben Ford, Dec 23 '14 at 13:53
In your connection string, do you have something like "MultipleActiveResultSets = true"? That is required if using Lazy Loading but also has the negative effect of slowing down queries. If you do have that set as true, try setting it to false to see if that changes the behavior of this part of the code. — Solomon Rutzky, Jan 13 '15 at 15:07
I am using MARS at the moment. I'll try turning it off and see if it has any positive effect. — Ben Ford, Jan 14 '15 at 12:35

faby · Answer 1 · 2014-12-23T22:50:42.823

0

Maybe your query is tacking all references, try in this way with AsNoTracking

database.Table.AsNoTracking().Where(e=>other code)

update

If it doesn't work try moving the 'AsQueryable()' after the Where part and before the GroupBy part.

edited Dec 23 '14 at 22:50

answered Dec 23 '14 at 13:20

faby

7,394
3
27
44

I've tried adding AsNoTracking to the `properties` IQueryable with no change. – Ben Ford Dec 23 '14 at 13:57
@benford I've updated my answer. Let me know if something changes – faby Dec 23 '14 at 22:23
Hi faby. I've tried many different locations for the 'AsQueryable()' none of which seem to make any difference. – Ben Ford Jan 13 '15 at 14:14

score 0 · Answer 2 · answered Dec 23 '14 at 13:55

Check the session settings (arithabort, ansi_nulls...) when you execute from the different environments. Make sure they are all the same. Many times, different settings will result in the same procedure executing with completely different execution durations.

Entity Framework query timeout but SQL is instant

2 Answers2