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;

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.

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.

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!