为什么是。包含缓慢? 最有效的方法获得多个实体的主键？

通过主键选择多个实体的最有效方法是什么？

public IEnumerable<Models.Image> GetImagesById(IEnumerable<int> ids)
{


//return ids.Select(id => Images.Find(id));       //is this cool?
return Images.Where( im => ids.Contains(im.Id));  //is this better, worse or the same?
//is there a (better) third way?


}

我意识到我可以做一些性能测试来进行比较，但是我想知道是否有比这两种方法更好的方法，并且我正在寻找一些启示，看看这两种查询之间的区别是什么，如果有的话，一旦它们被“翻译”了。

49033 次浏览
小开

第二种选择肯定比第一种好。第一个选项将导致对数据库的 ids.Length查询，而第二个选项可以在 SQL 查询中使用 'IN'运算符。它基本上会将您的 LINQ 查询转换成如下 SQL:

SELECT *
FROM ImagesTable
WHERE id IN (value1,value2,...)

其中 value1、 value2等等是 id 变量的值。但是，请注意，我认为可以用这种方式序列化到查询中的值的数量可能有一个上限。我看看能不能找到一些文件。

小开
最佳答案

更新: 在 EF6中添加 InExpression 后，处理枚举的性能。容量大幅提升。这个答案中的分析很棒，但自2013年以来基本上已经过时。

在实体框架中使用 Contains实际上非常慢。的确，它在 SQL 中转换为 IN子句，并且 SQL 查询本身执行得很快。但是问题和性能瓶颈在于将 LINQ 查询转换为 SQL。将要创建的表达式树被扩展为一个长链的 OR连接，因为没有表示 IN的本机表达式。在创建 SQL 时，许多 OR的这个表达式被识别并折叠回 SQLIN子句中。

这并不意味着使用 Contains比在 ids集合中为每个元素发出一个查询(第一个选项)更糟糕。它可能仍然更好-至少对于不太大的收藏。但对于大型收藏品来说，这真的很糟糕。我记得不久前测试过一个包含大约12.000个元素的 Contains查询，这个查询可以正常工作，但是即使 SQL 中的查询在不到一秒钟的时间内就执行完毕，也要花费大约一分钟的时间。

对于每个往返，使用 Contains表达式中的较少数量的元素来测试多个往返数据库的组合的性能可能是值得的。

这种方法以及将 Contains与实体框架结合使用的局限性在这里显示和解释:

为什么包含()操作符降低实体框架的性能如此显着？

在这种情况下，原始 SQL 命令可能表现得最好，这意味着调用 dbContext.Database.SqlQuery<Image>(sqlString)或 dbContext.Images.SqlQuery(sqlString)，其中 sqlString是@Rune 的答案中显示的 SQL。

剪辑

以下是一些测量方法:

我在一个有550000条记录和11列(ID 从1开始，没有间隔)的表上完成了这项工作，并随机挑选了20000个 id:

using (var context = new MyDbContext())
{
Random rand = new Random();
var ids = new List<int>();
for (int i = 0; i < 20000; i++)
ids.Add(rand.Next(550000));


Stopwatch watch = new Stopwatch();
watch.Start();


// here are the code snippets from below


watch.Stop();
var msec = watch.ElapsedMilliseconds;
}

测试1

var result = context.Set<MyEntity>()
.Where(e => ids.Contains(e.ID))
.ToList();

结果-> 毫秒 = 85.5秒

测试2

var result = context.Set<MyEntity>().AsNoTracking()
.Where(e => ids.Contains(e.ID))
.ToList();

结果-> 毫秒 = 84.5秒

AsNoTracking的这种微小作用是非常不寻常的。它表明瓶颈不是对象物化(也不是如下所示的 SQL)。

对于这两个测试，可以在 SQLProfiler 中看到，SQL 查询到达数据库的时间非常晚。(我没有精确测量，但已经过了70秒。)很明显，将这个 LINQ 查询转换成 SQL 是非常昂贵的。

测试3

var values = new StringBuilder();
values.AppendFormat("{0}", ids[0]);
for (int i = 1; i < ids.Count; i++)
values.AppendFormat(", {0}", ids[i]);


var sql = string.Format(
"SELECT * FROM [MyDb].[dbo].[MyEntities] WHERE [ID] IN ({0})",
values);


var result = context.Set<MyEntity>().SqlQuery(sql).ToList();

结果-> 毫秒 = 5.1秒

测试4

// same as Test 3 but this time including AsNoTracking
var result = context.Set<MyEntity>().SqlQuery(sql).AsNoTracking().ToList();

结果-> 毫秒 = 3.8秒

这一次，禁用跟踪的效果更加明显。

测试5

// same as Test 3 but this time using Database.SqlQuery
var result = context.Database.SqlQuery<MyEntity>(sql).ToList();

结果-> 毫秒 = 3.7秒

我的理解是 context.Database.SqlQuery<MyEntity>(sql)与 context.Set<MyEntity>().SqlQuery(sql).AsNoTracking()相同，所以测试4和测试5之间没有什么不同。

(由于随机标识选择后可能出现重复，结果集的长度并不总是相同的，但总是在19600到19640个元素之间。)

编辑2

测试6

即使20000次往返数据库也比使用 Contains快:

var result = new List<MyEntity>();
foreach (var id in ids)
result.Add(context.Set<MyEntity>().SingleOrDefault(e => e.ID == id));

结果-> 毫秒 = 73.6秒

注意，我使用的是 SingleOrDefault而不是 Find。对 Find使用相同的代码非常慢(我在几分钟后取消了测试) ，因为 Find在内部调用 DetectChanges。禁用自动变化检测(context.Configuration.AutoDetectChangesEnabled = false)可以获得与 SingleOrDefault大致相同的性能。使用 AsNoTracking可以减少一到两秒的时间。

测试是在同一台机器上用数据库客户机(控制台应用程序)和数据库服务器完成的。对于“远程”数据库，由于往返次数较多，最后的结果可能会严重恶化。

小开

最近我遇到了一个类似的问题，我发现最好的方法是将列表插入到临时表中，然后进行连接。

private List<Foo> GetFoos(IEnumerable<long> ids)
{
var sb = new StringBuilder();
sb.Append("DECLARE @Temp TABLE (Id bigint PRIMARY KEY)\n");


foreach (var id in ids)
{
sb.Append("INSERT INTO @Temp VALUES ('");
sb.Append(id);
sb.Append("')\n");
}


sb.Append("SELECT f.* FROM [dbo].[Foo] f inner join @Temp t on f.Id = t.Id");


return this.context.Database.SqlQuery<Foo>(sb.ToString()).ToList();
}

这不是一个漂亮的方式，但是对于大型列表来说，它是非常性能化的。

小开

使用 toArray ()将 List 转换为 Array 可以提高性能:

ids.Select(id => Images.Find(id));
return Images.toArray().Where( im => ids.Contains(im.Id));

提交答案