I have following query within my project and it is consuming lot of time to execute. I am trying to optimize it, but not able to successfully do it. Any suggestions would be highly appreciated.
_context.MainTable .Include(mt => mt.ChildTable1) .Include(mt => mt.ChildTable1.ChildTable2) .Include(mt => mt.ChildTable3) .Include(mt => mt.ChildTable3.ChildTable4) .SingleOrDefault( mt => mt.ChildTable3.ChildTable4.Id == id && mt.ChildTable1.Operation == operation && mt.ChildTable1.Method = method && mt.StatusId == statusId);
Include() gets translates to join and you are using too many joins in the code. You can optimize indexes with the help of DB engine execution plan.
I suggest you not to use all
Include in one go. instead, you break the query and apply
Include one by one. I meant you apply
Include, get the result and then apply theInclude
again and so..By having more than twoInclude` affect the performance.