Função Escalar x Table Valued Function

Muito se fala na internet sobre como as funções escalares são terríveis e que devemos evitar o uso a todo o custo… Eu gostaria de mostrar o porquê:

Vejam o seguinte script abaixo (um procedimento muito utilizado por aí: a utilização de uma função escalar que trata “N” casos e retorna o valor desejado). Primeiro, a criação da tabela e inserção dos dados:

CREATE TABLE NotaFiscal (Cod int identity primary key, Pessoa varchar(20), Valor decimal (15,2), Data date)
CREATE INDEX Ncl_NotaFiscal ON NotaFiscal (Data) INCLUDE (Pessoa, Valor)

GO

-- Inserindo 10 notas no período de 500 dias (5000 notas).
INSERT INTO NotaFiscal
SELECT 'Logan', 100, DATEADD(DD, Number, '2012-12-31')
from Numbers
WHERE Number < 500
GO 10

GO

CREATE FUNCTION CalculaTributos (@Codigo int, @Tipo char(1))
Returns decimal(15,2)
AS
BEGIN
  DECLARE @valor decimal(15,2)
  SELECT @Valor =
    CASE @tipo
      WHEN 'a' then valor * 0.1
      WHEN 'b' then valor * 0.3
      WHEN 'c' then valor * 0.5 
    END 
  FROM NotaFiscal 
  RETURN @valor 

END;

CREATE TABLE NotaFiscal (Cod int identity primary key, Pessoa varchar(20), Valor decimal (15,2), Data date)

CREATE INDEX Ncl_NotaFiscal ON NotaFiscal (Data) INCLUDE (Pessoa, Valor)

-- Inserindo 10 notas no período de 500 dias (5000 notas).

INSERT INTO NotaFiscal

SELECT 'Logan', 100, DATEADD(DD, Number, '2012-12-31')

from Numbers

WHERE Number < 500

GO 10

CREATE FUNCTION CalculaTributos (@Codigo int, @Tipo char(1))

Returns decimal(15,2)

BEGIN

DECLARE @valor decimal(15,2)

SELECT @Valor =

CASE @tipo

WHEN 'a' then valor * 0.1

WHEN 'b' then valor * 0.3

WHEN 'c' then valor * 0.5

END

FROM NotaFiscal

RETURN @valor

END;

Ao executar uma consulta para retornar as notas fiscais no período de um mês utilizando a função escalar, temos o seguinte resultado:

SELECT 
  Cod,
  Valor,
  dbo.CalculaTributos(Cod,'a'),
  dbo.CalculaTributos(Cod,'b'),
  dbo.CalculaTributos(Cod,'c')
FROM NotaFiscal
WHERE Data BETWEEN '2013-01-01' AND '2013-02-01'

(320 row(s) affected)
Table 'notafiscal'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1997 ms,  elapsed time = 2050 ms.

SELECT

Cod,

Valor,

dbo.CalculaTributos(Cod,'a'),

dbo.CalculaTributos(Cod,'b'),

dbo.CalculaTributos(Cod,'c')

FROM NotaFiscal

WHERE Data BETWEEN '2013-01-01' AND '2013-02-01'

(320 row(s) affected)

Table 'notafiscal'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1997 ms, elapsed time = 2050 ms.

2 segundos para 320 registros… Pouco, você pode imaginar… Mas pense que isto é para uma função, sem nenhuma regra absurda (apenas um case simples e direto) e na minha máquina, onde somente a minha conexão está aberta. Imaginem isso em um servidor com uma alta carga e concorrência.

Se substituirmos essa função por uma função que retorne uma tabela, teremos o seguinte resultado:

CREATE FUNCTION CalculaTributosTbl (@Codigo INT)
Returns Table
AS
RETURN
(
  SELECT Cod as Codigo, 
         valor * 0.1 as ValorA, 
         valor * 0.3 as ValorB, 
         valor * 0.5 as ValorC
  FROM NotaFiscal
  WHERE Cod = @Codigo
);

SELECT 
  Cod,
  Valor,
  ValorA,
  ValorB,
  ValorC
from notafiscal
cross apply dbo.calculatributosTbl(cod)
where data between '2013-01-01' and '2013-02-01'

(320 row(s) affected)
Table 'notafiscal'. Scan count 2, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

CREATE FUNCTION CalculaTributosTbl (@Codigo INT)

Returns Table

RETURN

(

SELECT Cod as Codigo,

valor * 0.1 as ValorA,

valor * 0.3 as ValorB,

valor * 0.5 as ValorC

FROM NotaFiscal

WHERE Cod = @Codigo

);

SELECT

Cod,

Valor,

ValorA,

ValorB,

ValorC

from notafiscal

cross apply dbo.calculatributosTbl(cod)

where data between '2013-01-01' and '2013-02-01'

(320 row(s) affected)

Table 'notafiscal'. Scan count 2, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 2 ms.

Notaram a diferença? Conseguimos reduzir a consulta de 2 segundos para 2 milissegundos, um ganho realmente considerável.

O que eu quero dizer aqui: Sempre que possível, evitem o uso de funções escalares. Vejam se uma TVP pode ser utilizada e, caso contrário, avaliem tratar o dado na aplicação e não no banco, pois o ganho de desempenho pode ser realmente significativo.

Se tiverem algo a agregar e/ou corrigir, por favor, faça… A ideia aqui é passar a informação da melhor forma e mais correta possível.

PS: A tabela Numbers que eu utilizei para a criação dos dados é a versão do Adam Machanic, que pode ser encontrada aqui: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx

[]’s!

Relacionado

3 comentários

Renato Siqueira disse:

março 7, 2014 às 10:34 pm

Explicação muito fácil de entender, simples e direto.
Parabéns pelo artigo!

Carregando...

Responder
1. Logan Merazzi disse:
  
  março 12, 2014 às 8:07 am
  
  Opa! Obrigado pelas palavras Renato! 🙂
  
  Carregando...
  
  Responder
Rogério Ferreira disse:

janeiro 27, 2015 às 10:17 am

muito legal, testei aqui e realmente devemos tomar muito cuidado.

Carregando...

Responder