Um mergulho profundo no desempenho do Entity Framework ao usar "Contains"

Durante meu trabalho diário, uso o Entity Framework. É muito conveniente, mas em alguns casos, seu desempenho é lento. Apesar de haver muitos artigos bons sobre melhorias de desempenho do EF, e alguns conselhos muito bons e úteis são fornecidos (por exemplo, evite consultas complexas, parâmetros em Skip and Take, use views, selecione apenas os campos necessários etc.), não muito pode realmente ser feito quando você precisa usar Contains complexo em dois ou mais campos — em outras palavras, quando você junta dados a uma lista de memória .

Problema

Vamos verificar o seguinte exemplo:

 var localData = GetDataFromApiOrUser(); var query = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId join t in localData on new { s.Ticker, p.TradedOn, p.PriceSourceId } equals new { t.Ticker, t.TradedOn, t.PriceSourceId } select p; var result = query.ToList();

O código acima não funciona no EF 6 e, embora funcione no EF Core, a junção é realmente feita localmente, já que tenho dez milhões de registros em meu banco de dados, todos são baixados e toda a memória é consumida . Este não é um bug no EF. É esperado. No entanto, não seria fantástico se houvesse algo para resolver isso? Neste artigo, farei alguns experimentos com uma abordagem diferente para contornar esse gargalo de desempenho.

Solução

Vou tentar diferentes maneiras de conseguir isso, começando do mais simples ao mais avançado. Em cada etapa, fornecerei código e métricas, como tempo gasto e uso de memória. Observe que interromperei a execução do programa de benchmarking se ele funcionar por mais de dez minutos.

O código para o programa de benchmarking está localizado no repositório a seguir. Ele usa C#, .NET Core, EF Core e PostgreSQL. Eu usei uma máquina com Intel Core i5, 8 GB de RAM e um SSD.

O esquema de banco de dados para teste se parece com isso:

Opção 1. Simples e ingênuo

Vamos tentar algo simples, apenas para começar.

 var result = new List<Price>(); using (var context = CreateContext()) { foreach (var testElement in TestData) { result.AddRange(context.Prices.Where( x => x.Security.Ticker == testElement.Ticker && x.TradedOn == testElement.TradedOn && x.PriceSourceId == testElement.PriceSourceId)); } }

O algoritmo é simples: para cada elemento nos dados de teste, encontre um elemento adequado no banco de dados e adicione-o à coleção de resultados. Este código tem apenas uma vantagem: é muito fácil de implementar. Além disso, é legível e de fácil manutenção. Sua desvantagem óbvia é que é o mais lento. Embora todas as três colunas sejam indexadas, a sobrecarga da comunicação de rede ainda cria um gargalo de desempenho. Aqui estão as métricas:

Assim, para um grande volume, leva aproximadamente um minuto. O consumo de memória parece ser razoável.

Opção 2. Ingênuo com paralelo

Agora vamos tentar adicionar paralelismo ao código. A ideia central aqui é que acessar o banco de dados em threads paralelos pode melhorar o desempenho geral.

 var result = new ConcurrentBag<Price>(); var partitioner = Partitioner.Create(0, TestData.Count); Parallel.ForEach(partitioner, range => { var subList = TestData.Skip(range.Item1) .Take(range.Item2 - range.Item1) .ToList(); using (var context = CreateContext()) { foreach (var testElement in subList) { var query = context.Prices.Where( x => x.Security.Ticker == testElement.Ticker && x.TradedOn == testElement.TradedOn && x.PriceSourceId == testElement.PriceSourceId); foreach (var el in query) { result.Add(el); } } } });

É interessante que, para conjuntos de dados de teste menores, essa abordagem funciona mais lentamente do que a primeira solução, mas para amostras maiores, é mais rápida (aproximadamente 2 vezes neste caso). O consumo de memória muda um pouco, mas não significativamente.

Opção 3. Vários Contém

Vamos tentar outra abordagem:

• Prepare 3 coleções de valores exclusivos de Ticker, PriceSourceId e Date.
• Execute a consulta com uma filtragem de execução usando 3 contém.
• Verifique novamente localmente (veja abaixo).

 var result = new List<Price>(); using (var context = CreateContext()) { var tickers = TestData.Select(x => x.Ticker).Distinct().ToList(); var dates = TestData.Select(x => x.TradedOn).Distinct().ToList(); var ps = TestData.Select(x => x.PriceSourceId) .Distinct().ToList(); var data = context.Prices .Where(x => tickers.Contains(x.Security.Ticker) && dates.Contains(x.TradedOn) && ps.Contains(x.PriceSourceId)) .Select(x => new { x.PriceSourceId, Price = x, Ticker = x.Security.Ticker, }) .ToList(); var lookup = data.ToLookup(x => $"{x.Ticker}, {x.Price.TradedOn}, {x.PriceSourceId}"); foreach (var el in TestData) { var key = $"{el.Ticker}, {el.TradedOn}, {el.PriceSourceId}"; result.AddRange(lookup[key].Select(x => x.Price)); } }

Essa abordagem é problemática. O tempo de execução depende muito dos dados. Ele pode recuperar apenas os registros necessários (nesse caso, será muito rápido), mas pode retornar muitos mais (talvez até 100 vezes mais).

Vamos considerar os seguintes dados de teste:

Aqui eu consulto os preços do Ticker1 negociado em 01-01-2018 e do Ticker2 negociado em 02-01-2018. No entanto, quatro registros serão realmente retornados.

Os valores exclusivos para Ticker são Ticker1 e Ticker2 . Os valores exclusivos para TradedOn são 2018-01-01 e 2018-01-02 .

Portanto, quatro registros correspondem a essa expressão.

É por isso que uma nova verificação local é necessária e por que essa abordagem é perigosa. As métricas são as seguintes:

Consumo de memória horrível! Testes com grandes volumes falharam devido a um tempo limite de 10 minutos.

Opção 4. Construtor de Predicados

Vamos mudar o paradigma: vamos construir uma boa e velha Expression para cada conjunto de dados de teste.

 var result = new List<Price>(); using (var context = CreateContext()) { var baseQuery = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId select new TestData() { Ticker = s.Ticker, TradedOn = p.TradedOn, PriceSourceId = p.PriceSourceId, PriceObject = p }; var tradedOnProperty = typeof(TestData).GetProperty("TradedOn"); var priceSourceIdProperty = typeof(TestData).GetProperty("PriceSourceId"); var tickerProperty = typeof(TestData).GetProperty("Ticker"); var paramExpression = Expression.Parameter(typeof(TestData)); Expression wholeClause = null; foreach (var td in TestData) { var elementClause = Expression.AndAlso( Expression.Equal( Expression.MakeMemberAccess( paramExpression, tradedOnProperty), Expression.Constant(td.TradedOn) ), Expression.AndAlso( Expression.Equal( Expression.MakeMemberAccess( paramExpression, priceSourceIdProperty), Expression.Constant(td.PriceSourceId) ), Expression.Equal( Expression.MakeMemberAccess( paramExpression, tickerProperty), Expression.Constant(td.Ticker)) )); if (wholeClause == null) wholeClause = elementClause; else wholeClause = Expression.OrElse(wholeClause, elementClause); } var query = baseQuery.Where( (Expression<Func<TestData, bool>>)Expression.Lambda( wholeClause, paramExpression)).Select(x => x.PriceObject); result.AddRange(query); }

O código resultante é bastante complexo. Construir expressões não é a coisa mais fácil e envolve reflexão (o que, por si só, não é tão rápido). Mas isso nos ajuda a construir uma única consulta usando muitos … (.. AND .. AND ..) OR (.. AND .. AND ..) OR (.. AND .. AND ..) ... . Estes são os resultados:

Ainda pior do que qualquer uma das abordagens anteriores.

Opção 5. Tabela de dados de consulta compartilhada

Vamos tentar mais uma abordagem:

Eu adicionei uma nova tabela ao banco de dados que conterá os dados da consulta. Para cada consulta, agora posso:

• Iniciar uma transação (se ainda não iniciada)
• Carregar dados de consulta para essa tabela (temporário)
• Faça uma consulta
• Reverter uma transação—para excluir dados carregados

 var result = new List<Price>(); using (var context = CreateContext()) { context.Database.BeginTransaction(); var reducedData = TestData.Select(x => new SharedQueryModel() { PriceSourceId = x.PriceSourceId, Ticker = x.Ticker, TradedOn = x.TradedOn }).ToList(); // Here query data is stored to shared table context.QueryDataShared.AddRange(reducedData); context.SaveChanges(); var query = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId join t in context.QueryDataShared on new { s.Ticker, p.TradedOn, p.PriceSourceId } equals new { t.Ticker, t.TradedOn, t.PriceSourceId } select p; result.AddRange(query); context.Database.RollbackTransaction(); }

Métricas primeiro:

O resultado é muito bom. Muito rápido. O consumo de memória também é bom. Mas as desvantagens são:

• Você tem que criar uma tabela extra no banco de dados para realizar apenas um tipo de consulta,
• Você tem que iniciar uma transação (que consome recursos DBMS de qualquer maneira), e
• Você precisa escrever algo no banco de dados (em uma operação READ!) - e basicamente, isso não funcionará se você usar algo como réplica de leitura.

Mas, além disso, essa abordagem é boa – rápida e legível. E um plano de consulta é armazenado em cache neste caso!

Opção 6. Extensão MemoryJoin

Aqui vou usar um pacote NuGet chamado EntityFrameworkCore.MemoryJoin. Apesar de seu nome ter a palavra Core nele, ele também suporta EF 6. Ele é chamado de MemoryJoin, mas na verdade ele envia os dados de consulta especificados como VALUES para o servidor e todo o trabalho é feito no servidor SQL.

Vamos verificar o código.

 var result = new List<Price>(); using (var context = CreateContext()) { // better to select needed properties only, for better performance var reducedData = TestData.Select(x => new { x.Ticker, x.TradedOn, x.PriceSourceId }).ToList(); var queryable = context.FromLocalList(reducedData); var query = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId join t in queryable on new { s.Ticker, p.TradedOn, p.PriceSourceId } equals new { t.Ticker, t.TradedOn, t.PriceSourceId } select p; result.AddRange(query); }

Métricas:

Isso parece incrível. Três vezes mais rápido que a abordagem anterior, o que a torna a mais rápida até agora. 3,5 segundos para registros de 64K! O código é simples e compreensível. Isso funciona com réplicas somente leitura. Vamos verificar a consulta gerada para três elementos:

 SELECT "p"."PriceId", "p"."ClosePrice", "p"."OpenPrice", "p"."PriceSourceId", "p"."SecurityId", "p"."TradedOn", "t"."Ticker", "t"."TradedOn", "t"."PriceSourceId" FROM "Price" AS "p" INNER JOIN "Security" AS "s" ON "p"."SecurityId" = "s"."SecurityId" INNER JOIN ( SELECT "x"."string1" AS "Ticker", "x"."date1" AS "TradedOn", CAST("x"."long1" AS int4) AS "PriceSourceId" FROM ( SELECT * FROM ( VALUES (1, @__gen_q_p0, @__gen_q_p1, @__gen_q_p2), (2, @__gen_q_p3, @__gen_q_p4, @__gen_q_p5), (3, @__gen_q_p6, @__gen_q_p7, @__gen_q_p8) ) AS __gen_query_data__ (id, string1, date1, long1) ) AS "x" ) AS "t" ON (("s"."Ticker" = "t"."Ticker") AND ("p"."PriceSourceId" = "t"."PriceSourceId")

Como você pode ver, desta vez os valores reais são passados ​​da memória para o servidor SQL na construção VALUES. E isso resolve o problema: o servidor SQL conseguiu realizar uma operação de junção rápida e usar os índices corretamente.

No entanto, existem algumas desvantagens (você pode ler mais no meu blog):

• Você precisa adicionar um DbSet extra ao seu modelo (no entanto, não há necessidade de criá-lo no banco de dados)
• A extensão não suporta classes de modelo com muitas propriedades: três propriedades de string, três propriedades de data, três propriedades de guia, três propriedades float/double e três propriedades int/byte/long/decimal. Isso é mais do que suficiente em 90% dos casos, eu acho. No entanto, se não for, você pode criar uma classe personalizada e usá-la. Então, DICA: você precisa passar os valores reais em uma consulta, caso contrário, os recursos são desperdiçados.

Conclusão

Entre as coisas que testei aqui, eu definitivamente escolheria o MemoryJoin. Alguém pode objetar que as desvantagens são insuperáveis ​​e, como nem todas podem ser resolvidas no momento, devemos nos abster de usar a extensão. Bem, para mim, é como dizer que você não deve usar uma faca porque você pode se cortar. A otimização não era uma tarefa para desenvolvedores juniores, mas para alguém que entende como o EF funciona. Para esse fim, esta ferramenta pode melhorar drasticamente o desempenho. Quem sabe? Talvez um dia, alguém da Microsoft adicione algum suporte básico para VALORES dinâmicos.

Finalmente, aqui estão mais alguns diagramas para comparar os resultados.

Abaixo está um diagrama para o tempo necessário para realizar uma operação. MemoryJoin é o único que faz o trabalho em um tempo razoável. Apenas quatro abordagens podem processar grandes volumes: duas implementações ingênuas, tabela compartilhada e MemoryJoin.

O próximo diagrama é para consumo de memória. Todas as abordagens demonstram mais ou menos os mesmos números, exceto aquele com vários Contains . Este fenômeno foi descrito acima.