EF LINQ ToList is very slow

Question

I am using ASP NET MVC 4.5 and EF6, code first migrations.

I have this code, which takes about 6 seconds.

var filtered = _repository.Requests.Where(r => some conditions); // this is fast, conditions match only 8 items
var list = filtered.ToList(); // this takes 6 seconds, has 8 items inside

I thought that this is because of relations, it must build them inside memory, but that is not the case, because even when I return 0 fields, it is still as slow.

var filtered = _repository.Requests.Where(r => some conditions).Select(e => new {}); // this is fast, conditions match only 8 items
var list = filtered.ToList(); // this takes still around 5-6 seconds, has 8 items inside

Now the Requests table is quite complex, lots of relations and has ~16k items. On the other hand, the filtered list should only contain proxies to 8 items.

Why is ToList() method so slow? I actually think the problem is not in ToList() method, but probably EF issue, or bad design problem.

Anyone has had experience with anything like this?

EDIT:

These are the conditions:

_repository.Requests.Where(r => ids.Any(a => a == r.Student.Id) && r.StartDate <= cycle.EndDate && r.EndDate >= cycle.StartDate)

So basically, I can checking if Student id is in my id list and checking if dates match.

are you using any navigational property in `some condition` because it results in join on the sql side? — Hamid Pourjam, Nov 04 '14 at 11:00
In addtition to @HamidP, are your navigational properties marked as `virtual`? Else they will be joined immediately instead of lazy loading (correct me if I'm wrong..) — Marthijn, Nov 04 '14 at 13:18

score 6 · Answer 1 · answered Nov 04 '14 at 10:56

6

Your filtered variable contains a query which is a question, and it doesn't contain the answer. If you request the answer by calling .ToList(), that is when the query is executed. And that is the reason why it is slow, because only when you call .ToList() is the query executed by your database.

It is called Deferred execution. A google might give you some more information about it.

If you show some of your conditions, we might be able to say why it is slow.

answered Nov 04 '14 at 10:56

Maarten

22,527
3
47
68

I added the condition in comment. – Jaanus Nov 04 '14 at 11:53
Can you check if it makes a difference if you change `ids.Any(a => a == r.Student.Id)` to `ids.Contains(r.Student.Id)` ? It still means that you need the extra table. Is `Student.Id` a key or index? – Maarten Nov 04 '14 at 12:05
Nope, it actually was contains before, changed to Any because I thought it would make it faster. Student Id is integer, primary key – Jaanus Nov 04 '14 at 12:29

Hamid Pourjam · Accepted Answer · 2014-11-04T12:13:09.127

3

In addition to Maarten's answer I think the problem is about two different situation

some condition is complex and results in complex and heavy joins or query in your database
some condition is filtering on a column which does not have an index and this cause the full table scan and make your query slow.

I suggest start monitoring the query generated by Entity Framework, it's very simple, you just need to set Log function of your context and see the results,

using (var context = new MyContext())
{
    context.Database.Log = Console.Write;

    // Your code here...
}

if you see something strange in generated query try to make it better by breaking it in parts, some times Entity Framework generated queries are not so good.

if the query is okay then the problem lies in your database (assuming no network problem).

run your query with an SQL profiler and check what's wrong.

UPDATE

I suggest you to:

add index for StartDate and EndDate Column in your table (one for each, not one for both)

edited Nov 04 '14 at 12:13

answered Nov 04 '14 at 11:04

Hamid Pourjam

20,441
9
58
74

I added the condition in comment. – Jaanus Nov 04 '14 at 11:54
what is ids? list of integers? what is the size of it? – Hamid Pourjam Nov 04 '14 at 11:57
it can vary from 1 to 100 or so, but it is slow even with 1 id in it. it is integer list on student Ids. – Jaanus Nov 04 '14 at 12:00
how you obtain ids? from database or from user? can you give us the code where you get ids? – Hamid Pourjam Nov 04 '14 at 12:11
ListOfStudents.ToList().Select(i => i.Id); – Jaanus Nov 04 '14 at 12:18
and from where do you get `ListOfStudents`? – Hamid Pourjam Nov 04 '14 at 12:22
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/64236/discussion-between-jaanus-and-hamidp). – Jaanus Nov 04 '14 at 12:26
_repository.Requests.ToList().Where(e =>LOGIC).Select(v => v.Student); – Jaanus Nov 04 '14 at 12:27

score 2 · Answer 3 · answered Nov 04 '14 at 10:51

2

ToList executes the query against DB, while first line is not.

Can you show some conditions code here? To increase the performance you need to optimize query/create indexes on the DB tables.

answered Nov 04 '14 at 10:51

Max Brodin

3,903
1
14
23

I added the condition in comment. – Jaanus Nov 04 '14 at 11:53

score 1 · Answer 4 · edited Nov 04 '14 at 12:31

1

Your first line of code only returns an IQueryable. This is a representation of a query that you want to run not the result of the query. The query itself is only runs on the databse when you call .ToList() on your IQueryable, because its the first point that you have actually asked for data.

Your adjustment to add the .Select only adds to the existing IQueryable query definition. It doesnt change what conditions have to execute. You have essentially changed the following, where you get back 8 records:

select * from Requests where [some conditions];

to something like:

select '' from Requests where [some conditions];

You will still have to perform the full query with the conditions giving you 8 records, but for each one, you only asked for an empty string, so you get back 8 empty strings.

The long and the short of this is that any performance problem you are having is coming from your "some conditions". Without seeing them, its is difficult to know. But I have seen people in the past add .Where clauses inside a loop, before calling .ToList() and inadvertently creating a massively complicated query.

edited Nov 04 '14 at 12:31

Hamid Pourjam

20,441
9
58
74

answered Nov 04 '14 at 11:33

Keith

718
8
12

I added the condition in comment. – Jaanus Nov 04 '14 at 11:58
What type is ids? if it is another IQueriable or a List? If it is an IQueryable, you are going to be reevaluating it for every record of the Requests table – Keith Nov 04 '14 at 13:35
You could also try running the date comparison first, as it wil be fast, then filter is by ID: _repository.Requests.Where(r => r.StartDate <= cycle.EndDate && r.EndDate >= cycle.StartDate).where(r => ids.Contains(r.Student.Id)); – Keith Nov 04 '14 at 13:36
I thought it was a list, but it seems out to be iqueriable. I will try your trick, I think its good idea. – Jaanus Nov 04 '14 at 13:56
Yeah, do .ToList() on your ids before you use it in your linq expression – Keith Nov 04 '14 at 14:00
It seems that now the Ids toList() take 6 seconds and my current query is fast. – Jaanus Nov 04 '14 at 14:03
Ok. what are the conditions you used to get your list of Ids then? Is that based on yet another IQueriable? Essentially, you need to make sure you are never evaluating an IQueriable inside another expression, or inside a loop. – Keith Nov 04 '14 at 14:10

score 0 · Answer 5 · answered Nov 04 '14 at 10:58

Jaanus. The most likely reason of this issue is complecity of generated SQL query by entity framework. I guess that your filter condition contains some check of other tables.

Try to check generated query by "SQL Server Profiler". And then copy this query to "Management Studio" and check "Estimated execution plan". As a rule "Management Studio" generatd index recomendation for your query try to follow these recomendations.

EF LINQ ToList is very slow

5 Answers5

Linked