为什么使用Contains()运算符会显著降低Entity Framework的性能？

Question

更新3: 根据这篇公告，EF团队在EF6 alpha 2中已经解决了这个问题。

更新2: 我已经建议解决此问题。要投票，请点击这里。

考虑一个只有一个非常简单的表的SQL数据库。

CREATE TABLE Main (Id INT PRIMARY KEY)

我填充了这个表格，共有10,000条记录。

WITH Numbers AS
(
  SELECT 1 AS Id
  UNION ALL
  SELECT Id + 1 AS Id FROM Numbers WHERE Id <= 10000
)
INSERT Main (Id)
SELECT Id FROM Numbers
OPTION (MAXRECURSION 0)

我为这张表构建了一个 EF 模型，并在 LINQPad 中以以下方式运行查询（我正在使用“C# 语句”模式，以便 LINQPad 不会自动创建转储）。

var rows = 
  Main
  .ToArray();

执行时间约为0.07秒。现在我添加了Contains操作符并重新运行查询。

var ids = Main.Select(a => a.Id).ToArray();
var rows = 
  Main
  .Where (a => ids.Contains(a.Id))
  .ToArray();

此案例的执行时间为20.14秒（慢了288倍）！

起初，我怀疑查询生成的T-SQL需要更长的执行时间，因此我尝试将其从LINQPad的SQL窗格剪切并粘贴到SQL Server Management Studio中。

SET NOCOUNT ON
SET STATISTICS TIME ON
SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Primary] AS [Extent1]
WHERE [Extent1].[Id] IN (1,2,3,4,5,6,7,8,...

而结果是

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 88 ms.

接下来我怀疑是LINQPad造成了问题，但无论是在LINQPad中运行还是在控制台应用程序中运行，性能都是一样的。

因此，问题似乎出现在Entity Framework的某个地方。

我这里做错了什么吗？这是我的代码的关键部分，所以是否有什么我可以做来加速性能？

我正在使用Entity Framework 4.1和Sql Server 2008 R2。

更新1：

在下面的讨论中有一些关于EF是在构建初始查询时还是在解析接收到的数据时发生延迟的问题。为了测试这个问题，我运行了以下代码，

var ids = Main.Select(a => a.Id).ToArray();
var rows = 
  (ObjectQuery<MainRow>)
  Main
  .Where (a => ids.Contains(a.Id));
var sql = rows.ToTraceString();

这会强制 EF 生成查询而不会将其执行到数据库中。结果是这段代码需要大约20秒才能运行，因此看起来几乎所有时间都用在了构建初始查询上。

那么编译查询有救吗？不要那么快...... 编译查询要求传递给查询的参数为基本类型（int、string、float 等）。它不接受数组或 IEnumerable，所以我不能将其用于 ID 列表。

- Mike

---

你尝试过使用 var qry = Main.Where(a => ids.Contains(a.Id)); var rows = qry.ToArray(); 来查看查询的哪个部分需要时间吗？ - Andrew Cooper

不是 EF 降低了您的查询效率，而是您尝试运行的实际查询；您能解释一下您想要做什么吗？也许有更好的方法来满足您的需求。 - Kris Ivanov

@Mike，这1,000个ID是随机序列还是遵循某种一致性，例如 10 <= ids <= 500？ - Kris Ivanov

更新一下：EF6 alpha 2中包含了一个改进，加速了Enumerable.Contains的翻译。请参阅此处的公告：http://blogs.msdn.com/b/adonet/archive/2012/12/10/ef6-alpha-2-available-on-nuget.aspx。我的测试表明，对于包含100,000个int元素的列表进行list.Contains(x)翻译现在只需要不到一秒钟，并且时间随列表元素数量近似线性增长。感谢您的反馈并帮助我们改进EF！ - divega

请注意...带有任何IEnumerable参数的查询无法被缓存，当您的查询计划变得复杂时，这可能会导致相当严重的副作用。如果您必须多次运行操作（例如使用Contains获取数据块），则可能会出现一些非常恶劣的查询重新编译时间！查看源代码，您可以看到所有包含IEnumerable<T>参数的查询都会发生parent._recompileRequired =（）=> true;。糟糕！ - jocull

Answer 1

更新：在EF6中添加InExpression后，处理Enumerable.Contains的性能显著提高。因此，本答案中描述的方法已不再必要。

你说得对，大部分时间都用于处理查询的翻译。EF的提供程序模型目前不包括表示IN子句的表达式，因此ADO.NET提供程序无法原生支持IN。相反，Enumerable.Contains的实现将其转换为OR表达式树，即在C#中看起来像这样的内容：

new []{1, 2, 3, 4}.Contains(i)

...我们将生成一个DbExpression树，可以表示为：

((1 = @i) OR (2 = @i)) OR ((3 = @i) OR (4 = @i))

表达式树必须平衡，因为如果我们将所有OR连接在单个长脊柱上，表达式访问者更有可能遇到堆栈溢出（是的，在我们的测试中确实遇到了这种情况）。

之后，我们将这样的树发送给ADO.NET提供程序，该提供程序可以识别此模式并在SQL生成期间将其减少为IN子句。

当我们在EF4中添加对Enumerable.Contains的支持时，我们认为无需引入提供程序模型中的IN表达式即可实现，而且老实说，1万比我们预期的客户传递给Enumerable.Contains的元素数量要多得多。也就是说，我知道这很烦人，并且表达式树的操作使得在您特定的场景中成本过高。

我与我们的一位开发人员讨论了这个问题，我们认为在未来我们可以通过添加IN的一流支持来改变实现方式。我会确保将其添加到我们的待办事项列表中，但我不能保证它何时会完成，因为我们还有许多其他改进需要进行。

除了线程中已经建议的解决方法外，我还要添加以下内容：

考虑创建一个方法，该方法平衡了数据库往返次数和您传递给Contains的元素数量。例如，在我的测试中，我观察到计算并针对SQL Server的本地实例执行具有100个元素的查询需要1/60秒。如果您可以以这样的方式编写查询，即执行100个具有100个不同ID集的查询将为您提供与具有10,000个元素的查询相同的结果，则您可以在大约1.67秒内获得结果，而不是18秒。

不同的块大小应该根据查询和数据库连接的延迟效果更好。对于某些查询，例如如果传递的序列有重复项或者在嵌套条件中使用Enumerable.Contains，则可能会在结果中获得重复的元素。

以下是代码片段（如果用于将输入切片的代码看起来有点复杂，请见谅。有更简单的方法可以实现相同的事情，但我试图想出一种保留流式传输序列的模式，并且我在LINQ中找不到任何类似的东西，因此我可能过度做了那部分 :)）：

var list = context.GetMainItems(ids).ToList();

上下文或存储库的方法：

public partial class ContainsTestEntities
{
    public IEnumerable<Main> GetMainItems(IEnumerable<int> ids, int chunkSize = 100)
    {
        foreach (var chunk in ids.Chunk(chunkSize))
        {
            var q = this.MainItems.Where(a => chunk.Contains(a.Id));
            foreach (var item in q)
            {
                yield return item;
            }
        }
    }
}

用于对可枚举序列进行切片的扩展方法：

public static class EnumerableSlicing
{

    private class Status
    {
        public bool EndOfSequence;
    }

    private static IEnumerable<T> TakeOnEnumerator<T>(IEnumerator<T> enumerator, int count, 
        Status status)
    {
        while (--count > 0 && (enumerator.MoveNext() || !(status.EndOfSequence = true)))
        {
            yield return enumerator.Current;
        }
    }

    public static IEnumerable<IEnumerable<T>> Chunk<T>(this IEnumerable<T> items, int chunkSize)
    {
        if (chunkSize < 1)
        {
            throw new ArgumentException("Chunks should not be smaller than 1 element");
        }
        var status = new Status { EndOfSequence = false };
        using (var enumerator = items.GetEnumerator())
        {
            while (!status.EndOfSequence)
            {
                yield return TakeOnEnumerator(enumerator, chunkSize, status);
            }
        }
    }
}

希望这能帮助到您！

- divega

---

解释 TakeOnEnumerator<T> 方法中的 !(status.EndOfSequence = true)：这个表达式赋值的副作用始终为 !true，因此不会影响整个表达式。它实际上只有在还有剩余项需要获取，但已经枚举到末尾时将 stats.EndOfSequence 标记为 true。 - arviman

或许在 EF 6 中，处理 Enumerable.Contains 的性能相较于之前的 EF 版本有了显著提升。但是不幸的是，在我们的使用场景中仍然远远达不到令人满意/生产就绪的水平。 - Nik

@Nik 如果你正在使用EF Core，你可以尝试这个：https://dev59.com/tl8e5IYBdhLWcg3w6d4_#70587979 - yv989c

Answer 2

如果你发现了一个阻塞你的性能问题，不要试图花费很长时间去解决它，因为你很可能不会成功，并且你必须直接与微软沟通（如果你有高级支持），这需要花费很长时间。

在处理性能问题时，请使用解决方法和变通方法。EF意味着直接SQL，这没有什么不好的。全局理解“使用EF = 不再使用SQL”是错误的。你有SQL Server 2008 R2，所以：

• 创建存储过程接受表值参数来传递你的ID
• 让你的存储过程返回多个结果集以最佳方式模拟“Include”逻辑
• 如果你需要一些复杂的查询构建，在存储过程内使用动态SQL
• 使用SqlDataReader获取结果并构造你的实体
• 将它们附加到上下文中，并像从EF加载的实体一样使用它们

如果性能对你至关重要，那么你将找不到更好的解决方案。当前版本既不支持表值参数也不支持多个结果集，因此无法将此过程映射和执行到EF中。

- Ladislav Mrnka

---

@Laddislav Mrnka，我们遇到了类似的性能问题，原因是list.Contains()。我们将尝试通过传递ids来创建存储过程。如果我们通过EF运行此存储过程，是否会遇到任何性能问题？ - Kurubaran

Answer 3

我们能够通过添加一个中间表，并从需要使用Contains子句的LINQ查询连接到该表，解决EF Contains问题。我们通过这种方法获得了惊人的结果。由于在预编译EF查询时不允许使用“Contains”，因此我们的大型EF模型对使用“Contains”子句的查询性能非常差。

• Create a table in SQL Server - for example HelperForContainsOfIntType with HelperID of Guid data-type and ReferenceID of int data-type columns. Create different tables with ReferenceID of differing data-types as needed.

• Create an Entity / EntitySet for HelperForContainsOfIntType and other such tables in EF model. Create different Entity / EntitySet for different data-types as needed.

• Create a helper method in .NET code which takes the input of an IEnumerable<int> and returns an Guid. This method generates a new Guid and inserts the values from IEnumerable<int> into HelperForContainsOfIntType along with the generated Guid. Next, the method returns this newly generated Guid to the caller. For fast inserting into HelperForContainsOfIntType table, create a stored-procedure which takes input of an list of values and does the insertion. See Table-Valued Parameters in SQL Server 2008 (ADO.NET). Create different helpers for different data-types or create a generic helper method to handle different data-types.

• Create a EF compiled query which is similar to something like below:

  static Func<MyEntities, Guid, IEnumerable<Customer>> _selectCustomers =
      CompiledQuery.Compile(
          (MyEntities db, Guid containsHelperID) =>
              from cust in db.Customers
              join x in db.HelperForContainsOfIntType on cust.CustomerID equals x.ReferenceID where x.HelperID == containsHelperID
              select cust 
      );
  

• Call the helper method with values to be used in the Contains clause and get the Guid to use in the query. For example:

  var containsHelperID = dbHelper.InsertIntoHelperForContainsOfIntType(new int[] { 1, 2, 3 });
  var result = _selectCustomers(_dbContext, containsHelperID).ToList();
  

- Dhwanil Shah

---

谢谢！我使用了你的解决方案的变种来解决我的问题。 - Mike

Answer 4

编辑我的原始回答-有一个可能的解决方法，取决于你的实体的复杂性。如果你知道EF生成的SQL来填充你的实体，你可以使用DbContext.Database.SqlQuery直接执行它。在EF 4中，我认为你可以使用ObjectContext.ExecuteStoreQuery，但我没有尝试过。

例如，使用我的原始答案中的代码使用StringBuilder来生成SQL语句，我能够做到以下几点。

var rows = db.Database.SqlQuery<Main>(sql).ToArray();

并且总时间从大约26秒降至0.5秒。 我会第一个说这很丑陋，希望有更好的解决方案出现。

更新

经过更多的思考，我意识到如果使用连接来过滤您的结果，则EF不必构建那个长列表的id。这可能很复杂，具体取决于并发查询的数量，但我认为您可以使用用户ID或会话ID来隔离它们。

为了测试这一点，我创建了一个与Main具有相同模式的Target表。然后我使用StringBuilder创建INSERT命令以批量插入1000个记录到Target表中，因为这是SQL Server在单个INSERT中接受的最大数量。直接执行sql语句比通过EF快得多（大约0.3秒对2.5秒），而且我认为这样做应该是可以的，因为表结构不应该改变。

最后，使用JOIN选择结果导致查询更简单，并且在不到0.5秒的时间内执行。

ExecuteStoreCommand("DELETE Target");

var ids = Main.Select(a => a.Id).ToArray();
var sb = new StringBuilder();

for (int i = 0; i < 10; i++)
{
    sb.Append("INSERT INTO Target(Id) VALUES (");
    for (int j = 1; j <= 1000; j++)
    {
        if (j > 1)
        {
            sb.Append(",(");
        }
        sb.Append(i * 1000 + j);
        sb.Append(")");
    }
    ExecuteStoreCommand(sb.ToString());
    sb.Clear();
}

var rows = (from m in Main
            join t in Target on m.Id equals t.Id
            select m).ToArray();

rows.Length.Dump();

并且EF生成的用于连接的SQL：

SELECT 
[Extent1].[Id] AS [Id]
FROM  [dbo].[Main] AS [Extent1]
INNER JOIN [dbo].[Target] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]

---

(translated answer)

这不是一个答案，但我想分享一些额外的信息，它太长而无法在评论中放下。我能够复制你的结果，并且有一些其他的东西要补充：

SQL Profiler显示延迟在第一个查询（Main.Select）和第二个Main.Where查询之间，因此我怀疑问题在于生成并发送这么大的查询（48,980字节）。

然而，在T-SQL中动态构建相同的SQL语句只需要不到1秒钟的时间，并且从你的Main.Select语句中取出ids，构建相同的SQL语句并使用SqlCommand执行它只需要0.112秒的时间，这还包括将内容写入控制台的时间。

目前为止，我怀疑EF正在为构建查询时的每个10,000个ids进行一些分析/处理。很抱歉我不能提供明确的答案和解决方案 :(。

这是我在SSMS和LINQPad中尝试的代码（请不要太苛刻地批评，我很匆忙，试图离开工作）：

declare @sql nvarchar(max)

set @sql = 'SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Main] AS [Extent1]
WHERE [Extent1].[Id] IN ('

declare @count int = 0
while @count < 10000
begin
    if @count > 0 set @sql = @sql + ','
    set @count = @count + 1
    set @sql = @sql + cast(@count as nvarchar)
end
set @sql = @sql + ')'

exec(@sql)

---

var ids = Mains.Select(a => a.Id).ToArray();

var sb = new StringBuilder();
sb.Append("SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Main] AS [Extent1] WHERE [Extent1].[Id] IN (");
for(int i = 0; i < ids.Length; i++)
{
    if (i > 0) 
        sb.Append(",");     
    sb.Append(ids[i].ToString());
}
sb.Append(")");

using (SqlConnection connection = new SqlConnection("server = localhost;database = Test;integrated security = true"))
using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = sb.ToString();
    connection.Open();
    using(SqlDataReader reader = command.ExecuteReader())
    {
        while(reader.Read())
        {
            Console.WriteLine(reader.GetInt32(0));
        }
    }
}

- Jeff Ogata

---

感谢您在此方面的工作。知道您能够重现它让我感觉好多了——至少我不是疯了！不幸的是，您的解决方法在我的情况下并没有真正帮助，因为正如您所猜测的那样，我在这里给出的示例尽可能地简化以隔离问题。我的实际查询涉及相当复杂的模式，几个其他表上的.Include()，以及一些其他的LINQ运算符。 - Mike

@Mike，我又提出了一个适用于复杂实体的想法。如果你别无选择，希望它不会太难实现。 - Jeff Ogata

我进行了一些测试，我认为你是正确的，延迟是在执行SQL之前创建它。我已经更新了我的问题并添加了详细信息。 - Mike

@Mike，你能尝试加入这些id吗（请参见我的答案中的更新）？ - Jeff Ogata

我最终采用了你的方法变体来解决性能问题。结果看起来相当丑陋，但直到微软解决这个问题（如果他们解决），这可能是最佳选择。 - Mike

Answer 5

我是一名有用的助手，可以为您翻译文本。

我不熟悉实体框架，但如果您采取以下措施，性能是否会更好呢？

不要使用这种方法：

var ids = Main.Select(a => a.Id).ToArray();
var rows = Main.Where (a => ids.Contains(a.Id)).ToArray();

这样怎么样（假设ID是一个整数）：

var ids = new HashSet<int>(Main.Select(a => a.Id));
var rows = Main.Where (a => ids.Contains(a.Id)).ToArray();

- Shiv

---

我不知道为什么和如何，但它运行得很好 :) - Wahid Bitar

性能更好的原因在于第一个调用中的int[].Contains调用是O(n) - 可能是完整的数组扫描 - 而HashSet<int>.Contains调用是O(1)。请参见https://dev59.com/w2kw5IYBdhLWcg3wkLMX以获取哈希集性能信息。 - Shiv

@Shiv，我不认为那是正确的。EF会将任何集合都转换成SQL。集合的类型应该不是问题。 - Rob

@Rob 我持怀疑态度 - 如果是这样的话，我无法解释性能差异。可能需要分析二进制文件以查看它所做的事情。 - Shiv

HashSet不是IEnumerable。在LINQ中调用.Contains的IEnumerables表现很差（至少在EF6之前）。 - Jason Beck

@Rob 是正确的，我认为。new []{1, 2, 3, 4}.Contains(column)将简单地被转换为SQL中的... WHERE column IN (1, 2, 3, 4)，所以使用HashSet在性能上不会有任何区别。只有当 Main 是一个 IEnumerable 时，这种优化才值得实现。 - RoadRunner

Answer 6

它在Entity Framework 6 Alpha 2中得到了修复：http://entityframework.codeplex.com/SourceControl/changeset/a7b70f69e551 更多信息：http://blogs.msdn.com/b/adonet/archive/2012/12/10/ef6-alpha-2-available-on-nuget.aspx

- Felipe Pessoto

Answer 7

问题出在Entity Framework的SQL生成上。如果其中一个参数是列表，它无法缓存查询。

为了让EF缓存你的查询，你可以将列表转换为字符串，并在字符串上执行.Contains操作。

例如，以下代码将运行得更快，因为EF可以缓存查询：

var ids = Main.Select(a => a.Id).ToArray();
var idsString = "|" + String.Join("|", ids) + "|";
var rows = Main
.Where (a => idsString.Contains("|" + a.Id + "|"))
.ToArray();

当生成这个查询时，通常会使用Like而不是In，因此它会加快你的C#程序，但可能会使你的SQL变慢。在我的情况下，我没有注意到SQL执行性能的下降，而C#运行得更快了。

- user2704238

---

好主意，但这不会利用所讨论的列上的任何索引。 - spender

是的，这是真的，这就是为什么我提到它可能会减慢SQL执行速度。我猜这只是一个潜在的替代方案，如果你不能使用谓词生成器并且你正在处理足够小的数据集，以便你可以承受不使用索引的情况。我还想说的是，谓词生成器是首选选项。 - user2704238

这是一个惊人的解决方案。我们成功将生产查询运行时间从约12,600毫秒降至仅18毫秒。这是巨大的改进。非常感谢！ - Jacob

@spender 这个解决方案将尊重您的索引：https://dev59.com/tl8e5IYBdhLWcg3w6d4_#70587979 - yv989c

Answer 8

一种可缓存的Contains替代方案？

我刚遇到了这个问题，所以我在Entity Framework Feature Suggestions链接中添加了我的建议。

问题肯定出在生成SQL时。我有一个客户端的数据，查询生成需要4秒钟，但执行只需要0.1秒钟。

我注意到，当使用动态LINQ和ORs时，SQL生成时间同样长，但生成的内容可以缓存。因此，再次执行时，时间缩短至0.2秒。

请注意，仍然会生成一个SQL。

如果您能承受最初的开销，数组计数不会有太大变化，并且经常运行查询，则可以考虑其他事项。（在LINQ Pad中测试）

- Dave

---

还可以在 CodePlex 网站上为其投票 http://entityframework.codeplex.com/workitem/245。 - Dave