Entity Framework causing Timeout Error

Question

I am working with the following Entity Framework query. I know there's a lot going on here but am hoping it's clear enough that someone might be able to spot the issue.

var lineItems = from li in Repository.Query<CostingLineItem>()

                let cid = (li.ParentCostingPackage != null) ?
                    li.ParentCostingPackage.ParentCostingEvent.ProposalSection.Proposal.Costing.Id :
                    li.ParentCostingEvent.ProposalSection.Proposal.Costing.Id

                where cid == costingId &&
                    li.OriginalProductId.HasValue &&
                    (li.Quantity.HasValue && li.Quantity.Value > 0) &&  // li.QuantityUnitMultiplier
                    Classifications.Contains(li.OriginalProduct.ClassificationEnumIndex)

                let selectedChoiceId = li.OriginalPackageOptionId.HasValue ?
                    (from c in li.OriginalPackageOption.CostingLineItems
                        orderby (c.IsIncluded ?? false) ? -2 : (c.IsDefaultItem ?? false) ? -1 : c.Id
                        select (int)c.OriginalPackageOptionChoiceId).FirstOrDefault() :
                    0

                where selectedChoiceId == 0 || (li.OriginalPackageOptionChoiceId.HasValue && li.OriginalPackageOptionId.Value == selectedChoiceId)

                let hasProviderAvailable = li.OriginalProductItem.ProductItemVendors.Any(
                    piv => piv.ProductPricings.Any(pp => pp.ProductItemVendor.CompanyId != null || pp.ProductItemVendor.HotelId != null))

                select new
                {
                    LineItem = li,
                    ProductItem = li.OriginalProductItem,
                    Product = li.OriginalProduct,
                    Vendors = li.CostingLineItemVendors,
                    HasProviderAvailable = hasProviderAvailable
                };

As is, this query generates the following run-time error:

The wait operation timed out

If I change the section that declares selectedChoiceId to the following, the error goes away:

let selectedChoiceId = 0

Can anyone see how that code is consistently causing a time-out error?

(Note: This code is part of a large application that has been running for several years. So I really don't think this has anything to do with the connection string or anything like that. If I make the change above, it works consistently.)

It looks rather sql-server related than EF problem. Others recommend updating statistics http://serverfault.com/questions/419997/the-wait-operation-timed-out-when-running-sql-server-in-hyper-v. Also could be blocking problem - you can try to read uncommitted data http://stackoverflow.com/a/24699606/2224701. — Vojtěch Dohnal, Apr 03 '15 at 19:34
@VojtěchDohnal: I assumed that, ultimately, the error was occurring within SQL Server. The question is really what it is about the lines in question that would cause this issue in SQL Server. I highly doubt this is a blocking problem. Blocking problems general depend on other code and would not be as consistent as the code above. — Jonathan Wood, Apr 03 '15 at 20:40
On first look I suspect the `selectedChoiceId` declaration results in a more complicated subquery being sent from EF to SQL Server, so naturally removing it executes faster (hence no time out). I would attach profiler, capture the generated SQL, and investigate its estimated execution plan to see which indexes it hits and how. — Nick Patsaris, Apr 04 '15 at 01:35
try moving `var choice = (from c in li.OriginalPackageOption.CostingLineItems orderby (c.IsIncluded ?? false) ? -2 : (c.IsDefaultItem ?? false) ? -1 : c.Id select (int)c.OriginalPackageOptionChoiceId).FirstOrDefault() : 0` and then use `let selectedChoiceId = choice` I think there should be a problem with the subquery. — Nilesh, Apr 04 '15 at 12:31
@Nilesh: Move it to where, and why would that make any difference? — Jonathan Wood, Apr 05 '15 at 15:39

score 0 · Answer 1 · answered Apr 17 '15 at 06:52

I think this will give you a better performance but not sure if it'll fix the problem :

let selectedChoiceId = li.OriginalPackageOptionId.HasValue
    ? (from c in li.OriginalPackageOption.CostingLineItems
        let cOrder = (c.IsIncluded ?? false) ? -2 : (c.IsDefaultItem ?? false) ? -1 : c.Id
        orderby cOrder
        select (int) c.OriginalPackageOptionChoiceId).FirstOrDefault()
    : 0

score 0 · Accepted Answer · answered Apr 17 '15 at 10:12

The query can be simplified in a number of ways, which should make it easier to optimize by the database engine.

Firstly, you can remove a number of null checks (HasValue), because they're not relevant in SQL, but they do bloat the generated SQL.

Secondly, I think this check involving selectedChoiceId can be greatly simplified. This is what I think the statement could look like:

from li in Repository.Query<CostingLineItem>()

let cid = (li.ParentCostingPackage != null) ?
    li.ParentCostingPackage.ParentCostingEvent.ProposalSection.Proposal.Costing.Id :
    li.ParentCostingEvent.ProposalSection.Proposal.Costing.Id

where cid == costingId &&
    li.OriginalProductId.HasValue &&
    li.Quantity > 0 &&  // no null check
    Classifications.Contains(li.OriginalProduct.ClassificationEnumIndex)

let selectedChoiceId = (from c in li.OriginalPackageOption.CostingLineItems
        orderby c.IsIncluded ? -2 : c.IsDefaultItem ? -1 : c.Id // no null checks
        select (int)c.OriginalPackageOptionChoiceId).FirstOrDefault()

where !li.OriginalPackageOptionId.HasValue || li.OriginalPackageOptionId == selectedChoiceId

let hasProviderAvailable = li.OriginalProductItem.ProductItemVendors.Any(
    piv => piv.ProductPricings.Any(pp => pp.ProductItemVendor.CompanyId != null || pp.ProductItemVendor.HotelId != null))

select new
{
    LineItem = li,
    ProductItem = li.OriginalProductItem,
    Product = li.OriginalProduct,
    Vendors = li.CostingLineItemVendors,
    HasProviderAvailable = hasProviderAvailable
}

For the rest, of course there are the usual suspects. Better indexes may become more important as the database volume increases. Checking for (and fixing) database fragmentation can also have a significant impact.

Entity Framework causing Timeout Error

2 Answers2