为什么.contains很慢?通过主键获取多个实体的最有效方法
本文关键字:实体 方法 有效 获取 很慢 contains 为什么 | 更新日期: 2023-09-27 18:14:13
通过主键选择多个实体的最有效方法是什么?
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?
}
我意识到我可以做一些性能测试来比较,但我想知道是否有比两者更好的方法,并且我正在寻找一些关于这两个查询之间的差异的启示,如果有的话,一旦它们被"翻译"。
更新:随着EF6中InExpression的增加,处理Enumerable的性能。包含显著改善。这个答案中的分析很好,但自2013年以来基本上已经过时了。
在实体框架中使用Contains
实际上非常慢。确实,它在SQL中转换为IN
子句,并且SQL查询本身执行得很快。但是问题和性能瓶颈是在从LINQ查询到SQL的转换中。将要创建的表达式树被扩展成OR
连接的长链,因为没有表示IN
的原生表达式。当创建SQL时,许多OR
s的表达式被识别并折叠回SQL IN
子句。
这并不意味着使用Contains
比在ids
集合(您的第一个选项)中对每个元素发出一个查询更糟糕。它可能仍然更好——至少对于不太大的集合来说。但对于大型收藏品来说,这真的很糟糕。我记得我前一段时间测试了一个Contains
查询,大约有12000个元素,它工作了,但花了大约一分钟,尽管SQL中的查询在不到一秒钟的时间内执行。
测试多次往返数据库的组合的性能可能是值得的,每次往返使用较少数量的Contains
表达式中的元素。
这种方法以及使用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();
Result -> msec = 85.5 sec
测试2var result = context.Set<MyEntity>().AsNoTracking()
.Where(e => ids.Contains(e.ID))
.ToList();
Result -> msec = 84.5 sec
AsNoTracking
的这种微小影响是非常不寻常的。它表明瓶颈不是对象物化(也不是SQL,如下所示)。
对于这两个测试,在SQL Profiler中可以看到SQL查询到达数据库的时间很晚。(我没有精确测量,但时间超过了70秒。)显然,将这个LINQ查询转换为SQL是非常昂贵的。
测试3var 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();
Result -> msec = 5.1 sec
测试4// same as Test 3 but this time including AsNoTracking
var result = context.Set<MyEntity>().SqlQuery(sql).AsNoTracking().ToList();
Result -> msec = 3.8 sec
此时禁用跟踪的效果更加明显。
测试5// same as Test 3 but this time using Database.SqlQuery
var result = context.Database.SqlQuery<MyEntity>(sql).ToList();
Result -> msec = 3.7 sec
我的理解是context.Database.SqlQuery<MyEntity>(sql)
和context.Set<MyEntity>().SqlQuery(sql).AsNoTracking()
是一样的,所以Test 4和Test 5之间没有期望的差异。
(由于随机id选择后可能存在重复,结果集的长度并不总是相同的,但它总是在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));
Result -> msec = 73.6 sec
注意我使用的是SingleOrDefault
而不是Find
。对Find
使用相同的代码非常慢(我在几分钟后取消了测试),因为Find
在内部调用DetectChanges
。禁用自动更改检测(context.Configuration.AutoDetectChangesEnabled = false
)会导致与SingleOrDefault
大致相同的性能。使用AsNoTracking
可以减少一到两秒的时间。
在同一台机器上使用数据库客户端(控制台应用程序)和数据库服务器进行测试。对于"远程"数据库,由于多次往返,最后的结果可能会变得更糟。
第二个选项肯定比第一个好。第一个选项将导致对数据库的ids.Length
查询,而第二个选项可以在SQL查询中使用'IN'
操作符。它将把你的LINQ查询转换成如下SQL:
SELECT *
FROM ImagesTable
WHERE id IN (value1,value2,...)
其中value1, value2等是ids变量的值。但是,请注意,我认为以这种方式序列化到查询中的值的数量可能有上限。
嗯,最近我有一个类似的问题,我发现最好的方法是插入列表在一个临时表,然后进行连接。
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转换为数组可以提高性能。你可以这样做:
ids.Select(id => Images.Find(id));
return Images.toArray().Where( im => ids.Contains(im.Id));