in C#

Entity Framework, performance e uso de índices em cenários de grandes volumes

O Entity Framework é um ORM extremamente completo, que traz diversos recursos como: testabilidade, implementações assincronas(Await e Async), configurações baseada em codigo(code-based configuration), mapeamento para store-procedures, migrations e até interceptores e logs para as consultas.

Porém em cenários como quado configuramos o mapeamento do objeto manualmente, ou mesmo cenários em que o volume de dados ou requisições é grande, olhar e analisar a consulta que o Entity Framework gera é muito importante para indentificar possiveis gargalos ou melhorias. Tenho utilizado o EF com sucesso em várias aplicações, tive alguns problemas que foram gerados por como o banco estava estruturado e como eu fiz a configuração do mapeamento.

Neste post vou mostrar como é possivel analisar a consulta SQL que é gerada pelo EF,  mostrarei também um exemplo prático de como isto foi útil em um problema que tive.

Criando a Aplicação

Para começar, criei um tabela de exemplo no SQL Server e populei ela com 20.000.000 de registros.

CREATE TABLE [dbo].[CadastroExemplo](
    [Id] [int] NOT NULL,
    [Email] [varchar](300) NULL,
    [Nome] [varchar](500) NULL,
    [DataNascimento] [datetime] NULL
) ON [PRIMARY]

Como aplicação, criei um Console Application e instalei o Entity Framework via nuget.

>> Install-Package EntityFramework

Nesta aplicação, vou criar minha classe que representará o objeto Cadastro.

public class Cadastro
{
    public int Id { get; set; }
    public string EmailPrincipal{ get; set; }
    public string Nome { get; set; }
    public DateTime? DataNascimento { get; set; }
}

Também criarei meu Context e a configuração de mapeamento para a minha tabela.

public class CadastroContext : DbContext
{
    public DbSet<Cadastro> Cadastros { get; set; }
    public CadastroContext()
        : base("Cadastro")
    {

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new CadastroConfiguration());

        base.OnModelCreating(modelBuilder);
    }
}
public class CadastroConfiguration : EntityTypeConfiguration<Cadastro>
{
    public CadastroConfiguration()
    {
        ToTable(tableName: "CadastroExemplo", schemaName: "dbo");
        Property(x => x.NomeCompleto).HasColumnName("Nome");
        Property(x => x.EmailPrincipal).HasColumnName("Email");
    }
}

Outra implementação que fiz no meu projeto foi inserir a connection string no App.config. Com isto temos a aplicação pronta para ser utilizada, acessando o nosso banco de dados, a principal função de nossa aplicação será trazer uma lista com os usuários que possuam o email = [email protected].

Deixando o metodo principal com a seguinte implementação:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new CadastroContext())
            {
                var cadastros =context.Cadastros.Where(a=>a.EmailPrincipal=="[email protected]").ToList();
               
                foreach(var cadastro in cadastros){
                    Console.WriteLine("----- cadastro ------");
                    Console.WriteLine(cadastro.Id);
                    Console.WriteLine(cadastro.NomeCompleto);
                }
            }
        }
    }
}

Criando o Índice

Pensando em melhorar a perfomance da aplicação, vou realisar a consulta que minha aplicação faz no SQL e utilizar o plano de execução para criar o índice que ele sugere, para isto é só habilitar o plano de execução no SQL Management Studio (detalhe amarelo).

image

Como é possivel analisar, a consulta sem indice demora muito( cerca de 27segundos), para melhorar esta performance, vou criar o indice que foi sugerido, e após isto podemos ver que o tempo baixou para 0 segundos.

CREATE NONCLUSTERED INDEX [idx_Email]
ON [dbo].[CadastroExemplo] ([Email])

 

Analisando o plano de execução, agora vemos que a consulta está sendo executada no Indice e não esta realizando um Table Scan, e ainda temos um tempo de execução de 0 segundos \o/

image

Executando a consulta pela aplicação

Entretando, quando executamos nossa aplicação, a qual teoricamente executa o mesmo Select que escrevi anteriormente, temos uma exception sendo lançada: Timeout (a consulta está demorando mais do que o tempo de timeout configurado)

image

Neste caso, a primeira coisa que farei para tentar encontrar o problema é analisar qual SQL está sendo gerado pelo Entity Framework, para isto temos diversas opções, na versão Ultimate do Vsual Studio, temos o intelitrace, que mostra todas as consultas SQL que estão sendo realizadas durante aquele debug.

image

Temos outras maneiras para olhar qual SQL está sendo gerado, neste exemplo vou utilizar uma feature do Entity Framework, que é o Interception (https://entityframework.codeplex.com/wikipage?title=Interception), para ele funcionar vou adicionar a seguinte linha no meu projeto “context.Database.Log = Console.Write;”

static void Main(string[] args)
{
    using (var context = new CadastroContext())
    {
        //Log da consulta que será realizada
        context.Database.Log = Console.Write;

        var cadastros = context.Cadastros.Where(a => a.EmailPrincipal == "[email protected]").ToList();

        foreach (var cadastro in cadastros)
        {
            Console.WriteLine("----- cadastro ------");
            Console.WriteLine(cadastro.Id);
            Console.WriteLine(cadastro.NomeCompleto);
        }
    }
}

Com isto, ao executar meu projeto, poderemos ver qual é o SQL gerado pelo EF.

image

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Email] AS [Email],
    [Extent1].[Nome] AS [Nome],
    [Extent1].[DataNascimento] AS [DataNascimento]
    FROM [dbo].[CadastroExemplo] AS [Extent1]
    WHERE N'[email protected]' = [Extent1].[Email]

A partir deste SQL podemos identificar que a consulta está sendo realizada com uma conversão implicita, causada pelo “N” que está sendo utilizada junto do parametro de email.

Isto é causado pelo tipo de dado que está sendo mapeado no EF, e isto faz com que o SQL Server não utilize o indice que criamos anteriormente, já que ele acaba tendo que fazer a conversão.

Configurando o tipo da coluna

Para resolver este tipo de problema eu precisarei configurar qual o tipo de dado que eu tenho no SQL Server, alterando a minha classe de mapeamente, deixando ela da seguinte maneira:

public class CadastroConfiguration : EntityTypeConfiguration<Cadastro>
{
    public CadastroConfiguration()
    {
        ToTable(tableName: "CadastroExemplo", schemaName: "dbo");
        Property(x => x.NomeCompleto).HasColumnName("Nome");
        Property(x => x.EmailPrincipal).HasColumnName("Email").HasColumnType("varchar");
    }
}

Após configurar o ColumType, ao executar nosso programa, vemos que a consulta gerada não realiza a conversão, e que a consulta é executada instataneamente \o/

image

Bom espero que este post seja útil, a principal mensagem que eu queria deixar é que nos preocupemos com o SQL que é gerado pelos ORM’s que utilizamos, o projeto está no meu GitHub https://github.com/rodolfofadino/EF-PerformanceExample/

Estou a disposição para dúvidas, criticas e sugestões.

abs

Rodolfo Fadino

  • Eduardo Cucharro

    Conheço um sistema com problemas parecido com o desse post. 🙂

    Parabéns pelo trabalho!

  • https://wenndersantos.wordpress.com/ Wennder Santos

    Muito útil Rodolfo, parabéns.

  • Anderson Gonçalves

    Muito bom, parabéns por compartilhar.