Consulta eficiente

  • Artigo

Consultar com eficiência é um assunto vasto, que aborda assuntos tão abrangentes quanto índices, estratégias de carregamento de entidades relacionadas e muitas outras. Esta seção detalha alguns temas comuns para tornar suas consultas mais rápidas e armadilhas que os usuários normalmente encontram.

Usar índices corretamente

O principal fator decisivo para se uma consulta é executada rapidamente ou não é se ela utilizará corretamente índices quando apropriado: os bancos de dados normalmente são usados para conter grandes quantidades de dados e consultas que atravessam tabelas inteiras normalmente são fontes de problemas sérios de desempenho. Problemas de indexação não são fáceis de detectar, porque não é imediatamente óbvio se uma determinada consulta usará um índice ou não. Por exemplo:

// Matches on start, so uses an index (on SQL Server)
var posts1 = context.Posts.Where(p => p.Title.StartsWith("A")).ToList();
// Matches on end, so does not use the index
var posts2 = context.Posts.Where(p => p.Title.EndsWith("A")).ToList();

Uma boa maneira de detectar problemas de indexação é primeiro identificar uma consulta lenta e, em seguida, examinar seu plano de consulta por meio da ferramenta favorita do banco de dados; consulte a página de diagnóstico de desempenho para obter mais informações sobre como fazer isso. O plano de consulta exibe se a consulta percorre toda a tabela ou usa um índice.

Como regra geral, não há nenhum conhecimento especial de EF para usar índices ou diagnosticar problemas de desempenho relacionados a eles; O conhecimento geral do banco de dados relacionado a índices é tão relevante para aplicativos EF quanto para aplicativos que não usam EF. O seguinte lista algumas diretrizes gerais para ter em mente ao usar índices:

  • Embora os índices acelerem as consultas, eles também reduzem a velocidade das atualizações, pois elas precisam ser mantidas atualizadas. Evite definir índices que não são necessários e considere usar filtros de índice para limitar o índice a um subconjunto das linhas, reduzindo assim essa sobrecarga.
  • Índices compostos podem acelerar consultas que filtram várias colunas, mas também podem acelerar consultas que não filtram todas as colunas do índice, dependendo da ordenação. Por exemplo, um índice nas colunas A e B acelera a filtragem de consultas por A e B, bem como as consultas filtradas apenas por A, mas não acelera as consultas apenas filtrando por B.
  • Se uma consulta for filtrada por uma expressão em uma coluna (por exemplo price / 2), um índice simples não poderá ser usado. No entanto, você pode definir uma coluna persistente armazenada para sua expressão e criar um índice sobre ela. Alguns bancos de dados também dão suporte a índices de expressão, que podem ser usados diretamente para acelerar a filtragem de consultas por qualquer expressão.
  • Bancos de dados diferentes permitem que os índices sejam configurados de várias maneiras e, em muitos casos, os provedores do EF Core os expõem por meio da API fluente. Por exemplo, o provedor do SQL Server permite que você configure se um índice está clusterizado ou defina seu fator de preenchimento. Consulte a documentação do provedor para obter mais informações.

Propriedades somente do projeto de que você precisa

O EF Core facilita muito a consulta de instâncias de entidade e, em seguida, usar essas instâncias no código. No entanto, a consulta de instâncias de entidade pode frequentemente efetuar pull de mais dados do que o necessário do banco de dados. Considere estes fatores:

foreach (var blog in context.Blogs)
{
    Console.WriteLine("Blog: " + blog.Url);
}

Embora esse código precise apenas da propriedade Url de cada Blog, toda a entidade blog é buscada e colunas desnecessárias são transferidas do banco de dados:

SELECT [b].[BlogId], [b].[CreationDate], [b].[Name], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]

Isso pode ser otimizado usando Select para informar ao EF quais colunas projetar:

foreach (var blogName in context.Blogs.Select(b => b.Url))
{
    Console.WriteLine("Blog: " + blogName);
}

O SQL resultante efetua pull apenas das colunas necessárias:

SELECT [b].[Url]
FROM [Blogs] AS [b]

Se você precisar projetar mais de uma coluna, projeta para um tipo anônimo em C# com as propriedades desejadas.

Observe que essa técnica é muito útil para consultas somente leitura, mas as coisas ficam mais complicadas se você precisar atualizar os blogs buscados, já que o controle de alterações do EF só funciona com instâncias de entidade. É possível executar atualizações sem carregar entidades inteiras anexando uma instância de Blog modificada e informando ao EF quais propriedades foram alteradas, mas essa é uma técnica mais avançada que pode não valer a pena.

Limitar o tamanho do conjunto de resultados

Por padrão, uma consulta retorna todas as linhas que correspondem aos filtros:

var blogsAll = context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .ToList();

Como o número de linhas retornadas depende de dados reais em seu banco de dados, é impossível saber quantos dados serão carregados do banco de dados, quanta memória será recolhida pelos resultados e quanta carga adicional será gerada ao processar esses resultados (por exemplo, enviando-os para um navegador de usuário pela rede). Crucialmente, os bancos de dados de teste frequentemente contêm poucos dados, para que tudo funcione bem durante o teste, mas os problemas de desempenho aparecem repentinamente quando a consulta começa a ser executada em dados reais e muitas linhas são retornadas.

Como resultado, geralmente vale a pena pensar em limitar o número de resultados:

var blogs25 = context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .Take(25)
    .ToList();

No mínimo, sua interface do usuário pode mostrar uma mensagem indicando que mais linhas podem existir no banco de dados (e permitir recuperá-las de alguma outra maneira). Uma solução completa implementaria a paginação, em que sua interface do usuário mostra apenas um determinado número de linhas de cada vez e permitiria que os usuários avançassem para a próxima página conforme necessário; consulte a próxima seção para obter mais detalhes sobre como implementar isso com eficiência.

Paginação eficiente

Paginação refere-se à recuperação de resultados em páginas, em vez de tudo de uma vez; isso normalmente é feito para conjuntos de resultados grandes, em que uma interface do usuário é mostrada que permite que o usuário navegue até a próxima página ou página anterior dos resultados. Uma maneira comum de implementar a paginação com bancos de dados é usar os operadores Skip e Take (OFFSET e LIMIT no SQL); embora essa seja uma implementação intuitiva, ela também é bastante ineficiente. Para paginação que permite mover uma página de cada vez (em vez de saltar para páginas arbitrárias), considere usar a paginação de conjunto de chaves.

Para obter mais informações, consulte a página de documentação sobre paginação.

Em bancos de dados relacionais, todas as entidades relacionadas são carregadas pela introdução de JOINs em uma única consulta.

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Blogs] AS [b]
LEFT JOIN [Post] AS [p] ON [b].[BlogId] = [p].[BlogId]
ORDER BY [b].[BlogId], [p].[PostId]

Se um blog típico tiver várias postagens relacionadas, as linhas dessas postagens duplicarão as informações do blog. Essa duplicação leva ao chamado problema de "explosão cartesiana". À medida que mais relações um para muitos são carregadas, a quantidade de dados duplicados pode aumentar e afetar negativamente o desempenho do aplicativo.

O EF permite evitar esse efeito por meio do uso de "consultas divididas", que carregam as entidades relacionadas por meio de consultas separadas. Para obter mais informações, leia a documentação sobre consultas divididas e individuais.

Observação

A implementação atual de consultas divididas executa uma viagem de ida e volta para cada consulta. Planejamos melhorar isso no futuro e executar todas as consultas em uma única viagem de ida e volta.

É recomendável ler a página dedicada em entidades relacionadas antes de continuar com esta seção.

Ao lidar com entidades relacionadas, geralmente sabemos com antecedência o que precisamos carregar: um exemplo típico seria carregar um determinado conjunto de Blogs, juntamente com todas as suas Postagens. Nesses cenários, é sempre melhor usar o carregamento adiantado, para que o EF possa buscar todos os dados necessários em uma viagem de ida e volta. O recurso de inclusão filtrado também permite limitar quais entidades relacionadas você gostaria de carregar, mantendo o processo de carregamento adiantado e, portanto, factível em uma única viagem de ida e volta:

using (var context = new BloggingContext())
{
    var filteredBlogs = context.Blogs
        .Include(
            blog => blog.Posts
                .Where(post => post.BlogId == 1)
                .OrderByDescending(post => post.Title)
                .Take(5))
        .ToList();
}

Em outros cenários, talvez não saibamos qual entidade relacionada precisaremos antes de obtermos sua entidade principal. Por exemplo, ao carregar algum Blog, talvez seja necessário consultar alguma outra fonte de dados - possivelmente um serviço Web - para saber se estamos interessados nas Postagens desse Blog. Nesses casos, o carregamento explícito ou lento pode ser usado para buscar entidades relacionadas separadamente e preencher a navegação postagens do Blog. Observe que, como esses métodos não estão adiantados, eles exigem viagens de ida e volta adicionais para o banco de dados, que é a fonte de desaceleração; dependendo do cenário específico, pode ser mais eficiente carregar sempre todas as Postagens, em vez de executar as viagens de ida e volta adicionais e obter seletivamente apenas as Postagens de que você precisa.

Cuidado com o carregamento lento

O carregamento lento geralmente parece uma maneira muito útil de escrever a lógica do banco de dados, já que o EF Core carrega automaticamente entidades relacionadas do banco de dados à medida que são acessadas pelo seu código. Isso evita o carregamento de entidades relacionadas que não são necessárias (como o carregamento explícito) e, aparentemente, libera o programador de ter que lidar completamente com entidades relacionadas. No entanto, o carregamento lento é particularmente propenso a produzir viagens de ida e volta extras desnecessárias que podem retardar o aplicativo.

Considere estes fatores:

foreach (var blog in context.Blogs.ToList())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

Esse código aparentemente inocente itera em todos os blogs e suas postagens, imprimindo-os. Ativar o log de instruções do EF Core revela o seguinte:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [b].[BlogId], [b].[Rating], [b].[Url]
      FROM [Blogs] AS [b]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[@__p_0='1'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__p_0='2'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__p_0='3'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0

... and so on

O que está acontecendo? Por que todas essas consultas estão sendo enviadas para os loops simples acima? Com o carregamento lento, as Postagens de um Blog são carregadas somente (lentamente) quando sua propriedade Posts é acessada; como resultado, cada iteração no foreach interno dispara uma consulta de banco de dados adicional, em sua própria viagem de ida e volta. Como resultado, após a consulta inicial carregar todos os blogs, temos outra consulta por blog, carregando todas as suas postagens; isso às vezes é chamado de problema N+1 e pode causar problemas de desempenho muito significativos.

Supondo que precisaremos de todas as postagens dos blogs, faz sentido usar o carregamento adiantado aqui. Podemos usar o operador Include para executar o carregamento, mas como só precisamos das URLs dos Blogs (e devemos carregar apenas o que é necessário). Portanto, usaremos uma projeção em vez disso:

foreach (var blog in context.Blogs.Select(b => new { b.Url, b.Posts }).ToList())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

Isso fará com que o EF Core busque todos os Blogs, juntamente com suas Postagens, em uma única consulta. Em alguns casos, também pode ser útil evitar efeitos de explosão cartesianos usando consultas divididas.

Aviso

Como o carregamento lento torna extremamente fácil disparar inadvertidamente o problema N+1, é recomendável evitá-lo. O carregamento adiantado ou explícito torna muito claro no código-fonte quando ocorre uma viagem de ida e volta do banco de dados.

Buffer e streaming

O buffer refere-se ao carregamento de todos os resultados da consulta na memória, enquanto o streaming significa que o EF entrega ao aplicativo um único resultado a cada vez, nunca contendo todo o conjunto de resultados na memória. Em princípio, os requisitos de memória de uma consulta de streaming são fixos – eles são os mesmos se a consulta retorna 1 linha ou 1000; uma consulta de buffer, por outro lado, requer mais memória, quanto mais linhas forem retornadas. Para consultas que resultam em conjuntos de resultados grandes, isso pode ser um fator de desempenho importante.

Se um buffer de consulta ou fluxos depende de como ele é avaliado:

// ToList and ToArray cause the entire resultset to be buffered:
var blogsList = context.Posts.Where(p => p.Title.StartsWith("A")).ToList();
var blogsArray = context.Posts.Where(p => p.Title.StartsWith("A")).ToArray();

// Foreach streams, processing one row at a time:
foreach (var blog in context.Posts.Where(p => p.Title.StartsWith("A")))
{
    // ...
}

// AsEnumerable also streams, allowing you to execute LINQ operators on the client-side:
var doubleFilteredBlogs = context.Posts
    .Where(p => p.Title.StartsWith("A")) // Translated to SQL and executed in the database
    .AsEnumerable()
    .Where(p => SomeDotNetMethod(p)); // Executed at the client on all database results

Se suas consultas retornarem apenas alguns resultados, você provavelmente não precisará se preocupar com isso. No entanto, se a consulta puder retornar um grande número de linhas, vale a pena pensar em streaming em vez de buffer.

Observação

Evite usar ToList ou ToArray se você pretende usar outro operador LINQ no resultado. Isso fará o buffer desnecessariamente de todos os resultados na memória. Use o AsEnumerable em vez disso.

Buffer interno por EF

Em determinadas situações, o EF fará o buffer do conjunto de resultados internamente, independentemente de como você avalia sua consulta. Os dois casos em que isso acontece são:

  • Quando uma estratégia de execução de repetição está em vigor. Isso é feito para garantir que os mesmos resultados sejam retornados se a consulta for repetida posteriormente.
  • Quando a consulta dividida é usada, os conjuntos de resultados de todos, exceto a última consulta, são armazenados em buffer, a menos que MARS (Vários Conjuntos de Resultados Ativos) esteja habilitado no SQL Server. Isso ocorre porque geralmente é impossível ter vários conjuntos de resultados de consulta ativos ao mesmo tempo.

Observe que esse buffer interno ocorre além de qualquer buffer causado por meio de operadores LINQ. Por exemplo, se você usar ToList em uma consulta e uma estratégia de execução de repetição estiver em vigor, o conjunto de resultados será carregado na memória duas vezes: uma vez internamente pelo EF e uma vez por ToList.

Acompanhamento, sem acompanhamento e resolução de identidade

É recomendável ler a página dedicada sobre acompanhamento e sem acompanhamento antes de continuar com esta seção.

O EF rastreia instâncias de entidade por padrão, para que as alterações nelas sejam detectadas e persistidas quando SaveChanges forem chamadas. Outro efeito do acompanhamento de consultas é que o EF detecta se uma instância já foi carregada para seus dados e retornará automaticamente essa instância rastreada em vez de retornar uma nova; isso é chamado de resolução de identidade. De uma perspectiva de desempenho, o controle de alterações significa o seguinte:

  • O EF mantém internamente um dicionário de instâncias controladas. Quando novos dados são carregados, o EF verifica o dicionário para ver se uma instância já está rastreada para a chave dessa entidade (resolução de identidade). A manutenção e as pesquisas do dicionário levam algum tempo ao carregar os resultados da consulta.
  • Antes de entregar uma instância carregada ao aplicativo, o EF captura essa instância e mantém o instantâneo internamente. Quando SaveChanges é chamada, a instância do aplicativo é comparada com o instantâneo para descobrir as alterações a serem mantidas. O instantâneo ocupa mais memória e o processo de instantâneo em si leva tempo; Às vezes, é possível especificar um comportamento de instantâneo diferente, possivelmente mais eficiente por meio de comparadores de valor, ou usar proxies de controle de alterações para ignorar completamente o processo de instantâneo (embora isso venha com seu próprio conjunto de desvantagens).

Em cenários somente leitura em que as alterações não são salvas de volta no banco de dados, as sobrecargas acima podem ser evitadas usando consultas sem acompanhamento. No entanto, como as consultas sem acompanhamento não executam a resolução de identidade, uma linha de banco de dados referenciada por várias outras linhas carregadas será materializada como instâncias diferentes.

Para ilustrar, suponha que estamos carregando um grande número de Postagens do banco de dados, bem como o Blog referenciado por cada Postagem. Se 100 Postagens fizerem referência ao mesmo Blog, uma consulta de acompanhamento detectará isso por meio da resolução de identidade e todas as instâncias de Postagem consultarão a mesma instância de Blog duplicada. Uma consulta sem acompanhamento, por outro lado, duplica o mesmo Blog 100 vezes e o código do aplicativo deve ser escrito adequadamente.

Aqui estão os resultados de um parâmetro de comparação de controle versus comportamento sem acompanhamento para uma consulta carregando 10 Blogs com 20 Postagens cada. O código-fonte está disponível aqui, fique à vontade para usá-lo como base para suas próprias medidas.

Método NumBlogs NumPostsPerBlog Média Erro StdDev Median Proporção RatioSD Geração 0 Gen 1 Gen 2 Alocado
AsTracking 10 20 1.414,7 us 27,20 us 45,44 us 1.405,5 us 1,00 0,00 60.5469 13.6719 - 380,11 KB
AsNoTracking 10 20 993,3 us 24,04 us 65,40 us 966,2 us 0,71 0,05 37.1094 6.8359 - 232,89 KB

Por fim, é possível executar atualizações sem a sobrecarga do controle de alterações, utilizando uma consulta sem acompanhamento e anexando a instância retornada ao contexto, especificando quais alterações devem ser feitas. Isso transfere o ônus do controle de alterações do EF para o usuário e só deverá ser tentado se a sobrecarga de controle de alterações tiver se mostrado inaceitável por meio de criação de perfil ou parâmetro de comparação.

Usar consultas SQL

Em alguns casos, existe um SQL mais otimizado para sua consulta, que o EF não gera. Isso pode acontecer quando o constructo do SQL é uma extensão específica para o banco de dados sem suporte ou simplesmente porque o EF ainda não se traduz para ele. Nesses casos, escrever o SQL manualmente pode fornecer um aumento substancial de desempenho e o EF dá suporte a várias maneiras de fazer isso.

  • Use consultas SQL diretamente em sua consulta, por exemplo, via FromSqlRaw. O EF até permite que você redigir sobre o SQL com consultas LINQ regulares, permitindo que você expresse apenas uma parte da consulta no SQL. Essa é uma boa técnica quando o SQL só precisa ser usado em uma única consulta em sua base de código.
  • Defina uma função definida pelo usuário (UDF) e chame-a de suas consultas. Observe que o EF permite que as UDFs retornem conjuntos de resultados completos - eles são conhecidos como TVFs (funções com valor de tabela) - e também permite mapear uma função DbSet, fazendo com que ela pareça apenas com outra tabela.
  • Defina uma exibição de banco de dados e uma consulta a partir dele em suas consultas. Observe que, ao contrário das funções, as exibições não podem aceitar parâmetros.

Observação

O SQL bruto geralmente deve ser usado como último recurso, depois de garantir que o EF não possa gerar o SQL desejado e quando o desempenho for importante o suficiente para que a consulta fornecida a justifique. O uso de SQL bruto traz desvantagens de manutenção consideráveis.

Programação assíncrona

Como regra geral, para que seu aplicativo seja escalonável, é importante sempre usar APIs assíncronas em vez de síncronas (por exemplo, SaveChangesAsync em vez de SaveChanges). APIs síncronas bloqueiam o thread durante a E/S do banco de dados, aumentando a necessidade de threads e o número de comutadores de contexto de thread que devem ocorrer.

Para obter mais informações, consulte a página sobre programação assíncrona.

Aviso

Evite misturar código síncrono e assíncrono no mesmo aplicativo - é muito fácil disparar inadvertidamente problemas sutis de fome no pool de threads.

Aviso

A implementação assíncrona do Microsoft.Data.SqlClient infelizmente tem alguns problemas conhecidos (por exemplo, nº 593, nº 601 e outros). Se você estiver enfrentando problemas de desempenho inesperados, tente usar a execução de comando de sincronização, especialmente ao lidar com valores binários ou de texto grande.

Recursos adicionais