We are having an issue in linq query. In select query multiple tables are associated with joins. Where record filters were applied on join section.

This query was running fine and giving proper result within time since past 8 to 9 months. Suddenly it took time around 2 minute to retrieve the record with the same join and consuming 99% CPU.

This query is being used always while user login so its sure that it was running fine in past till issue moment. Can any one help why this issue occurred suddenly?

var entityList = (from x in db.TableA
    join y in db.TableB on x.Id equals y.TableC.TableAId into g
    from d in g.DefaultIfEmpty()
    where d.EndDateTime == null &&
          (d == null || d.TableE.PersonID == personId) &&
          x.EndDateTime == null &&
          d.TableC.EndDateTime == null &&
          x.TableE.PersonID == personId
    select new
    {
        Col1 = d != null ? d.TableC.TableD.Id : 0,
        Col2 = d != null ? d.TableC.Id : 0,
    }).AsQueryable();

1 Answers

0
Rick James On

If that framework turns d == null into d = null instead of d IS NULL, that is a serious flaw.

OR can hurt performance. Do you really need the NULL option for d? What indexes are there?