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).

USE Traces

IF (OBJECT_ID('TESTE_COLLATION_TABELA_1') IS NOT NULL)

DROP TABLE TESTE_COLLATION_TABELA_1

CREATE TABLE TESTE_COLLATION_TABELA_1 (

ID INT IDENTITY(1,1),

NOME VARCHAR(100),

ENDERECO VARCHAR(500)

)

IF (OBJECT_ID('TESTE_COLLATION_TABELA_2') IS NOT NULL)

DROP TABLE TESTE_COLLATION_TABELA_2

CREATE TABLE TESTE_COLLATION_TABELA_2 (

ID INT IDENTITY(1,1),

NOME VARCHAR(100),

ENDERECO VARCHAR(500)

)

GO

INSERT INTO TESTE_COLLATION_TABELA_1

SELECT 'NOME FULANO', REPLICATE('A',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_1

SELECT 'NOME FULANO', REPLICATE('B',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_1

SELECT 'NOME FULANO', REPLICATE('C',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_1

SELECT 'NOME FULANO', REPLICATE('D',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_2

SELECT 'NOME FULANO', REPLICATE('A',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_2

SELECT 'NOME FULANO', REPLICATE('B',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_2

SELECT 'NOME FULANO', REPLICATE('C',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_2

SELECT 'NOME FULANO', REPLICATE('D',500)

GO 50000

CREATE NONCLUSTERED INDEX SK01_TESTE_COLLATION_TABELA_1

ON TESTE_COLLATION_TABELA_1(ENDERECO)

INCLUDE(NOME)

WITH(DATA_COMPRESSION = PAGE)

CREATE NONCLUSTERED INDEX SK01_TESTE_COLLATION_TABELA_2

ON TESTE_COLLATION_TABELA_2(ENDERECO)

INCLUDE(NOME)

WITH(DATA_COMPRESSION = PAGE)

USE Traces

IF (OBJECT_ID('TESTE_COLLATION_TABELA_1') IS NOT NULL)

DROP TABLE TESTE_COLLATION_TABELA_1

CREATE TABLE TESTE_COLLATION_TABELA_1 (

ID INT IDENTITY(1,1),

NOME VARCHAR(100),

ENDERECO VARCHAR(500)

)

IF (OBJECT_ID('TESTE_COLLATION_TABELA_2') IS NOT NULL)

DROP TABLE TESTE_COLLATION_TABELA_2

CREATE TABLE TESTE_COLLATION_TABELA_2 (

ID INT IDENTITY(1,1),

NOME VARCHAR(100),

ENDERECO VARCHAR(500)

)

INSERT INTO TESTE_COLLATION_TABELA_1

SELECT 'NOME FULANO', REPLICATE('A',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_1

SELECT 'NOME FULANO', REPLICATE('B',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_1

SELECT 'NOME FULANO', REPLICATE('C',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_1

SELECT 'NOME FULANO', REPLICATE('D',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_2

SELECT 'NOME FULANO', REPLICATE('A',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_2

SELECT 'NOME FULANO', REPLICATE('B',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_2

SELECT 'NOME FULANO', REPLICATE('C',500)

GO 50000

INSERT INTO TESTE_COLLATION_TABELA_2

SELECT 'NOME FULANO', REPLICATE('D',500)

GO 50000

CREATE NONCLUSTERED INDEX SK01_TESTE_COLLATION_TABELA_1

ON TESTE_COLLATION_TABELA_1(ENDERECO)

INCLUDE(NOME)

WITH(DATA_COMPRESSION = PAGE)

CREATE NONCLUSTERED INDEX SK01_TESTE_COLLATION_TABELA_2

ON TESTE_COLLATION_TABELA_2(ENDERECO)

INCLUDE(NOME)

WITH(DATA_COMPRESSION = PAGE)

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).

SET STATISTICS IO, TIME ON

SELECT TOP 1000000 TB1.NOME, TB1.ENDERECO

FROM TESTE_COLLATION_TABELA_1 AS TB1

JOIN TESTE_COLLATION_TABELA_2 AS TB2 ON TB1.ENDERECO = TB2.ENDERECO COLLATE SQL_Latin1_General_CP1_CI_AS

SET STATISTICS IO, TIME ON

SELECT TOP 1000000 TB1.NOME, TB1.ENDERECO

FROM TESTE_COLLATION_TABELA_1 AS TB1

JOIN TESTE_COLLATION_TABELA_2 AS TB2 ON TB1.ENDERECO = TB2.ENDERECO COLLATE SQL_Latin1_General_CP1_CI_AS

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

Table 'Worktable'. Scan count 7, logical reads 4758390

Table 'TESTE_COLLATION_TABELA_2'. Scan count 8, logical reads 2614

Table 'TESTE_COLLATION_TABELA_1'. Scan count 9, logical reads 59

SQL Server Execution Times:

CPU time = 15187 ms,  elapsed time = 15184 ms.

Table 'Worktable'. Scan count 7, logical reads 4758390

Table 'TESTE_COLLATION_TABELA_2'. Scan count 8, logical reads 2614

Table 'TESTE_COLLATION_TABELA_1'. Scan count 9, logical reads 59

SQL Server Execution Times:

CPU time = 15187 ms, elapsed time = 15184 ms.

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:

SELECT TOP 1000000 TB1.NOME, TB1.ENDERECO

FROM TESTE_COLLATION_TABELA_1 AS TB1

JOIN TESTE_COLLATION_TABELA_2 AS TB2 ON TB1.ENDERECO = TB2.ENDERECO

SELECT TOP 1000000 TB1.NOME, TB1.ENDERECO

FROM TESTE_COLLATION_TABELA_1 AS TB1

JOIN TESTE_COLLATION_TABELA_2 AS TB2 ON TB1.ENDERECO = TB2.ENDERECO

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!!!

Table 'TESTE_COLLATION_TABELA_2'. Scan count 20, logical reads 1951

Table 'TESTE_COLLATION_TABELA_1'. Scan count 1, logical reads 4

SQL Server Execution Times:

CPU time = 1484 ms,  elapsed time = 12345 ms.

Table 'TESTE_COLLATION_TABELA_2'. Scan count 20, logical reads 1951

Table 'TESTE_COLLATION_TABELA_1'. Scan count 1, logical reads 4

SQL Server Execution Times:

CPU time = 1484 ms, elapsed time = 12345 ms.

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