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 the
Includeagain and so..By having more than two
Include` affect the performance.