Casos do Dia a Dia – Cuidado ao utilizar o comando COLLATE!

Fala Pessoal,

A ideia desse post é compartilhar mais um Caso do Dia a Dia que encontrei em um cliente ao fazer a análise de uma query que estava com lentidão e o motivo era a utilização do comando COLLATE.

Você conhece o comando COLLATE? Sabe pra que ele serve e qual o impacto ao utilizá-lo? Então vamos lá…


COLLATE:

Gostei muito de uma definição sobre o COLLATE que encontrei no link abaixo:

https://pt.stackoverflow.com/questions/209779/o-que-collate-latin1-general-cs-ai-faz

“Segundo a documentação do COLLATE (traduzido by Google) é:

COLLATE é uma cláusula que pode ser aplicada a uma definição de banco de dados ou a uma definição de coluna para definir o agrupamento ou a uma expressão de sequência de caracteres para aplicar um elenco de agrupamento.

Em resumo, especifica o conjunto de caracteres e regras que está utilizando.

Ao separar o COLLATE LATIN1_GENERAL_CS_AI, temos as seguintes funções:

 LATIN1: Define o charset que será utilizado. Você pode fazer isso para o banco ou por consulta.

 CS: Especifica como Case Sensitive;

 AI: Especifica como Accent Insensitive.

Também temos outras opções, como:

 CI: Especifica como Case Insensitive.

 AS: Especifica como Accent Sensitive.

 BIN: Especifica a ordem de classificação a ser utilizada como binária.”

Caso você queira conhecer um pouco mais sobre o comando COLLATE, no final desse post deixei mais alguns links de referência sobre o assunto.


Montando o Cenário:

Segue abaixo o script de criação do cenário para a demonstração desse post. Em resumo:

  • Utilizo uma database chamada “Traces”, mas você pode alterar para o nome que desejar;
  • Crio duas tabelas que utilizam a mesma COLLATION;
  • Populo as duas tabelas com 200 mil registros cada para ter uma massa de dados e conseguir gerar uma diferença maior nos Reads;
  • Crio alguns índices para ajudar no desempenho;
  • OBS: Se a sua versão do SQL Server não suportar a compressão de dados, retire a cláusula “WITH(DATA_COMPRESSION = PAGE)” da criação dos índices;
  • A execução desse script pode demorar alguns minutos (na minha máquina levou uns 3 minutos).

Problema ao utilizar o COLLATE:

Agora chegamos ao ponto que interessa. Após criar as tabelas, repare que elas possuem a mesma COLLATION na coluna “ENDERECO”.

Tabela: TESTE_COLLATION_TABELA_1

Tabela: TESTE_COLLATION_TABELA_2

A query abaixo simula a mesma situação que encontrei no cliente que citei no início do post. Repare que na linha do JOIN ela utiliza o comando “COLLATE SQL_Latin1_General_CP1_CI_AS”. A única diferença dessa collation para a coluna é que ela é AS (Accent Sensitive) e a coluna é AI (Accent Insensitive).

Repare na quantidade altíssima de Reads que chega a quase 5 milhões! O tempo de execução foi de 15 segundos.

Segue o Plano de Execução:

Veja que o Plano de Execução utilizou bastante paralelismo (aumenta o consumo de CPU) e gerou o WARNING abaixo informando que a CONVERSÃO da coluna “ENDERECO” pode afetar a escolha de um “SeekPlan”. E realmente o plano não fez nenhuma operação de “Index Seek”.

Visto isso, vamos remover o comando COLLATE e executar a query abaixo:

Repare que dessa vez a quantidade de Reads diminuiu drasticamente, ficando com menos de 2 mil ao invés dos quase 5 milhões e o tempo também foi reduzido em alguns segundos!!! WOW Luiz!!! Sensacional!!!

Segue o novo Plano de Execução:

Observe que o plano de execução sem utilizar o COLLATE ficou muitooo mais simples do que o anterior!

 Dessa vez o novo plano não precisou utilizar o paralelismo (utilizou menos CPU), não gerou o WARNING e ainda fez uma operação de “Index Seek”. Conseguiu notar quanta diferença em relação ao plano anterior?


Conclusão:

Podemos concluir que o comando COLLATE pode gerar um grande impacto no Plano de Execução quando utilizado indevidamente.

Portanto, quando você se deparar com uma query e ela possuir o comando COLLATE, verifique a collation das colunas envolvidas e valide com o cliente se é realmente necessário utilizá-lo. No meu caso, verifiquei com o cliente que informou que o código era legado e provavelmente tinha alguma database que possuía as colunas com collation diferente na época, mas essa database específica não tinha esse problema. Com isso, ele removeu o COLLATE, pois naquele caso não seria necessário e a query passou a executar instantaneamente!

O print abaixo é o caso real do cliente, onde a query demorava mais de 6 segundos pra executar com mais de 300 mil reads. Após remover o comando COLLATE, a query passou a executar instantaneamente (tive que colocar um WAITFOR DELAY ’00:00:03’ para ela conseguir aparecer no Trace de Queries Demoradas) e utilizando muito menos recursos!

Resultado: mais um cliente feliz com a consultoria TOP de Banco de Dados!!! #TeamFabricioLima


Download “Script Cuidado ao utilizar o comando COLLATE”:

Segue o link para baixar o script utilizado nesse Post:

Link: https://github.com/luizvitorf/SQLServer/blob/master/Scripts/T-SQL/Casos%20do%20Dia%20a%20Dia%20-%20Cuidado%20ao%20utilizar%20o%20comando%20COLLATE.sql


Referências:

Segue abaixo mais alguns links interessantes que também falam sobre o comando COLLATE:

https://pt.stackoverflow.com/questions/209779/o-que-collate-latin1-general-cs-ai-faz

https://docs.microsoft.com/en-us/sql/t-sql/statements/collations

https://luanmorenodba.com/2013/01/09/collation-diferentesseek-ou-scan/

https://www.mssqltips.com/sqlservertip/3215/how-column-collation-can-affect-sql-server-query-performance/

https://www.sqlservercentral.com/forums/topic/performance-impact-due-to-different-collations

https://stackoverflow.com/questions/5039211/what-does-collate-sql-latin1-general-cp1-ci-as-do

https://docs.microsoft.com/en-us/sql/t-sql/statements/sql-server-collation-name-transact-sql

https://www.fabriciolima.net/blog/2017/02/08/improve-the-query-performance-with-like-string-changing-only-the-collation/

https://www.fabriciolima.net/blog/2017/02/06/video-melhorando-a-performance-de-uma-consulta-com-like-string-alterando-a-collation/

https://www.fabriciolima.net/blog/2012/01/10/casos-do-dia-a-dia-erro-de-collation-em-uma-consulta/


Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

3 comentários em “Casos do Dia a Dia – Cuidado ao utilizar o comando COLLATE!

  1. Pingback: Dicas de Tuning – Como o Paralelismo pode afetar o CPU Time? – Luiz Lima

Deixe uma resposta