.NET .ToList function is WAY WAY too slow

Question

We're having alot of troubles here with the .ToList command, it's used in VB.NET with a MVC ASP.NET web project.

We have ~2000 entries in our database, we use a LINQ command to SELECT and ORDER the 2000 entries. The result is transformed into a list by the .ToList method for our pager and grid builder. Problem is, the .ToList takes WAY WAY TOO long (we're talking 40-60seconds to execute) so our websites looks slow as hell.

We tested the equivalent SQL command on the database and it responds quickly. It's not a problem with the commands or a slow database server. We tried an IEnumrable witch was alot faster but we need it in the .ToList format at the end for our grids. What's the deal with the .ToList ? Anything we can do ?

Here's the code :

 'list = (From c In _entities.XXXXXXXXSet.Include("XXXXXX").Include("XXXXXX") _
                Where Not (c.XXXXXX Is Nothing AndAlso c.XXXXXX = String.Empty) _
                And c.XXXXXX = codeClient _
                And c.XXXXXX > dateLimite _
                Order By c.XXXXXX Descending _
                Select c).ToList()

We divided the code and to leave only the .ToList function alone and that's really what sucks up all the time. The LINQ command executes in no time.

Thanks alot. Tom

Sounds like its the paging/grid work _after_ the .ToList() call that's causing the problem. — Joel Coehoorn, Apr 15 '11 at 17:55
The LINQ command doesn't "execute" immediately - the expression doesn't get executed until the .ToList causes it to be enumerated... — Cade Roux, Apr 15 '11 at 17:57
@Joel: Nope, I stepped into the function, and the .ToList line takes literally 30-40 secs to executes. — Tommy Dubé-Leblanc, Apr 15 '11 at 18:02
When you say "equivalent SQL command" did you actually get the SQL SELECT produced by the LINQ query, or just what you think it is? Oftentimes, they are not nearly the same thing. — Jim Bolla, Apr 15 '11 at 18:10
The query doesn't execute until you do something to enumerate it. (in this case, calling ToList() enumerates it, and that's what makes the query execute). You can watch that behavior if you split out hte call to ToList, and hook up SQL Profiler -- you will see that the query does not actually go to SQL Server until you execute the ToList() command (you could also eliminate the ToList() call, and run a foreach over list -- you will get the same effect, where the query doesn't really execute until you iterate it). — JMarsch, Apr 15 '11 at 19:33

score 15 · Answer 1 · answered Apr 15 '11 at 17:55

Of course the LINQ command "executes" in no time, because it just represents the query. The query is only executed once you iterate over it, which is exactly what the ToList method does.

I would advise you to use the Skip and Take operators in your pagers to narrow down the result queried from the database. Doing this, you only request the 10 or 20 elements or whatever you need, resulting in a much smoother experience.

score 8 · Accepted Answer · answered Apr 15 '11 at 17:54

8

I think it would be better to page in the query instead of fetching all data in one go, using Skip and Take.

list = (From c In _entities.XXXXXXXXSet.Include("XXXXXX").Include("XXXXXX") _
            Where Not (c.XXXXXX Is Nothing AndAlso c.XXXXXX = String.Empty) _
            And c.XXXXXX = codeClient _
            And c.XXXXXX > dateLimite _
            Order By c.XXXXXX Descending _
            Select c).Skip(pageSize * pageIndex).Take(pageSize).ToList();

That, paired with some well targeted caching (if possible) should provide a snappier user experience.

answered Apr 15 '11 at 17:54

Fredrik Mörk

155,851
29
291
343

2000 rows really isn't enough to force paging. I routinely pull out 2000 rows in 0.05 seconds. – Amy B Apr 16 '11 at 12:35
@David: that will really depend a lot on the query in question. Regardless of that, I think that smacking 2000 rows in the face of the user is always a bad idea. Given that, I think it's better to not fetch all 2000 rows from the DB each time, just to filter out the few to actually use. – Fredrik Mörk Apr 16 '11 at 12:42
We coded with this method and we went from 30-40secs to 1-3 seconds! Thanks alot – Tommy Dubé-Leblanc Apr 18 '11 at 18:08

score 2 · Answer 3 · answered Apr 15 '11 at 18:04

2

When you say "the equivalent SQL command on the database and it responds quickly" - is that the actual SQL statements which the LINQ code is generating or handcoded SQL which is logically equivalent?

Because that LINQ-generated code might not be terribly efficient.

answered Apr 15 '11 at 18:04

Cade Roux

88,164
40
182
265

No, I tried with the generic "human" SQL command. I'll translate the LINQ into SQL, thanks for the tip. – Tommy Dubé-Leblanc Apr 15 '11 at 18:12
2

@Tommy Leblanc - have a look at the Log: http://msdn.microsoft.com/en-us/library/bb386961.aspx – Cade Roux Apr 15 '11 at 18:15

score 1 · Answer 4 · answered Apr 15 '11 at 17:55

For stuff like this, it's often useful to run the code in the profiler. There could be any number of things slowing down... network, memory, object size, etc.

You could also create your own list and copy the IEnumerable values into it. If it's at all possible, I would recommend changing your grid to accept an IEnumerable.

score 1 · Answer 5 · answered Apr 16 '11 at 12:34

To confirm the performance of ToList as opposed to query execution, add a statement and compare:

  //this call iterates a query, causing a database roundtrip.
List<Row> result = query.ToList();
  //this call generates a new List by iterating the old List.
result = result.ToList();

Looking over your query, I suspect you'll need an codeClient, and an index on each of the tables mentioned in the calls to .Include. Grab the generated sql and check the execution plan to confirm.

.NET .ToList function is WAY WAY too slow

5 Answers5