Blog – Fabiano Neves Amorim

Scalar functions são bastante utilizadas em banco de dados, mas elas são verdadeiras killers de CPU…

 

Estou trabalhando na manutenção de um projeto ETL para BI e me deparei com um desses casos onde vale a pena escrever…

 

Tenho uma consulta que retorna aproximadamente 800 mil linhas o que é bastante comum quando estamos falando de bases DW, mas o interessante aqui é que o select que retorna os dados para criação da minha dimensão usa uma function para gerar o Ano, Mês e Dia a partir de uma Data existente na tabela. Depois de converter a data para Ano usando a function YEAR do SQL eu pego o valor de Ano e jogo em uma outra tabela na coluna Ano, e depois uso ela na dimensão de tempo no meu cubo Analysis Services.

 

Bom vou resumir para ver se da pra ficar mais fácil de entender, eu tenho uma Data e preciso do Ano, Mês e Dia desta data em colunas separadas para poder efetuar os filtros por um determinado período no Cubo.

 

Ai é que ta, na verdade eu nem precisava fazer a conversão no SQL para jogar no AS, mas eu posso simplesmente criar uma Named Calculation na tabela do DataSource View no AS, antes de mostrar isso, vamos ver porque Scalar Functions são bad.

 

Vamos criar uma base para os testes,

 

use tempdb

SET NOCOUNT ON

IF OBJECT_ID(‘TabTeste’) IS NOT NULL

  DROP TABLE TabTeste

GO

CREATE TABLE TabTeste (ID Int Identity(1,1),

                       Nome VarChar(200) DEFAULT NEWID(),

                       Data DateTime DEFAULT GETDATE())

GO

— Vamos inserir um registro para simular o problema

INSERT INTO TabTeste(Nome, Data) VALUES(‘Teste’, ‘20050101’)

GO

— Massa aleatoria de dados.

INSERT INTO TabTeste DEFAULT VALUES

GO 1000000

 

Agora vamos criar uma Scalar Function para usar nos testes.

 

IF OBJECT_ID(‘fn_PreencheComZeros’) IS NOT NULL

  DROP FUNCTION dbo.fn_PreencheComZeros

GO

 

CREATE FUNCTION dbo.fn_PreencheComZeros(@Coluna VarChar(250), @Qtde_Zeros Int)

RETURNS Varchar(200)

AS

BEGIN

  DECLARE @Result VarChar(200)

  SET @Result = RIGHT(REPLICATE(‘0’, @Qtde_Zeros) + @Coluna, @Qtde_Zeros)

 

  RETURN @Result;

END

GO

 

Agora um exemplo simples de um select para retornar os dados que eu preciso.

 

— Retorna os dados com da tabela criando 3 colunas, Ano, Mes e Dia.

SELECT YEAR(Data)  AS Ano,

       MONTH(Data) AS Mes,

       DAY(Data)   AS Dia,

       *

  FROM TabTeste

— Rodou em 31 segundos e usou 1109 ms de CPU

 

/* Exemplo do resultado

Ano   Mes  Dia  ID   Nome                                  Data

2005  1       1    1   Teste                                 2005-01-01 00:00:00.000

2008  10     21    2   E09D7055-CC12-40FC-8D3E-F7A28521D601  2008-10-21 11:35:46.087*/

 

Repare que na data de 01/01/2005 o Mês veio como "1" e não "01" a mesma coisa acontece com o dia, eu sei que existem outras formas de fazer retornar o "01" mas vamos dizer que eu me depare com uma solução que usa a função para formatar os valores para retornar conforme o desejado. É bem comum usar funções deste tipo para fazer diversas formatações desde concatenações até cálculos super complexos.

 

— Com a simples função de Preencher com Zero o SQL ficaria assim:

SELECT dbo.fn_PreencheComZeros(YEAR(Data),4)  AS Ano,

       dbo.fn_PreencheComZeros(MONTH(Data),2) AS Mes,

       dbo.fn_PreencheComZeros(DAY(Data),2)   AS Dia,

       *

  FROM TabTeste

— Rodou em 35 segundos e usou 22438 ms de CPU

 

/* Exemplo do resultado

Ano     Mes Dia  ID   Nome                                  Data

2005  01   01   1        Teste                                     2005-01-01 00:00:00.000

2008  10     21    2     E09D7055-CC12-40FC-8D3E-F7A28521D601   2008-10-21 11:35:46.087*/

 

Agora imagine que uma mente Brilhante resolva sofisticar a função e usa-lá para diversas formatações deixando o código dentro dela um pouco maior…

 

— Conforme mais código na function pior fica a coisa…

SELECT DBO.fn_PreencheComZerosSuper(YEAR(Data),4,0)  AS Ano,

       DBO.fn_PreencheComZerosSuper(MONTH(Data),2,0) AS Mes,

       DBO.fn_PreencheComZerosSuper(DAY(Data),2,0)   AS Dia,

       *

  FROM TabTeste

— Rodou em 1:15 e usou 73515 ms de CPU, ou seja o dobro do tempo inicial.

 

Mas o que fazer neste caso?

Bom a resposta é, evite usar Functions de qualquer tipo que seja em colunas, caso enha que fazer alguma formatação tente formatar o código direto na coluna por ex:

 

— Exemplo de uma solução

SELECT RIGHT(‘0000’ + Convert(VarChar(4),YEAR(Data)),4) AS Ano,

       RIGHT(’00’ + Convert(VarChar(2), MONTH(Data)),2)  AS Mes,

       RIGHT(’00’ + Convert(VarChar(2), DAY(Data)),2)    AS Dia,

       *

  FROM TabTeste

— Rodou em 33 segundos e usou 1984 ms de CPU

 

Named Calculation são colunas calculadas que você pode criar no seu DataSource View sem que você tenha que alterar a estrutura de suas tabelas, ou seja, não precisa gastar tempo para popular e espaço para armazenar os dados sendo que você pode criar uma Named Calculation que irá retornar o valor que você deseja.

 

No meu exemplo acima, optei por ao invés de ter que calcular e gravar os dados de Ano, Mês e Dia em uma tabela e depois ler estes valores, achei melhor criar 3 novas Named Calculation direto no AS, segue um print das telas para ficar mais claro.

 

 

 

 

Depois de clicar no OK a coluna ficará disponível como todas as outras mas com um ícone() diferente.

 

That’s all folks…