Por que o operador Contains () prejudica o desempenho do Entity Framework tão drasticamente?

ATUALIZAÇÃO 3: De acordo com este anúncio , isso foi tratado pela equipe da EF no EF6 alpha 2.

ATUALIZAÇÃO 2: criei uma sugestão para corrigir esse problema. Para votar, vá aqui .

Considere um database SQL com uma tabela muito simples.

CREATE TABLE Main (Id INT PRIMARY KEY) 

Eu preencho a tabela com 10.000 registros.

 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) 

Eu construo um modelo EF para a tabela e execute a seguinte consulta no LINQPad (estou usando o modo “C # Statements” para que o LINQPad não crie um dump automaticamente).

 var rows = Main .ToArray(); 

O tempo de execução é de 0,07 segundos. Agora eu adiciono o operador Contains e executo novamente a consulta.

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

O tempo de execução para este caso é de 20,14 segundos (288 vezes mais lento)!

No começo eu suspeitava que o T-SQL emitido para a consulta demorava mais tempo para ser executado, então tentei cortar e colar do painel SQL do LINQPad no 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,... 

E o resultado foi

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

Em seguida, suspeitei que o LINQPad estava causando o problema, mas o desempenho é o mesmo, quer eu o execute no LINQPad ou em um aplicativo de console.

Portanto, parece que o problema está em algum lugar dentro do Entity Framework.

Estou fazendo algo errado aqui? Esta é uma parte crítica do tempo do meu código, então há algo que eu possa fazer para acelerar o desempenho?

Estou usando o Entity Framework 4.1 e o Sql Server 2008 R2.

ATUALIZAÇÃO 1:

Na discussão abaixo, houve algumas perguntas sobre se o atraso ocorreu enquanto a EF estava construindo a consulta inicial ou enquanto estava analisando os dados recebidos de volta. Para testar isso eu corri o seguinte código,

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

o que força a EF a gerar a consulta sem executá-la no database. O resultado foi que este código requeria ~ 20 secords para rodar, então parece que quase todo o tempo é levado na construção da consulta inicial.

CompiledQuery para o resgate então? Não tão rápido … CompiledQuery requer que os parâmetros passados ​​para a consulta sejam tipos fundamentais (int, string, float e assim por diante). Ele não aceita arrays nem IEnumerable, então não posso usá-lo para uma lista de IDs.

ATUALIZAÇÃO: Com a adição do InExpression no EF6, o desempenho do processamento Enumerable.Contains melhorou drasticamente. A abordagem descrita nesta resposta não é mais necessária.

Você está certo de que a maior parte do tempo é gasto no processamento da tradução da consulta. O modelo de provedor da EF atualmente não inclui uma expressão que represente uma cláusula IN, portanto, os provedores do ADO.NET não podem suportar IN nativamente. Em vez disso, a implementação de Enumerable.Contains traduz para uma tree de expressões OR, ou seja, para algo que em C # se parece com isso:

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

… vamos gerar uma tree DbExpression que poderia ser representada assim:

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

(As trees de expressão precisam ser balanceadas porque, se tivéssemos todas as SOs em uma única espinha longa, haveria mais chances de que a expressão visitante atingisse um estouro de pilha (sim, nós realmente atingimos isso em nossos testes))

Mais tarde, enviamos uma tree como essa para o provedor ADO.NET, que pode ter a capacidade de reconhecer esse padrão e reduzi-lo à cláusula IN durante a geração de SQL.

Quando adicionamos suporte para Enumerable.Contains no EF4, achamos que era desejável fazê-lo sem ter que introduzir suporte para expressões IN no modelo de provedor, e honestamente, 10.000 é muito mais do que o número de elementos que antecipamos que os clientes passariam para Enumerable.Contains. Dito isso, entendo que isso é um aborrecimento e que a manipulação de trees de expressões torna as coisas muito caras em seu cenário específico.

Eu discuti isso com um de nossos desenvolvedores e acreditamos que no futuro poderemos mudar a implementação adicionando suporte de primeira class para o IN. Eu me certificarei de que isso seja adicionado ao nosso backlog, mas não posso prometer quando será feito, pois há muitas outras melhorias que gostaríamos de fazer.

Para as soluções alternativas já sugeridas no segmento, adicionaria o seguinte:

Considere criar um método que equilibre o número de viagens de database com o número de elementos que você passa para Contém. Por exemplo, em meus próprios testes, observei que computar e executar em uma instância local do SQL Server a consulta com 100 elementos leva 1/60 de segundo. Se você puder escrever sua consulta de forma que executar 100 consultas com 100 conjuntos diferentes de ids lhe daria resultado equivalente à consulta com 10.000 elementos, você poderá obter os resultados em aproximadamente 1.67 segundos em vez de 18 segundos.

Diferentes tamanhos de partes devem funcionar melhor, dependendo da consulta e da latência da conexão com o database. Para determinadas consultas, ou seja, se a sequência transmitida tiver duplicatas ou se Enumerable.Contains for usada em uma condição aninhada, você poderá obter elementos duplicados nos resultados.

Aqui está um trecho de código (desculpe se o código usado para dividir a input em pedaços parece um pouco complexo. Existem maneiras mais simples de conseguir a mesma coisa, mas eu estava tentando criar um padrão que preservasse o streaming para a sequência e Eu não consegui encontrar nada parecido no LINQ, então eu provavelmente exagerei nessa parte :)):

Uso:

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

Método para contexto ou repository:

 public partial class ContainsTestEntities { public IEnumerable
GetMainItems(IEnumerable 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; } } } }

Métodos de extensão para fatiar seqüências enumeráveis:

 public static class EnumerableSlicing { private class Status { public bool EndOfSequence; } private static IEnumerable TakeOnEnumerator(IEnumerator enumerator, int count, Status status) { while (--count > 0 && (enumerator.MoveNext() || !(status.EndOfSequence = true))) { yield return enumerator.Current; } } public static IEnumerable> Chunk(this IEnumerable 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); } } } } 

Espero que isto ajude!

Se você encontrar um problema de desempenho que esteja bloqueando para você, não tente gastar muito tempo resolvendo-o porque provavelmente não terá sucesso e terá que comunicá-lo diretamente com o MS (se tiver suporte premium) e será necessário idades.

Use solução alternativa e solução alternativa em caso de problema de desempenho e EF significa SQL direto. Não há nada de mal nisso. A ideia global de que usar EF = não usar mais o SQL é uma mentira. Você tem o SQL Server 2008 R2 para:

  • Criar procedimento armazenado aceitando o parâmetro com valor de tabela para passar seus IDs
  • Deixe seu procedimento armazenado retornar vários conjuntos de resultados para emular Include lógica da maneira ideal
  • Se você precisar de alguma construção de consulta complexa, use o SQL dynamic dentro do procedimento armazenado
  • Use o SqlDataReader para obter resultados e construir suas entidades
  • Anexe-os ao contexto e trabalhe com eles como se fossem carregados da EF

Se o desempenho for crítico para você, você não encontrará uma solução melhor. Este procedimento não pode ser mapeado e executado pelo EF porque a versão atual não suporta parâmetros com valor de tabela ou múltiplos conjuntos de resultados.

Conseguimos resolver o problema do EF Contains adicionando uma tabela intermediária e ingressando nessa tabela a partir da consulta LINQ que precisava usar a cláusula Contains. Conseguimos obter resultados surpreendentes com essa abordagem. Temos um modelo EF grande e como “Contains” não é permitido ao pré-compilar as consultas EF, estávamos obtendo um desempenho muito ruim para as consultas que usam a cláusula “Contains”.

Uma visão geral:

  • Crie uma tabela no SQL Server – por exemplo, HelperForContainsOfIntType com HelperID de tipo de dados Guid e ReferenceID de colunas de tipo de dados int . Crie tabelas diferentes com ReferenceID de diferentes tipos de dados, conforme necessário.

  • Crie um Entity / EntitySet para HelperForContainsOfIntType e outras tabelas no modelo EF. Crie diferentes Entity / EntitySet para diferentes tipos de dados, conforme necessário.

  • Crie um método auxiliar no código .NET que receba a input de um IEnumerable e retorne um Guid . Esse método gera um novo Guid e insere os valores de IEnumerable em HelperForContainsOfIntType junto com o Guid gerado. Em seguida, o método retorna este Guid recém-gerado ao chamador. Para inserir rapidamente na tabela HelperForContainsOfIntType , crie um procedimento armazenado que HelperForContainsOfIntType input de uma lista de valores e faça a inserção. Consulte parameters com valor de tabela no SQL Server 2008 (ADO.NET) . Crie diferentes auxiliares para diferentes tipos de dados ou crie um método auxiliar genérico para manipular diferentes tipos de dados.

  • Crie uma consulta EF compilada que seja semelhante a algo como abaixo:

     static Func> _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 ); 
  • Chame o método auxiliar com valores a serem usados ​​na cláusula Contains e obtenha o Guid para usar na consulta. Por exemplo:

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

Editando minha resposta original – Existe uma solução possível, dependendo da complexidade de suas entidades. Se você conhece o sql que o EF gera para preencher suas entidades, você pode executá-lo diretamente usando DbContext.Database.SqlQuery . Na EF 4, eu acho que você poderia usar ObjectContext.ExecuteStoreQuery , mas eu não tentei.

Por exemplo, usando o código da minha resposta original abaixo para gerar a instrução sql usando um StringBuilder , consegui fazer o seguinte

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

e o tempo total passou de aproximadamente 26 segundos para 0,5 segundos.

Eu serei o primeiro a dizer que é feio, e espero que uma solução melhor se apresente.

atualizar

Depois de pensar um pouco mais, percebi que, se você usa uma junit para filtrar seus resultados, a EF não precisa criar essa longa lista de IDs. Isso pode ser complexo, dependendo do número de consultas simultâneas, mas acredito que você poderia usar ids de usuário ou ids de session para isolá-los.

Para testar isso, criei uma tabela de Target com o mesmo esquema de Main . Em seguida, usei um StringBuilder para criar comandos INSERT para preencher a tabela de Target em lotes de 1.000, já que é o máximo que o SQL Server aceitará em um único INSERT . A execução direta das instruções sql foi muito mais rápida do que passar pela EF (aproximadamente 0.3 segundos vs. 2.5 segundos), e acredito que seria ok já que o esquema da tabela não deveria mudar.

Finalmente, selecionar usando uma join resultou em uma consulta muito mais simples e executada em menos de 0,5 segundos.

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

E o sql gerado pela EF para a junit:

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

(resposta original)

Isso não é uma resposta, mas eu queria compartilhar algumas informações adicionais e é muito longo para caber em um comentário. Consegui reproduzir seus resultados e tenho algumas outras coisas para adicionar:

O SQL Profiler mostra que o atraso é entre a execução da primeira consulta ( Main.Select ) e a segunda consulta Main.Where , portanto, suspeitei que o problema estava na geração e no envio de uma consulta desse tamanho (48.980 bytes).

No entanto, construir a mesma instrução sql em T-SQL dinamicamente leva menos de 1 segundo, e pegar os ids da instrução Main.Select , construir a mesma instrução sql e executá-la usando um SqlCommand levou 0,112 segundos, e isso inclui tempo para gravar o conteúdo para o console.

Neste ponto, suspeito que a EF está fazendo alguma análise / processamento para cada um dos 10.000 ids enquanto cria a consulta. Gostaria de poder fornecer uma resposta definitiva e solução :(.

Aqui está o código que eu tentei no SSMS e no LINQPad (por favor, não critique muito asperamente, estou com pressa de tentar sair do trabalho):

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

Eu não estou familiarizado com o Entity Framework, mas é melhor o desempenho se você faz o seguinte?

Em vez disso:

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

que tal isso (supondo que o ID é um int):

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

Uma alternativa armazenável para Contém?

Isso só me enganou, então adicionei meus dois centavos ao link Sugestões de Recurso do Entity Framework.

A questão é definitivamente ao gerar o SQL. Eu tenho um cliente em quem dados a geração de consulta foi de 4 segundos, mas a execução foi de 0,1 segundos.

Percebi que, ao usar o LINQ dynamic e o OR, a geração do sql estava demorando, mas gerava algo que poderia ser armazenado em cache . Então, ao executá-lo novamente, foi reduzido a 0,2 segundos.

Observe que um SQL in ainda foi gerado.

Só mais uma coisa a considerar se você conseguir aguentar o impacto inicial, sua contagem de matriz não muda muito e executa muito a consulta. (Testado no LINQ Pad)

O problema é com a geração de SQL do Entity Framework. Não pode armazenar em cache a consulta se um dos parâmetros for uma lista.

Para obter o EF para armazenar em cache sua consulta, você pode converter sua lista em uma string e fazer um .Contains na string.

Então, por exemplo, esse código rodaria muito mais rápido, já que o EF poderia armazenar em cache a consulta:

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

Quando esta consulta é gerada, ela provavelmente será gerada usando um Like em vez de um In para acelerar o seu C #, mas isso poderia desacelerar seu SQL. No meu caso, não notei nenhuma diminuição de desempenho na minha execução de SQL e o C # ficou significativamente mais rápido.