Otimizar o desempenho das consultas ao banco de dados é crucial para garantir aplicativos responsivos e eficientes. Neste artigo, veremos como aproveitar os recursos do Entity Framework Core (EF Core) para otimizar consultas ao banco de dados usando índices.

Veremos vários exemplos reais que demonstram o impacto das técnicas de otimização no desempenho da consulta.

Usando a indexação para ter uma resposta mais rápida

Um índice em um banco de dados é uma estrutura de dados que melhora a velocidade de recuperação de linhas de uma tabela baseado nos valores de uma ou mais colunas. Em essência, um índice é como um índice de um livro, onde você pode encontrar rapidamente informações específicas sem precisar ler o livro inteiro.

Como os Índices Melhoram o Desempenho ?

1. Redução da Quantidade de Dados a Serem Lidos:
   • Quando você consulta um banco de dados sem um índice em uma coluna, o banco de dados precisa percorrer todas as linhas da tabela para encontrar o que você está procurando. Com um índice, o banco de dados pode “saltar” diretamente para as linhas relevantes, reduzindo drasticamente o número de leituras de disco necessárias.
2. Acesso Direto aos Dados:
   • Um índice permite que o banco de dados acesse diretamente a localização das linhas relevantes, em vez de ter que pesquisar sequencialmente por elas. Isso é especialmente útil quando há muitos registros na tabela.
3. Otimização do Plano de Execução:
   • Com índices, o otimizador de consultas do banco de dados pode escolher um plano de execução mais eficiente, como usar um “Scan” ao invés de um “Table Scan” (ler a tabela inteira).

Como Criar os ìndices ?

Na plataforma .NET atualmente podemos criar índices usando duas abordagens prinicpais que iremos abordar a seguir. Para ilustrar vamos criar um índice para a coluna Nome da tabela Produtos que foi mapeada a partir da entidade Produto.

1- Usando o recurso Migrations do EF Core

Adicione uma migração para adicionar o índice:  dotnet ef migrations add AddIndexToProdutoNome

No arquivo de migração gerado, você pode adicionar o índice à coluna Categoria:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateIndex(
        name: "IX_Produtos_Nome",
        table: "Produtos",
        column: "Nome");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropIndex(
        name: "IX_Produtos_Nome",
        table: "Produtos");
}

Aplique a migração para atualizar o banco de dados:  dotnet ef database update

2- Usando a Fluent API

Podemos usar a Fluent API e definir o indice na entidade de configuração da seguinte forma:

1- No método OnModelCreating podemos definir o seguinte código para criar o índice:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    CriarIndice(modelBuilder);
     //..
 }
private void CriarIndice(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Produto>()
        .HasIndex(p => p.Nome)
        .HasDatabaseName("IX_Produtos_Nome");
}

A seguir você pode criar uma nova migração e aplica ao banco de dados

dotnet ef migrations add AddIndexToProdutosNome
dotnet ef database update

Vamos então criar um novo projeto contendo um projeto Class Library EFCoreDomain que tem as entidades:

public class Categoria
{
    public int Id { get; set; }
    public string? Nome { get; set; }
    public ICollection<Produto>? Produtos { get; set; }
}

public class Produto
{
    public int Id { get; set; }
    [StringLength(100)]
    public string? Nome { get; set; }
    [StringLength(100)]
    public string? Imagem { get; set; }
    public int Estoque { get; set; }
    public Categoria? Categoria { get; set; }
    public int CategoriaId { get; set; }
}

Vamos criar uma pasta Context e nesta pasta criar a classe de contexto AppDbContext:

 public class AppDbContext : DbContext
{
    public DbSet<Produto> Produtos { get; set; }
    public DbSet<Categoria> Categorias { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(
         "Data Source=<<Sua_Conexao;")
             .LogTo(Console.WriteLine, LogLevel.Information);
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        CriarIndice(modelBuilder);
        // Criação de 10 categorias
        for (int i = 1; i <= 10; i++)
        {
            modelBuilder.Entity<Categoria>().HasData(
                new Categoria { Id = i, Nome = $"Categoria {i}" }
            );
        }
        // Criação de 100 produtos com relação às categorias
        var produtos = new List<Produto>();
        for (int i = 1; i <= 10000; i++)
        {
            produtos.Add(new Produto
            {
                Id = i,
                Nome = $"Produto {i}",
                Imagem = $"imagem{i}.jpg",
                Estoque = i * 10,
                CategoriaId = i % 10 + 1              });
        }
        modelBuilder.Entity<Produto>().HasData(produtos.ToArray());
    }
    private void CriarIndice(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Produto>()
            .HasIndex(p => p.Nome)
            .HasDatabaseName("IX_Produtos_Nome");D
    }
} 

Estamos criando uma tabela Produtos contendo 10000 produtos e uma tabela Categorias contendo 10 categorias. O método CriarIndice() permite criar o índice para a coluna Nome da tabela Produtos.

Agora na classe Program temos o código que vai criar o banco de dados e realizar uma consulta LINQ que iremos testar com e sem usar o índice na coluna Nome.

using (AppDbContext context = new AppDbContext())
{
    Console.WriteLine("Criando banco de dados e tabelas... Aguarde ...\n");
    context.Database.EnsureDeleted();
    context.Database.EnsureCreated();
}
Consulta("Produto 9994");
Console.ReadKey();
void Consulta(string nome)
{
    using (var ctx = new AppDbContext())
    {
        Stopwatch stopwatch = new Stopwatch();
        // Inicia o cronômetro
        stopwatch.Start();
        var products = ctx.Produtos
                          .Where(p => p.Nome == nome).ToList();
        // Para o cronômetro
        stopwatch.Stop();
        // Calcula o tempo de execução
        TimeSpan tempoExecucao = stopwatch.Elapsed;
        // Mostra o tempo de execução em milissegundos
        Console.WriteLine($"Tempo de execução da consulta (sem índice): {tempoExecucao.TotalMilliseconds} ms");
        // Exemplo de como acessar os dados
        foreach (var product in products)
        {
            Console.WriteLine($"Produto: {product.Nome}, Categoria: {product.CategoriaId}");
        }
    }
} 

Vamos executar a consulta sem usar índices onde temos o seguinte resultado:

Agora vamos descomentar o método CriarIndice() e vamos executar a consulta usando o índice na coluna Nome:

Observe que mesmo para um pequeno número de registros na tabela Produtos já temos uma diferença no tempo de execução da consulta.

Em resumo, usar um índice em uma coluna de uma tabela no contexto do EF Core e C# pode significativamente melhorar o desempenho das consultas que fazem uso desse índice. Isso resulta em tempos de resposta mais rápidos e melhor escalabilidade do sistema, tornando as consultas mais eficientes e menos onerosas em termos de recursos do banco de dados.