Collations vs. Performance No SQL-SERVER

Recentemente me deparo com um fato aparentemente pouco conhecido sobre o SQL Server 

- especificamente os Collations do SQL Server e como eles afetam o desempenho.

Durante uma atualização de normalização para seu banco de dados, 

um de nossos clientes teve problemas de desempenho estranhos e inconsistentes

e não conseguiu encontrar uma solução por um bom tempo.

Eles tiveram duas perguntas:

1) A consulta original (não normalizada): varreu 2,5 milhões de linhas e foi executada por 3 segundos.

2) A nova consulta (normalizada): varreu 10 milhões de linhas semelhantes e foi executada por 50 segundos.

Cada consulta foi executada em uma tabela diferente, mas os dados nas tabelas eram idênticos 

(a diferença era devido à normalização). A coluna que procuramos foi varchar (255) em ambas as tabelas.

Por um tempo, não conseguimos entender a razão da enorme diferença de desempenho.

Algo simplesmente não parecia certo.

Durante minha investigação notei uma intrigante diferença nos planos de execução, 

e isso é na primeira consulta (a rápida) que uma conversão implícita foi realizada para a coluna varchar,

mas tal conversão não ocorreu na consulta lenta. No começo eu não prestei atenção porque, 

como DBA, eu fui treinado para pensar que o desempenho de HURT de conversões implícitas ou explícitas, 

e não o contrário, então isso não poderia ter sido o motivo.

Mas eventualmente (e com um pequeno empurrão) Eu dei uma olhada para descobrir 

por que essa conversão estava acontecendo em primeiro lugar.

Descobrimos que o Collation da coluna varchar era diferente entre as duas tabelas.

A primeira tabela tinha um Collation Latin SQL, mas a segunda tabela tinha um agrupamento ANSI 

(ou Windows Collation, se você preferir chamá-la assim).

Nós tentamos mudar o Collation da segunda tabela para o Collation SQL também - e pronto!

Nossa consulta agora executada por 5 segundos - 10 vezes mais rápido!

Procurei materiais sobre esse problema e não consegui encontrar muita coisa além desse (aparentemente importante) artigo Microsoft: http://support.microsoft.com/kb/322112

Notas:

As regras de classificação Unicode são muito mais complexas do que as regras para uma ordem de classificação SQL não-Unicode. Quando o SQL Server compara dados Unicode, os caracteres recebem um peso modificado dinamicamente com base na localidade do agrupamento. Os dados também são modificados por configurações de estilo de comparação, como largura, acento ou sensibilidade do Kana. As rotinas de classificação Unicode suportam comportamentos de ordenação mais inteligentes, como a ordenação de palavras.

[…]

• Se você estiver armazenando e manipulando seus dados usando tipos de dados não-Unicode (char, varchar, text) e estiver usando um collation SQL, as comparações de sequência serão executadas com uma ordem de classificação SQL não-Unicode .

• Se você estiver armazenando e manipulando seus dados usando tipos de dados não-Unicode (char, varchar, text) e estiver usando um collation do Windows, as comparações de sequência serão executadas com as regras de classificação Unicode .

E Isso pode fazer com que determinadas operações que são extraordinariamente dependentes do desempenho da classificação de sequências demorem mais e usem mais CPU do que uma operação semelhante executada com um collation SQL.

• Se você estiver usando tipos de dados Unicode (nchar, nvarchar, ntext), não haverá diferença no comportamento de classificação para o SQL e os collations do Windows. Ambos usarão regras de classificação Unicode.

Em suma, os Windows collation têm um desempenho tão lento quanto os tipos de dados Unicode, 

e os SQL Collation executam muito mais rapidamente.

No entanto, a ordem de classificação deve ser levada em consideração porque pode alterar os resultados.

Embora no caso deste cliente, a primeira tabela era a maneira default de qualquer maneira,

então estamos realmente “consertando” a diferença na ordem de classificação (caso ela existisse).

Posteriormente, também testamos o desempenho de um agrupamento binário e concluímos que

ele era ainda mais rápido em consultas que exigiam comparação e agrupamento, 

mas funcionava pior em consultas que exigiam classificação. Isso faz sentido,

já que os agrupamentos binários devem ser analisados ​​textualmente antes de serem classificados 

- uma operação mais pesada do que fazer o mesmo para collations SQL 

- ao contrário de uma operação de comparação que pode ser tão rápida quanto fazer XOR de dois valores.

Conclusão

SQL ou Windows Collations parecem ter outra importancia que precisamos tomar ao projetar um banco de dados

E analisar seu desempenho - mas cuidado! A alteração do collation pode alterar a ordem de classificação do seu texto,

portanto, se o seu aplicativo depender de uma determinada forma de classificação, será necessário garantir que 

o seu collation corresponda a melhor performance de classificação.

O mesmo artigo microsoft mencionado acima afirma que alterar índices ou como uma consulta é gravada

fará uma diferença maior do que alterar collations, mas é surpreendente a diferença enorme no desempenho

que um collation causou neste exemplo, e tentamos reescrever a consulta várias vezes e alterando os índices,

mas nada fez uma grande diferença, como o Collation.

Encontrou um cenário semelhante?  Problemas semelhantes (ou até mais interessantes - diferentes)?

Conte-nos sobre isso nos comentários!

Ref: Eitan Blumin 

Gustavo Damatto
MCSD - Microsoft Certified Solutions Developerlinkedin.com/in/damatto

Ribeirão Preto-SP