Os perigos da conversão automática de tipos de dados (implicit conversion) (2)

p010_placa conversão proibida_120pxA conversão implícita de tipos de dados, principalmente quando em predicados nas cláusulas FROM e WHERE, pode acarretar em execução lenta de consultas. Na primeira parte deste artigo foi tratado dos impactos na cláusula WHERE; agora, nesta continuação, acrescentamos então o item 2.3, que trata dos impactos da conversão implícita nas junções entre tabelas/exibições na cláusula FROM.

Ter lido a primeira parte deste artigo é pré-requisito para a leitura desta segunda parte. Se você não leu a primeira parte deste artigo, então acesse Os perigos da conversão automática de tipos de dados (1).

1. Introdução

2. Conversão implícita

2.1. Tabela de testes

2.2. Efeitos na cláusula WHERE

2.3. Efeitos na cláusula FROM

Considerando-se construção do tipo

on T1.coluna = T2.coluna

caso T1.coluna não seja do mesmo tipo de dados de T2.coluna, também ocorrerá a conversão implícita.

Como exemplo vamos considerar a existência de 2 tabelas: uma contendo a relação padrão de CEP e outra com cadastro de pessoas. Nesta tabela de cadastro os nomes das localidades estão grafados de forma variada. Para uma mesma localidade há diferenças, inclusive de abreviaturas. Por exemplo, para a localidade “Nossa Senhora de Nazaré”, no cadastro temos “N.S. Nazaré”, “N. Sra. do Nazaré”, “N. Sra. Nazareth”, dentre várias. Então, o objetivo é padronizar o conteúdo da coluna que contém o nome das localidades, a partir das denominações oficiais que constam na tabela padrão de CEP.

As tabelas estão definidas assim:

-- código #2.6
CREATE TABLE dbo.tbCadastro (
     Nome nvarchar(20) not null primary key,
     CEP nchar(8) not null,
     Deno_Localidade nvarchar(70) not null,
     Estado nchar(2) not null
);
go

set nocount on;
INSERT into dbo.tbCadastro (Nome, CEP, Deno_Localidade, Estado)
   values
   (N'Artur', N'64288000', N'N. Sra. do Nazaré', N'PI'),
   (N'Berenice', N'64288000', N'N. Sra. Nazareth', N'PI'),
   (N'Carlos', N'64288000', N'N.S. Nazaré', N'PI'),
   (N'Délia', N'64288000', N'Nossa Senhora de Nazareth', N'PI'),
   (N'Fábio', N'64288000', N'Nossa Senhora de Nazaré', N'PI');

declare @I int, @P int;
set @P= 11111111;
set @I= 6;
while @I <= 100
   begin
   INSERT into dbo.tbCadastro (Nome, CEP, Deno_Localidade, Estado)
      values ('pessoa ' + cast(@I as varchar), 
             cast(@P + @I as char(8)), 
             'Localidade ' + cast(@P + @I as char(8)), 'XX')
   set @I+=1;
   end;
go
-----

CREATE TABLE dbo.tbCEP (
     CEP char(8) not null primary key,
     UF char(2) not null,
     Localidade varchar(60) not null
);
go

set nocount on;
INSERT into dbo.tbCEP (CEP, UF, Localidade) values
     ('64288000', 'PI', 'NOSSA SENHORA DE NAZARÉ');

declare @I int, @P int;
set @P= 64288000;
set @I= 2;
while @I <= 1000
     begin
    INSERT into dbo.tbCEP (CEP, UF, Localidade)
      values (cast(@P + @I as char(8)), 'XX', 
              'Localidade ' + cast(@P + @I as char(8)) )
    set @I+=1;
end;
go

UPDATE STATISTICS dbo.tbCadastro with fullscan;
UPDATE STATISTICS dbo.tbCEP with fullscan;
go

Antes de atualizar serão realizadas consultas, para verificar a correção. O primeiro teste utilizou o seguinte código:

-- código #2.7
SELECT T1.CEP, T1.Deno_Localidade, T2.Localidade
   from dbo.tbCadastro as T1
        inner join dbo.tbCEP as T2 on T1.CEP = T2.CEP;

O plano de execução real do código anterior é o seguinte:

p012_codigo #2.7

No operador Compute Scalar no plano de execução acima ocorreu conversão implícita, conforme consta na imagem abaixo:

p012_codigo #2.7 - conversao

Considerando-se a precedência de tipos de dados, sendo a coluna T1.CEP nvarchar ela força que a coluna T2.CEP seja convertida implicitamente de varchar para nvarchar.

Podemos então reescrever o código, realizando a conversão explícita da coluna T1.CEP:

-- código #2.8
SELECT T1.CEP, T1.Deno_Localidade, T2.Localidade
   from dbo.tbCadastro as T1
        inner join dbo.tbCEP as T2 on cast(T1.CEP as char(8)) = T2.CEP;

Mas, independente de que a conversão seja implícita ou explícita, o predicado na cláusula ON continua non sargable.

p012_codigo #2.8

Para evitar a conversão a solução é alterar a definição da tabela tbCadastro para as colunas CEP e Estado, que não fazem sentido estarem declaradas como unicode.

-- código #2.9
ALTER TABLE dbo.tbCadastro ALTER COLUMN CEP char(8) not null;
ALTER TABLE dbo.tbCadastro ALTER COLUMN Estado char(2) not null;
UPDATE STATISTICS dbo.tbCadastro with fullscan;

É óbvio que antes de efetuar tal alteração é necessário estudar o impacto nos aplicativos existentes. Após a alteração acima, ao reexecutarmos o código #2.7 temos então o seguinte plano de execução:

p012_codigo #2.7 b

Finalmente o predicado tornou-se sargable.

O artigo “Join Performance, Implicit Conversions, and Residuals”, de Paul White, contém vários exemplos de impactos negativos da conversão implícita na cláusula ON; vide capítulo Referências.

2.4. Detectando conversões implícitas

3. Considerações finais

No artigo “Construindo códigos T-SQL eficientes: Sargability” foi tratado de forma introdutória o impacto negativo da conversão implícita em um predicado da cláusula WHERE, tornando-o non sargable. Mas percebeu-se que o assunto necessitava de artigo próprio, somente sobre conversão automática de tipos de dados. Durante as pesquisas para escrever este artigo foi possível notar o quanto o assunto é importante por quem se interessa em construir códigos T-SQL eficientes.

4. Referências


PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para obter o artigo completo.

Os perigos da conversão implícita

 

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Crie seu site com o WordPress.com
Comece agora
%d blogueiros gostam disto: