为什么Contains（）运算符大大降低了实体框架的性能？ [英] Why does the Contains() operator degrade Entity Framework's performance so dramatically?

问题描述

更新3：根据这个公告，这是由EF团队在EF6 alpha 2中解决的。

UPDATE 3: According to this announcement, this has been addressed by the EF team in EF6 alpha 2.

更新2：我创建了一个建议解决这个问题。要投票支持，去这里。

UPDATE 2: I've created a suggestion to fix this problem. To vote for it, go here.

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

Consider a SQL database with one very simple table.

CREATE TABLE Main (Id INT PRIMARY KEY)

我填充10,000个记录的表。

I populate the table with 10,000 records.

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不会自动创建转储）。

I build an EF model for the table and run the following query in LINQPad (I am using "C# Statements" mode so LINQPad doesn't create a dump automatically).

var rows = 
  Main
  .ToArray();

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

Execution time is ~0.07 seconds. Now I add the Contains operator and re-run the query.

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

这种情况的执行时间是 20.14秒（288倍慢）！

Execution time for this case is 20.14 seconds (288 times slower)!

起初我怀疑为查询发出的T-SQL需要更长的时间才能执行，所以我尝试将其从LINQPad的SQL窗格切割并粘贴到SQL Server Management Studio中

At first I suspected that the T-SQL emitted for the query was taking longer to execute, so I tried cutting and pasting it from LINQPad's SQL pane into 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还是在控制台应用程序中运行。

Next I suspected LINQPad was causing the problem, but performance is the same whether I run it in LINQPad or in a console application.

所以，似乎这个问题在实体框架内。

So, it appears that the problem is somewhere within Entity Framework.

我在这里做错了这是一个时间暴击我的代码的一部分，所以我可以做些什么来加快性能？

Am I doing something wrong here? This is a time-critical part of my code, so is there something I can do to speed up performance?

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

I am using Entity Framework 4.1 and Sql Server 2008 R2.

更新1：

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

In the discussion below there were some questions about whether the delay occurred while EF was building the initial query or while it was parsing the data it received back. To test this I ran the following code,

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个秒来运行，所以似乎几乎所有的时间都是用来构建初始查询。

which forces EF to generate the query without executing it against the database. The result was that this code required ~20 secords to run, so it appears that almost all of the time is taken in building the initial query.

？不是那么快... CompiledQuery要求将传入查询的参数作为基本类型（int，string，float等）。它不会接受数组或IEnumerable，所以我不能使用它的ID列表。

CompiledQuery to the rescue then? Not so fast ... CompiledQuery requires the parameters passed into the query to be fundamental types (int, string, float, and so on). It won't accept arrays or IEnumerable, so I can't use it for a list of Ids.

推荐答案

更新：EF6包括Enumerable.Contains的显着性能改进。

您是对的，大部分时间都用于处理查询的翻译。 EF的提供者模型当前不包含表示IN子句的表达式，因此ADO.NET提供程序无法原生支持。相反，Enumerable.Contains的实现将它转换为OR表达式的树，即C＃中的某些东西看起来像这样：

You are right that most of the time is spent processing the translation of the query. EF's provider model doesn't currently include an expression that represents an IN clause, therefore ADO.NET providers can't support IN natively. Instead, the implementation of Enumerable.Contains translates it to a tree of OR expressions, i.e. for something that in C# looks like like this:

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

我们将生成一个可以这样表示的DbExpression树：

... we will generate a DbExpression tree that could be represented like this:

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

表达式的树必须平衡，因为如果我们在单个长的脊柱上有所有的OR，那么表达式访问者将会有更多的机会碰到堆栈溢出（是的，我们实际上在我们的测试中打了））

(The expression trees have to be balanced because if we had all the ORs over a single long spine there would be more chances that the expression visitor would hit a stack overflow (yes, we actually did hit that in our testing))

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

We later send a tree like this to the ADO.NET provider, which can have the ability to recognize this pattern and reduce it to the IN clause during SQL generation.

当我们在EF4中添加了Enumerable.Contains的支持时，我们认为这样做不需要在提供者模型中引入对IN表达式的支持，而且老实说， 10,000远远超过我们预期客户将传递给Enumerable.Contains的元素数量。也就是说，我理解这是一个烦恼，表达式的操纵使得事情在你的特定场景中太贵了。

When we added support for Enumerable.Contains in EF4, we thought it was desirable to do it without having to introduce support for IN expressions in the provider model, and honestly, 10,000 is much more than the number of elements we anticipated customers would pass to Enumerable.Contains. That said, I understand that this is an annoyance and that the manipulation of expressions trees makes things too expensive in your particular scenario.

我和我们的开发者和我们相信，未来我们可以通过为IN添加一流的支持来改变实现。我会确保这被添加到我们的积压中，但是我不能承诺什么时候会让它有很多其他改进，我们想要做的。

I discussed this with one of our developers and we believe that in the future we could change the implementation by adding first-class support for IN. I will make sure this is added to our backlog, but I cannot promise when it will make it given there are many other improvements we would like to make.

对于已经在线程中建议的解决方法，我将添加以下内容：

To the workarounds already suggested in the thread I would add the following:

考虑创建一个平衡的方法数据库往返数目与您传递给Contains的元素数量。例如，在我自己的测试中，我观察到，对于SQL Server的本地实例的计算和执行，具有100个元素的查询占1/60秒。如果您可以使用100个不同的ids执行100个查询的方式编写查询，则会为10,000个元素的查询提供相等的结果，那么可以在大约1.67秒而不是18秒内获取结果。

Consider creating a method that balances the number of database roundtrips with the number of elements you pass to Contains. For instance, in my own testing I observed that computing and executing against a local instance of SQL Server the query with 100 elements takes 1/60 of a second. If you can write your query in such a way that executing 100 queries with 100 different sets of ids would give you equivalent result to the query with 10,000 elements, then you can get the results in aproximately 1.67 seconds instead of 18 seconds.

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

Different chunk sizes should work better depending on the query and the latency of the database connection. For certain queries, i.e. if the sequence passed has duplicates or if Enumerable.Contains is used in a nested condition you may obtain duplicate elements in the results.

这是一个代码片段（对不起，如果用于将输入切成块的代码看起来有点太复杂，有更简单的方法来实现同样的事情，但是试图想出一个保留序列流的模式，我在LINQ中找不到任何东西，所以我可能超过了这一点:)）：

Here is a code snippet (sorry if the code used to slice the input into chunks looks a little too complex. There are simpler ways to achieve the same thing, but I was trying to come up with a pattern that preserves streaming for the sequence and I couldn't find anything like it in LINQ, so I probably overdid that part :) ):

用法：

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

上下文或存储库的方法：

Method for context or repository:

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

希望这有帮助！

这篇关于为什么Contains（）运算符大大降低了实体框架的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！