.NET .ToList function is WAY WAY too slow

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

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.

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.

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.

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.

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.