Press enter to see results or esc to cancel.

Entity Framework: uma coluna mapeada errada pode estar matando sua performance

O que seria da vida de um programador sem os grandes momentos de epifania? Um problema bizarro que “não faz sentido” e parece desafiar todos seus anos de experiência de repente se torna claro e óbvio bem na frente de seus olhos.

Recentemente eu e o Angelo Carlotto passamos por essa montanha-russa de emoções através de um problema de performance numa simples consulta SQL através do Entity Framework. O problema era difícil até de explicar, isso dificultava a pesquisa por um caso parecido no StackOverflow, basicamente nosso problema era: a consulta SELECT * FROM [Cobrancas] WHERE CPF = 'numero' retornava instantaneamente no banco de dados, mas era lenta na aplicação, levando mais de 10 minutos através do Entity Framework.

Detectamos o problema quando uma página não carregava nunca devido à lentidão na consulta. Olhamos a linha problemática no código:

    db.Cobrancas.Where(x => x.CPF == 'numero');

Nada demais. O pequeno detalhe é que essa tabela possuía 70 milhões de registros. A primeira coisa que fomos conferir era se existia um índice nesta coluna, e para nossa surpresa o índice estava lá. Nós realmente começamos a coçar a cabeça quando executamos a query direto no banco de dados:

    SELECT * FROM [Cobrancas] WHERE CPF = 'numero'

E ela retornava em 4ms. Ahn???

Já passou por isso também? Depois de vários minutos em negação e de teorias mirabolantes sobre o possível motivo, uma DBA nos trouxe a salvação. Ela nos enviou a consulta que estava sendo realizada e nos informou que estávamos passando como parâmetro para o filtro uma string do tipo NVARCHAR, enquanto a coluna original era do tipo VARCHAR. O SQL faz com que eles sejam compatíveis através de uma conversão implícita, mas eles não são a mesma coisa.

Resumindo, os tipos NCHAR e NVARCHAR se diferem de seus irmãos CHAR e VARCHAR por armazenarem caracteres Unicode. Utilizando uma ferramenta de Profiling vimos que, ao contrário do que pensávamos, nossa consulta não era gerada como a consulta acima, a real consulta era essa:

    SELECT * FROM [Cobrancas] WHERE CPF = N'numero'

Repare no inocente N antes do nosso parâmetro. Esse simples N era o motivo da perda de performance, testando essa query tínhamos o mesmo resultado da consulta feita através do Entity Framework: minutos de espera.

A solução

Primeiro vou mostrar logo a solução do problema, porque tempo é dinheiro, né? Basicamente o que precisávamos era que o Entity Framework entendesse que esse campo não aceitava caracteres Unicode, e que a condição deveria ser gerada sem o N. Tendo um maior entendimento do problema começamos a investigar e encontramos relatos parecidos com o nosso ([1], [2]).

Você precisa apenas desabilitar o modo Unicode para todas as suas colunas que sejam do tipo CHAR ou VARCHAR, dessa forma você garantirá a geração correta da query. O Entity Framework permite que você indique se sua coluna aceitará Unicode ou não, mas por padrão ele trata os tipos string como Unicode, então você precisa definir isso através do método IsUnicode:

    this.Property(x => x.CPF).IsRequired().IsUnicode(false);

Caso esteja utilizando Annotations para mapear suas properties, você pode indicar o tipo da coluna através do parâmetro TypeName, da seguinte forma:

    [Column(TypeName="varchar")]
    public string CPF { get; set; }

Fizemos a alteração necessária no nosso mapeamento e pronto! Executamos o código e conferimos que agora o Entity Framework gerava a condição corretamente, sem N, e a resposta era tão rápida quanto esperada. A vida voltou a ser bela novamente.

Entendendo o problema

Mas afinal, por que isso acontece? O que a conversão implícita tem a ver com tudo isso e por que ela mata a performance? Bem, basicamente é porque ela não aproveita o índice da coluna.

Para recriar o problema, utilizei a famosa base de dados AdventureWorks. Alterei a coluna CarrierTrackingNumber da tabela Sales.SalesOrderDetail para o tipo VARCHAR(50) e criei um índice em cima dela. Executei o mesmo filtro, mas passando o parâmetro de forma diferente:

Caso 1: Com casting implícito

    SELECT CarrierTrackingNumber FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = N'FD9E-44C4-97'

Essa é a consulta gerada se o seu mapeamento está inconsistente. Ela gera este plano de execução:
query-plan1

Caso 2: Sem casting implícito

    SELECT CarrierTrackingNumber FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = 'FD9E-44C4-97'

Essa é a consulta na forma correta. Ela gera este plano de execução:
query-plan2

Elas são semelhantes, mas repare que a primeira consulta gera um Index Scan. Já a segunda consulta, mais rápida, gera um Index Seek. Como você deve imaginar, fazer um Scan em geral é muito mais lento que fazer um Seek. Veja a diferença de custo entre esses dois execution plans:

query compare

O SQL Server tenta te ajudar informando que está sendo feita uma conversão implícita, quando é o caso. Repare que o execution plan do Caso 1 exibe um triângulo de alerta, passando o cursor do mouse você pode ver mais informações a respeito do alerta:

sql server alert

Ele está te informando exatamente sobre o custo de ter uma conversão implícita na sua query, isso é um indicador de que você pode ter uma coluna mapeada de forma errada no seu código.

Conclusão

Apesar de eu ter mostrado esse problema no SQL Server, ele não é exclusivo deste banco de dados, tanto que nós tivemos o problema originalmente numa base de dados Oracle. Revise seus mapeamentos e tenha certeza de que eles estão corretos. Você pode observar esse problema no seu banco de dados e ignorá-lo porque a consulta pode estar rápida, mesmo com a conversão implícita, mas lembre-se que tudo é rápido quando Count <= 10, não espere ter perda de performance para corrigir problemas que vão tirar seu sono quando seus clientes resolverem usar seu sistema.

Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInEmail this to someone
Comments

5 Comments

Robert

Excelente artigo!!

Peterson Seridonio

Excelente artigo. Traz a solução e o entendimento do problema.

Mahmoud Ali

Obrigado Peterson, fico feliz que tenha gostado.

Sérgio D.

Bom texto. Mas incluo uma conclusão a mais ao artigo.

Sempre que uma página ficar mais lenta do que o esperado usando qualquer Entity Framework olhe as consultas executadas ( assim como foi feito 😉 ). Existem algumas outras pegadinhas que são resultado de um mal mapeamento.

Mahmoud Ali

Com certeza Sérgio, é sempre bom lembrarmos que temos um ORM entre a aplicação e o banco de dados quando for necessário.


Leave a Comment