CODEWITHSUNDEEP
c#asp.netentity-framework
Johan
Johan

Reputation: 35223

EF query performance issues

If i only pass createdFromDate to the lambda below, my query finished in about 300ms. But if i pass the id parameter, I end up in about 20-30 seconds. Is there any way to improve the query when passing the id variable? 

List<MYENTITY> list= ctx.MYENTITY.Where(s => 
      (string.IsNullOrEmpty(createdFrom) || s.CREATE_DATE >= createdFrom) && 
      (string.IsNullOrEmpty(id) || s.ANOTHER_ENTITY.FirstOrDefault().ID == id)
      .ToList();

Upvotes: 1

Views: 86

Answers (3)

BLoB
BLoB

Reputation: 9725

Can you not get the evaluated ID of:

s.ANOTHER_ENTITY.FirstOrDefault().ID

i.e. int? anotherEntityId = s.ANOTHER_ENTITY.FirstOrDefault().ID;

And use that in place of the above line of code.

As I think including it in the where clause will in effect quite possibly (depending on the ef query used to obtain this entity) cause the final sql query to be one hell of a mess.

At least I think it's worth testing.

That is you could substitute the anotherEntityId to some known value and test, if it is slow then it is the evaluation of 

s.ANOTHER_ENTITY.FirstOrDefault().ID

that is the real problem.

Upvotes: 1

paul
paul

Reputation: 22021

One simple thing to try:

List<MYENTITY> list = ctx.MYENTITY.Where(s => 
  string.IsNullOrEmpty(createdFrom) || s.CREATE_DATE >= createdFrom)
  .ToList()
  .Where(s2 => string.IsNullOrEmpty(id) || s2.ANOTHER_ENTITY.FirstOrDefault().ID == id)
  .ToList();

This is not a solution, but if it speeds things up then I'd suggest looking at your code, if the speed stays the same, I'd suggest looking at your database structure.

Upvotes: 1

Randy Minder
Randy Minder

Reputation: 48522

I suspect this isn't the fault of EF. What I would do is to capture the query being generated by EF, paste it into SSMS and look at the query plan. My guess is that a table or index scan is being done, because an index is missing. Or, an index exists but is not being used, possibly because one or more columns should be included in the index.

Upvotes: 1

Related Questions