为什么.Contains 慢？通过主键获取多个实体的最有效方法？

Question

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

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?

}

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

Answer 1

更新：通过在 EF6 中添加 InExpression，处理 Enumerable.Contains 的性能显着提高。这个答案中的分析很棒，但自 2013 年以来基本上已过时。

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

这并不意味着使用 Contains 比为 ids 集合中的每个元素发出一个查询（您的第一个选项）更糟糕。它可能仍然更好 - 至少对于不太大的集合来说。但对于大型收藏来说，这确实很糟糕。我记得不久前我测试过一个包含大约 12.000 个元素的 Contains 查询，该查询有效，但需要大约一分钟的时间，尽管 SQL 中的查询执行时间不到一秒。

可能值得测试多个往返数据库的组合的性能，并且每个往返的 Contains 表达式中的元素数量较少。

此处显示并解释了这种方法以及将 Contains 与实体框架一起使用的限制：

为什么 Contains() 运算符会降低实体框架的性能戏剧性地？

在这种情况下，原始 SQL 命令可能会表现最佳，这意味着您调用 dbContext.Database.SqlQuery(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，如下所示）。

对于这两个测试，可以在 SQL Profiler 中看到 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();

结果 -> msec = 3.7 sec

我的理解是 context.Database.SqlQuery(sql) 与 context.Set().SqlQuery 相同(sql).AsNoTracking()，因此测试 4 和测试 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));

结果-> msec = 73.6 sec

请注意，我使用了 SingleOrDefault 而不是 Find。使用与 Find 相同的代码非常慢（几分钟后我取消了测试），因为 Find 在内部调用 DetectChanges。禁用自动更改检测 (context.Configuration.AutoDetectChangesEnabled = false) 会导致与 SingleOrDefault 大致相同的性能。使用 AsNoTracking 可以将时间缩短一到两秒。

测试是在同一台计算机上使用数据库客户端（控制台应用程序）和数据库服务器完成的。由于多次往返，“远程”数据库的最后结果可能会变得更糟。

Answer 2

第二种选择肯定比第一种更好。第一个选项将导致对数据库进行 ids.Length 查询，而第二个选项可以在 SQL 查询中使用 'IN' 运算符。它基本上会将您的 LINQ 查询转换为类似于以下 SQL 的内容：

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

其中 value1、value2 等是 ids 变量的值。但请注意，我认为可以通过这种方式序列化到查询中的值的数量可能存在上限。我看看能不能找到一些文档...

Answer 3

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

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();
}

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

Answer 4

使用 toArray() 将列表转换为数组可以提高性能。你可以这样做：

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