EF LINQ ToList非常慢 [英] EF LINQ ToList is very slow

问题描述

我正在使用 ASP NET MVC 4.5和 EF6 ，代码首先迁移。

我有这个代码，大约需要6秒钟。

  var filtered = _repository.Requests。其中（r =>某些条件）; //这很快，条件只匹配8个项目
 var list = filtered.ToList（）; //这需要6秒，在
中有8个项目 

我以为这是因为关系，它必须在内存中构建它们，但事实并非如此，因为即使我返回0个字段，它仍然很慢。

  var filtered = _repository.Requests.Where（r =>某些条件）.Select（e => new {}）; //这很快，条件只匹配8个项目
 var list = filtered.ToList（）; //这需要大约5-6秒，在
内有8个项目 

现在，请求表是相当的复杂，关系很大，有〜16k的物品。另一方面，过滤后的列表只能包含8个代码。

为什么 ToList（）方法太慢了？我实际上认为问题不在ToList（）方法中，但是可能是EF问题，或是设计问题。

任何人都有这样的经历？ >

编辑：

这些条件是：

  _repository.Requests.Where（r => ids.Any（a => a == r.Student.Id）&& r.StartDate< ; = cycle.EndDate&& r.EndDate> = cycle.StartDate）
  

所以基本上，我可以检查学生 id在我的id列表中，并检查日期是否匹配。

解决方案

除了马鞍山的答案，我认为问题是两种不同的情况。

1. 一些条件是复杂的，导致数据库中复杂和沉重的连接或查询




2. 某些条件正在对没有索引的列进行过滤，这会导致全表扫描并使y我们的查询缓慢。

我建议开始监视Entity Framework生成的查询，这很简单，只需要设置您的上下文的日志函数，并查看结果

  using var context = new MyContext（））
 {
 context.Database.Log = Console.Write; 
 
 //您的代码在这里... 
} 
  

如果您在生成的查询中看到一些奇怪的东西，尝试通过部分破解来实现更好，有时候Entity Framework生成的查询不是那么好。

如果查询可以那么问题在于您的数据库（假设没有网络问题）。

使用SQL分析器运行查询，并检查是否出错。

更新

我建议您：

1. 添加 StartDate 和 EndDate 列表中的列（每个列为一个，而不是一个两者）

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.

解决方案

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

1. some condition is complex and results in complex and heavy joins or query in your database

2. 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:

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

这篇关于EF LINQ ToList非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！