Os perigos da conversão implícita (1)

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. No artigo “Construindo códigos T-SQL eficientes: Sargability” o assunto foi tratado de forma introdutória e agora neste artigo é esmiuçado, inclusive com técnicas para detectar os casos de conversão implícita.

1. Introdução

Quem programa em T-SQL provavelmente lê com certa frequência a recomendação para evitar a conversão implícita; inclusive no artigo “Construindo códigos T-SQL eficientes: Sargability” é demonstrado um dos efeitos negativos decorrentes da conversão implícita.

Mas o que é a conversão implícita, quando ela ocorre e quais os efeitos decorrentes de sua ocorrência?

1.1. Tipos de dados

As colunas, variáveis e alguns objetos utilizados em T-SQL são declarados com determinado tipo de dados, que é um atributo que especifica o que o objeto pode manter: dados inteiros, dados de caractere, dados monetários, data e hora, etc.

Os tipos de dados são agrupados em categorias: numérico, cadeia de caracteres (string), data & hora, etc. e estão documentados no Manual On Line. Vide item “Tipos de dados” no capítulo Referências, ao final.

1.2. Conversão de dados

A conversão é alterar um objeto de um tipo de dados para outro tipo de dados, sendo que em T-SQL dispomos das funções CAST e CONVERT para efetuarmos conversão. Por exemplo, para converter um valor de caractere para numérico podemos ter

-- código #1.1
declare @CPF char(11);
set @CPF= '12345678900';

SELECT cast(@CPF as bigint);

Esse tipo de conversão em que se utiliza a função CAST (ou CONVERT) é denominada de conversão explícita.

Há situações em que ocorre conversão de tipos de dados sem que essa conversão conste no código do programa. Por exemplo, ao somar 2 valores que estejam armazenados em variáveis com tipos de dados diferentes o próprio SQL Server se encarrega de internamente realizar a conversão de uma das variáveis para então somar os valores.

-- código #1.2
declare @TB table (Qtd_Loja1 tinyint, Qtd_Loja2 int);
INSERT into @TB values (120, 45000);

SELECT Soma= Qtd_Loja1 + Qtd_Loja2 from @TB;

No último comando do código anterior, com a instrução SELECT, o conteúdo de Qtd_Loja1 é previamente convertido de tinyint para int e a seguir a operação matemática é realizada. Essa conversão interna é denominada de conversão implícita. Para observar a ocorrência delas uma forma é analisar o plano de execução do código.

1.3. Precedência de tipos de dados

Sempre que for necessário converter implicitamente um objeto de um tipo de dados para outro, o SQL Server utiliza uma regra chamada de precedência de tipos de dados. Quando um operador relacionar duas expressões com tipos de dados diferentes, as regras de precedência do tipo de dados especificam que aquele com a precedência mais baixa será convertido para o tipo de dado de maior precedência.

Eis a tabela de precedência de tipos de dados, do item de maior importância para o de menor importância:

30. UDT (user data type) ⇐ maior prioridade
29. sql_variant
28. xml
27. datetimeoffset
26. datetime2
25. datetime
24. smalldatetime
23. date
22. time
21. float
20. real
19. decimal
18. money
17. smallmoney
16. bigint
15. int
14. smallint
13. tinyint
12. bit
11. ntext
10. text
09. image
08. timestamp
07. uniqueidentifier
06. nvarchar
05. nchar
04. varchar
03. char
02. varbinary
01. binary ⇐ (menor prioridade)

Na tabela anterior observa-se que expressões do tipo de dados int têm precedência sobre tinyint. Isto explica porque no código #1.2 o conteúdo da coluna Qtd_Loja1 foi convertido implicitamente para int, antes de realizar a soma.

A expressão cujo tipo de dados seja de maior valor na tabela anterior força com que a outra expressão seja convertida implicitamente para o mesmo tipo de dados. Deve-se observar que nem sempre a conversão é possível.

1.4. Quadro de conversão

O quadro a seguir ilustra as possibilidades da conversão, considerando-se o tipo de dados de origem e de destino.

p010_SQL Server Data Type Conversion Chart

Observe que nas linhas estão as origens (From) e nas colunas os destinos (To). Em cada interseção de linha/coluna há um símbolo que nos informa se a conversão é possível, se ela pode ocorrer de forma implícita (automática) ou se somente de forma explícita (manual).

As conversões que podem ocorrer de forma implícita estão marcadas com círculo laranja; por exemplo, de char para nchar pode ocorrer conversão automática. Já as conversões que somente são possíveis de forma manual (isto é, explícitas) estão marcadas com o quadrado azul; por exemplo, converter um objeto do tipo de dados xml para varchar. Mas, como mencionado anteriormente, nem toda conversão é possível; são as que estão marcadas com X vermelho. Por exemplo, não há como converter diretamente de xml para datetime.

O quadro é bem didático, de fácil interpretação. Ele está disponível na página “SQL Server Data Type Conversion Chart”; consulte no capítulo Referências, ao final.

2. Conversão implícita

Este capítulo trata da conversão implícita que pode ocorrer em código T-SQL, em várias situações.

2.1. Tabela de testes

Para testes deste artigo foi utilizada a tabela Teste_Implicit, criada pelo código a seguir.

-- código #2.1
CREATE TABLE dbo.Teste_Implicit (
   ID int identity primary key,
   Data_Entrada char(8) not null,
   Data_Saída date not null
);
--
declare @D1 date;
set dateformat dmy;
set @D1= cast('10/10/2008' as date);
INSERT into dbo.Teste_Implicit (Data_Entrada, Data_Saída)
   SELECT convert(char(8), dateadd(day, +(n % 30), @D1), 112),
          dateadd(day, +(n % 30), @D1)
     from dbo.GetNums (0, 99999);

CREATE nonclustered INDEX I2_TI on dbo.Teste_Implicit (Data_Entrada);
CREATE nonclustered INDEX I3_TI on dbo.Teste_Implicit (Data_Saída);

O código anterior gera tabela de testes com 100.000 linhas. Observe que os tipos de dados das colunas Data_Entrada e Data_Saída são propositalmente diferentes.

A função dbo.GetNums é de autoria de Itzik Ben-Gan e o código dela está no apêndice deste artigo.

2.2. Efeitos na cláusula WHERE

Considerando construção do tipo

WHERE coluna = valor

já sabemos que, caso o tipo de dados de coluna seja diferente do de valor, ocorre conversão implícita ou em coluna ou em valor, de modo que ambos fiquem com o mesmo tipo de dados e a comparação seja então possível.

2.2.1. Caso 1

No primeiro caso vamos utilizar pesquisa pela coluna Data_Entrada.

-- código #2.2
declare @Busca date;
set @Busca= convert(date, '20081010', 112);

SELECT ID, Data_Entrada
  from Teste_Implicit
  where Data_Entrada = @Busca;

No código anterior é realizada pesquisa pela coluna Data_Entrada, que está declarada como char(8). A variável de pesquisa, @Busca, está declarada como date. Temos então tipos de dados divergentes, o que fará com que ocorra conversão implícita em um deles; mas qual? Usando a tabela de precedência de tipos de dados você consegue dizer qual dos dois será convertido?

Como há índice de cobertura pela coluna Data_Entrada, a expectativa é que ocorra busca rápida (seek) na tabela. O plano de execução nos mostra o seguinte:

p010_codigo #2.2

Não ocorreu busca rápida (seek)! O motivo você já deve saber… Como o tipo de dados date tem precedência sobre o tipo de dados char(8), ocorreu conversão implícita no valor da coluna Data_Entrada, que foi convertida para date. É como se a cláusula WHERE do código #2.1 tivesse sido codificada assim:

where convert(date, Data_Entrada, 112) = @Busca;

E, como já sabemos do artigo “Construindo códigos T-SQL eficientes: Sargability”, isto torna o predicado non sargable. Está então explicado o Index Scan.

2.2.2. Caso 2

Vamos agora a outro teste, mas com a variável @Busca declarada como char(8); isto é, o mesmo tipo de dados da coluna Data_Entrada.

-- código #2.3
declare @Busca char(8);
set @Busca= '20081010';

SELECT ID, Data_Entrada
  from Teste_Implicit
  where Data_Entrada = @Busca;

O plano de execução do código anterior nos mostra

p010_codigo #2.3

Temos uma busca rápida (seek) e sem qualquer conversão implícita.

2.2.3. Caso 3

Vamos a outro caso, mas agora utilizando pesquisa pela coluna Data_Saída, que está declarada como date.

-- código #2.4
declare @Busca date;
set @Busca= convert(date, '20081010', 112);

SELECT ID, Data_Saída
  from Teste_Implicit
  where Data_Saída = @Busca;

Como no caso 1, a variável de busca está declarada como date. Ou seja, tanto coluna como valor têm o mesmo tipo de dados, o que significa que não ocorrerá conversão implícita.

p010_codigo #2.4

2.2.4. Caso 4

Ainda usando pesquisa pela coluna Data_Saída, mas agora com a variável @Busca declarada como char(8). Ou seja, temos mais um caso de tipos de dados diferentes, o que significa que uma conversão implícita ocorrerá.

-- código #2.5
declare @Busca char(8);
set @Busca= '20081010';

SELECT ID, Data_Saída
  from Teste_Implicit
  where Data_Saída = @Busca;

A execução do código acima gerou o seguinte plano de execução:

p010_codigo #2.5

Que estranho, se ocorreu conversão implícita então como é que o predicado continuou sargable? A explicação é que pelo fato do tipo de dados date ter precedência sobre o tipo de dados char(8), ocorreu conversão implícita na variável @Busca, que foi convertida para date. Algo como

where Data_Saída = convert(date, @Busca, 112);

E isto não torna o código non sargable, pois o conteúdo da coluna não foi modificado.

2.3. Efeitos na cláusula FROM

Aguarde a segunda parte deste artigo.

2.4. Detectando conversões implícitas

As técnicas para detectar conversões implícitas variam de acordo com a versão do SQL Server, sendo que a partir da versão 2012 existe evento específico que pode ser monitorado para alertar da ocorrência de conversão implícita.

2.4.1. Evento estendido plan_affecting_convert

Na versão 2012 do SQL Server foi adicionado evento estendido (extended event) que informa quando é executado plano de execução contendo conversão que força leitura sequencial completa (scan) em vez de busca direta (seek), denominado de “sqlserver.plan_affecting_convert”.

Para demonstração vamos criar a seguinte sessão de evento:

-- código #2.6
CREATE EVENT SESSION [Conversão implícita]
    ON SERVER
    ADD EVENT sqlserver.plan_affecting_convert
       (ACTION (sqlserver.sql_text)
        WHERE (sqlserver.equal_i_sql_unicode_string
                  (sqlserver.database_name, N'testdb')
               and [convert_issue]=(2)
              )
       )
    ADD TARGET package0.event_file
       (SET filename = N'C:\temp\convimp.xel');

A tabela Teste_Implicit foi criada no banco de dados testdb. As capturas da sessão serão armazenadas no arquivo convimp.xel, na pasta C:\temp\. Esse código foi adaptado do artigo “Using Extended Events to Capture Implicit Conversions”; vide capítulo Referências, ao final.

Para demonstração deste método temos o código abaixo, que nada mais é do que a ativação da sessão de evento, execução do código #2.2 e o encerramento da sessão de evento.

-- código #2.7
ALTER EVENT SESSION [Conversão implícita]
      ON SERVER state=start;

declare @Busca date;
set @Busca= convert(date, '20081010', 112);

SELECT ID, Data_Entrada
  from Teste_Implicit
  where Data_Entrada = @Busca;

ALTER EVENT SESSION [Conversão implícita]
      ON SERVER state=stop;

Após, basta analisar o conteúdo do arquivo convimp.xel, onde temos o seguinte resultado:

p010_codigo #2.7

Na linha expression observa-se trecho do plano de execução contendo a conversão implícita na coluna Data_Entrada.

Simples e prático!

2.4.2. Cache de planos de execução

Para versão anterior a 2012 do SQL Server a forma de se detectar conversões implícitas é através da análise dos planos de execução que estão no cache. No artigo “Querying the plan cache for specific implicit conversions” (vide capítulo Referências, ao final) é disponibilizado código que analisa o cache dos planos de execução, à procura que conversões implícitas. Esse código está no apêndice.

Para testar, primeiro foi limpo o cache de planos de execução, usando

DBCC FREEPROCCACHE;

A seguir foi executado o código #2.2 e em imediato o código #A.2. O resultado foi o seguinte:

p010_codigo #A.2

Na coluna expression observa-se trecho do plano de execução contendo a conversão implícita na coluna Data_Entrada.

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

A. Apêndice

A.1. Função que gera sequência numérica

O código a seguir é de autoria de Itzik Ben-Gan.

-- código #A.1
DROP FUNCTION IF EXISTS dbo.GetNums;
GO

-- Helper function dbo.GetNums
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L5)
SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum;
GO

A.2. Código que procura por conversões implícitas

O código a seguir foi obtido em artigo do Blog do Ezequiel. Vide capítulo Referências.

-- código #A.2
-- Querying the plan cache for specific implicit conversions
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
Convertsearch AS (SELECT qp.query_plan, cp.usecounts, cp.objtype, cp.plan_handle, cs.query('.') AS StmtSimple
FROM sys.dm_exec_cached_plans cp (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS p(cs)
WHERE cp.cacheobjtype = 'Compiled Plan'
AND cs.exist('@QueryHash') = 1
AND cs.exist('.//ScalarOperator[contains(@ScalarString, "CONVERT_IMPLICIT")]') = 1
AND cs.exist('.[contains(@StatementText, "Convertsearch")]') = 0
)
SELECT c2.value('@StatementText', 'VARCHAR(4000)') AS sql_text,
c2.value('@StatementId', 'int') AS StatementId,
c3.value('@ScalarString[1]','VARCHAR(4000)') AS expression,
ss.usecounts,
ss.query_plan,
StmtSimple.value('StmtSimple[1]/@QueryHash', 'VARCHAR(100)') AS query_hash,
StmtSimple.value('StmtSimple[1]/@QueryPlanHash', 'VARCHAR(100)') AS query_plan_hash,
StmtSimple.value('StmtSimple[1]/@StatementSubTreeCost', 'sysname') AS StatementSubTreeCost,
c2.value('@EstimatedTotalSubtreeCost','sysname') AS EstimatedTotalSubtreeCost,
StmtSimple.value('StmtSimple[1]/@StatementOptmEarlyAbortReason', 'sysname') AS StatementOptmEarlyAbortReason,
StmtSimple.value('StmtSimple[1]/@StatementOptmLevel', 'sysname') AS StatementOptmLevel,
ss.plan_handle
FROM Convertsearch ss
CROSS APPLY query_plan.nodes('//StmtSimple') AS q2(c2)
CROSS APPLY c2.nodes('.//ScalarOperator[contains(@ScalarString, "CONVERT_IMPLICIT")]') AS q3(c3)
OPTION(RECOMPILE, MAXDOP 1);
GO

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

Os perigos da conversão implícita

Deixe um comentário

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

Logotipo do WordPress.com

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

Foto do Google+

Você está comentando utilizando sua conta Google+. 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