Compartilhar via

Consulta eficiente

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 = await context.Posts.Where(p => p.Title.StartsWith("A")).ToListAsync();
// Matches on end, so does not use the index
var posts2 = await context.Posts.Where(p => p.Title.EndsWith("A")).ToListAsync();

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 as atualizações, pois precisam ser mantidas up-to-date. 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 mantida 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.

Projete apenas as propriedades necessárias

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

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

Embora esse código precise apenas da propriedade Url de cada Blog, toda a entidade Blog é recuperada, 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:

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

O SQL resultante recupera apenas as 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 = await context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .ToListAsync();

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 = await context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .Take(25)
    .ToListAsync();

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 paginação por 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 às 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 carregamento antecipado, para que o EF possa buscar todos os dados necessários em uma única operação. O recurso de inclusão filtrado também permite limitar quais entidades relacionadas você gostaria de carregar, mantendo o processo de carregamento ansioso e, portanto, factível em uma única viagem de ida e volta:

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

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 tardio pode ser usado para buscar entidades relacionadas separadamente e preencher a navegação de postagens do Blog. Observe que, como esses métodos não são imediatos, eles exigem acessos adicionais ao banco de dados, o que é uma causa de lentidão; dependendo do seu cenário específico, pode ser mais eficiente carregar todas as postagens, ao invés de executar os acessos adicionais e buscar seletivamente apenas as postagens 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 o seguinte:

foreach (var blog in await context.Blogs.ToListAsync())
{
    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 seus posts, imprimindo-os. Ativar o registro 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 (preguiçosamente) 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, teremos outra consulta por blog, carregando todas as suas postagens; às vezes, isso é 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 antecipado 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:

await foreach (var blog in context.Blogs.Select(b => new { b.Url, b.Posts }).AsAsyncEnumerable())
{
    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 ansioso ou explícito torna muito claro no código-fonte quando ocorre uma viagem de ida e volta do banco de dados.

Armazenamento em 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 produzem conjuntos de resultados grandes, isso pode ser um fator de desempenho importante.

Se uma consulta usa buffer ou fluxos, depende de como ela é avaliada.

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

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

// AsAsyncEnumerable 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
    .AsAsyncEnumerable()
    .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 considerar o fluxo em vez do armazenamento em buffer.

Observação

Evite usar ToList ou ToArray se você pretende usar outro operador LINQ no resultado, pois isso fará com que todos os resultados sejam armazenados desnecessariamente na memória. Use AsEnumerable em seu lugar.

Buffer interno pelo EF

Em determinadas situações, o EF armazenará em buffer o 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 que envolve 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 armazenamento em buffer interno ocorre além de qualquer armazenamento em buffer que você causar por meio de operadores LINQ. Por exemplo, se você usar ToList em uma consulta e uma estratégia de repetição de execução estiver em uso, o conjunto de resultados será carregado na memória duas vezes: uma vez internamente pelo EF e uma vez por ToList.

Rastreamento, não rastreamento 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 rastreamento 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 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 rastreadas. 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 armazenadas novamente no banco de dados, as sobrecargas acima podem ser evitadas usando consultas sem rastreamento. No entanto, como as consultas sem rastreamento 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 referenciarão a mesma instância de blog sem duplicação. 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 NumPostsPorBlog Média Erro StdDev Mediana Proporção RatioSD Geração 0 Gen 1 Gen 2 Alocado
AsTracking 10 20 1.414,7 nós 27:20 nós 45.44 nós 1.405,5 us 1.00 0,00 60.5469 13.6719 - 380,11 KB
AsNoTracking 10 20 993.3 nós 24.04 nós 65.40 nós 966.2 nós 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ó deve ser tentado se a sobrecarga do controle de alterações tiver se mostrado inaceitável por meio de perfilagem ou benchmarking.

Usando consultas SQL

Em alguns casos, existe um SQL mais otimizado para sua consulta, que o EF não gera. Isso pode acontecer quando o construto do SQL é uma extensão específica para o banco de dados que não tem suporte ou simplesmente porque o EF ainda não o traduz. 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ê componha em cima do SQL com consultas LINQ comuns, permitindo que você expresse apenas uma parte da consulta em 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 UDF (função definida pelo usuário) e depois chame-a a partir 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 DbSet função, 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 as threads durante a E/S do banco de dados, aumentando a necessidade de threads e o número de trocas de contexto de threads 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 delicados de esgotamento do pool de threads.

Aviso

A implementação assíncrona de Microsoft.Data.SqlClient infelizmente tem alguns problemas conhecidos (por exemplo, #593, #601e outros). Se você estiver vendo problemas inesperados de desempenho, tente usar a execução síncrona de comandos, especialmente ao lidar com grandes volumes de texto ou valores binários.

Recursos adicionais

  • Last updated on