Collation Diferentes–Seek ou Scan ?

Posted on janeiro 9, 2013

4


Introdução

O Collation no banco de dados tem um papel fundamental, ele provém regras de ordenação, e propriedades para utilização de ascento sensitivo/case sensitivo. Os collations são usados para tipos de dados caracteres como CHAR, VARCHAR…. O Collation do banco de dados é configurado durante a instalação da instância do banco de dados.

Quando um Collate é selecionado para a instância , banco de dados, campo na tabela, ou expressão os caracteres passam a possuir características únicas. O Collate padrão do SQL Server é o SQL_Latin1_General_CP1_CI_AS.

Mapa Mental – Collation SQL Server

A função do Mapa Mental e descrever de forma mais simplória alguma idéia, organizando um pensamento de forma mais objetiva e clara, nesse caso, quais são as funções do Collate dentro de uma instância de banco de dados.

MapaMental - CollationSQLServer

(Figura 1 – Mapa Mental do Collation de uma Instância de Banco de Dados.)

Q.O – Scan ou Seek ?

Podemos ter o Collate para instância, banco de dados, coluna ou até uma expressão. Essa granularidade é bastante útil, porém podemos nos deparar com alguns problemas durante o percurso. Veremos agora como o Collate pode nos causar grandes problema de performance se utilizado de forma inapropriada. Realizaremos a criação de um banco de dados e a criação de duas tabelas.

CREATE DATABASE DemoCollation

 

USE DemoCollation

go

 

SELECT database_id,

          name,

          collation_name

FROM sys.databases

WHERE name = ‘DemoCollation’

image

(Figura 2 – Collate do Banco de Dados criado.)

CREATE TABLE dbo.DadosCliente

(

       ID INT IDENTITY(1,1) NOT NULL,

       Nome VARCHAR(50) NOT NULL,

       DataNascimento DATETIME NOT NULL,

       Sexo CHAR(1) NOT NULL,

       EstadoCivil CHAR(1) NOT NULL

)

 

INSERT INTO dbo.DadosCliente (Nome, DataNascimento, Sexo, EstadoCivil)

VALUES (‘Luan.Moreno’,‘1988-07-20’,‘M’,‘C’),

       (‘Juscélio.Reis’,‘1988-04-15’,‘M’,‘C’),

       (‘Matheus.Carmago’,‘1994-01-02’,‘M’,‘S’),

       (‘Cláudio.Henrique’,‘1983-01-23’,‘M’,‘C’)

 

 

CREATE TABLE dbo.DadosPessoaFisica

(

       ID INT IDENTITY(1,1) NOT NULL,

       Nome VARCHAR(50) COLLATE Latin1_General_CS_AI NOT NULL ,

       DataNascimento DATETIME NOT NULL,

       Sexo CHAR(1) NOT NULL,

       EstadoCivil CHAR(1) NOT NULL

)

 

INSERT INTO dbo.DadosPessoaFisica(Nome, DataNascimento, Sexo, EstadoCivil)

VALUES (‘Luan.Moreno’,‘1988-07-20’,‘M’,‘C’),

       (‘Juscélio.Reis’,‘1988-04-15’,‘M’,‘C’),

       (‘Matheus.Carmago’,‘1994-01-02’,‘M’,‘S’),

       (‘Cláudio.Henrique’,‘1983-01-23’,‘M’,‘C’)

Porém entre as tabelas temos uma grande diferença, o COLLATE do campo nome são divergentes. Na tabela dbo.DadosCliente temos o collate contendo CI_AS e na tabela dbo.DadosPessoaFisica temos o COLLATE aceitando CS_AI. E para que possamos realizar um SEEK nessa consulta teremos o seguinte índice criado.

CREATE NONCLUSTERED INDEX idxNCL_DadosPessoaFisica_NomeDataNascimento

ON DadosPessoaFisica (Nome, DataNascimento)

SELECT Nome, DataNascimento

FROM dbo.DadosCliente AS DC

INNER JOIN dbo.DadosPessoaFisica AS DPF

ON DC.Nome = DPF.Nome

Porém, quando realizamos a comparação entre os campos das tabelas, para cada COLLATION a informação é “organizada“ de uma forma diferente, nesse caso é necessário realizar a comparação utilizando o AS – Ascent Sensitive, então faremos o seguinte.

Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "Latin1_General_CS_AI" and "Latin1_General_CI_AS" in the equal to operation.

Com isso, como possuímos os nomes contendo ascento iremos fazer com que a tabela dbo.DadosPessoaFisica utilize o COLLATE CI_AS.

SELECT DPF.Nome, DPF.DataNascimento

FROM dbo.DadosCliente AS DC

INNER JOIN dbo.DadosPessoaFisica AS DPF

ON DC.Nome = DPF.Nome COLLATE Latin1_General_CI_AS

image

(Figura 3 – Result Set da comparação entre tabelas, mesmo Collation.)

Porém, se olharmos o Plano de Execução que o Q.O utilizou veremos que….

image

(Figura 4 – Utilização do SCAN na tabela DadosPessoaFisica.)

De fato, não era para ele estar utilizando o Index Scan, isso porque temos um índice cover para essa consulta, porém quando passamos essa HINT para a comparação entre as tabelas, a tabela na qual possuia um Code Page e Collation passa a ter que comparar os caracteres de uma forma diferente e com isso ele não consegue realizar um SEEK. Por mais que pareça simples, há diversos problemas em se utilizar o SCAN, em pequenas tabelas não há problema consideráveis, agora quando você tenta comparar uma tabela com 180 Mihões de Registros essa comparação se torna inviável. Se verificarmos mais profundamente poderemos ver algumas características, como…

image

(Figura 5 – Plano de Execução – Hash Match utilizando o PROBE RESIDUAL.)

O ponto principal do PROBE RESIDUAL é que alguns dos predicados, no caso a HINT COLLATION não pode ser comparado tão efetivamente com o outro campo, e isso faz com que  o campo tenha que ser comparado com cada registro da outra tabela do banco de dados.

image

(Figura 6 – A comparação para o campo e feito por uma expressão, com isso temos um SCAN na tabela.)

Para a solução desse problema é necessário a alteração do COLLATION no campo nome. Porém como possuímos um índice criado contendo esse campo, é necessário que o mesmo seja recriado.

ALTER TABLE DadosPessoaFisica

       ALTER COLUMN Nome VARCHAR(50) COLLATE Latin1_General_CI_AS NOT NULL

Msg 5074, Level 16, State 1, Line 1
The index ‘idxNCL_DadosPessoaFisica_NomeDataNascimento’ is dependent on column ‘Nome’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Nome failed because one or more objects access this column.

DROP INDEX DadosPessoaFisica.idxNCL_DadosPessoaFisica_NomeDataNascimento

 

CREATE NONCLUSTERED INDEX idxNCL_DadosPessoaFisica_NomeDataNascimento

ON DadosPessoaFisica (Nome, DataNascimento)

SELECT DPF.Nome, DPF.DataNascimento

FROM dbo.DadosCliente AS DC

INNER JOIN dbo.DadosPessoaFisica AS DPF

ON DC.Nome = DPF.Nome COLLATE Latin1_General_CI_AS

image

(Figura 7 – Seek sendo utilizado na comparação.)

Com o COLLATION agora igual temos um SEEK durante a comparação do campo nome ou seja é exatamento isso que queremos para nosso plano de execução.

Conclusão

Sempre tenha cuidado quando for realizar a comparação de caracteres em banco de dados com COLLATION diferentes, se a tabela for pequena aplique o HINT para a consulta, agora se a mesma for gigante então você poderá mudar o COLLATION do campo para que o índice possa ser aproveitado com eficiência.