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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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!
Explicação muito fácil de entender, simples e direto.
Parabéns pelo artigo!
Opa! Obrigado pelas palavras Renato! 🙂
muito legal, testei aqui e realmente devemos tomar muito cuidado.