Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function

Esse post é a parte 5 de 10 da série Performance Tuning
Tempo de Leitura: 13 minutos

Olá pessoal,
Tudo bem com vocês ??

Neste post, que será o número 200 do blog, eu gostaria de falar sobre dois assuntos que eu particularmente gosto muito no SQL Server, que é a criação de rotinas .NET (CLR) dentro do banco de dados SQL Server e performance tuning.

Coincidência ou não, meu post de número 100 foi o Introdução ao SQL CLR (Common Language Runtime) no SQL Server, que foi o meu primeiro post no blog sobre o CLR.

Meu intuito nesse post, é demonstrar o ganho de performance que pode ser obtido ao utilizar Scalar functions do CLR ao invés da Scalar function escrita em T-SQL (UDF – User Defined Function). Nos testes realizados, a grande maioria das funções T-SQL que foram migradas para funções CLR tiverem um grande ganho de performance simplesmente por conta das otimizações do Microsoft .NET Framework frente ao motor do SQL Server.

Em muitas situações, vemos que os índices estão criados corretamente, estatísticas atualizadas e, sem precisar reescrever a query, pode-se conseguir um grande ganho de performance simplesmente alterando a linguagem de programação de funções escalares.

Função no CLR sempre vai ser melhor?

Há quem possa ter algumas dúvidas sobre a performance de funções escalares do CLR, as quais vou esclarecer:

– As funções escalares do CLR sempre vão ter uma performance melhor que funções ?
Com certeza não! Já fiz vários testes comparando diversas funções, de diversas finalidades diferentes e pela minha experiência, as funções scalares escritas em C# no CLR, GERALMENTE apresentam sim, uma melhor performance, mas já vi alguns casos em que mesmo otimizando o código ao máximo, a função T-SQL apresenta um desempenho melhor.

– E se compararmos a performance de funções nativas com as funções do CLR ?
Funções, de uma maneira geral, sempre prejudicam a performance se uma consulta ao banco de dados. Entretanto, por mais que as funções escalares do CLR sejam bem performáticas, GERALMENTE as funções nativas do SQL Server (Ex: CONVERT, CAST, DATEADD, etc) apresentam uma melhor performance quando comparadas.

Comparando a performance na prática

Depois de fazer essa introdução sobre o assunto, é hora de realmente demonstrar o que foi falado aqui. E nada melhor que realizar os testes de performance na prática para convencer você, de que o CLR pode, em muitos casos, prover um grande ganho de performance nas suas consultas ao SQL Server.

Para gerar a massa de dados de testes, utilizei esse script:

O código-fonte da função fncRand(), bem como a explicação de porque utilizo essa função ao invés da RAND(), você encontra no post SQL Server – Msg 443 Invalid use of a side-effecting operator ‘rand’ within a function.

fncPrimeiroDiaMes

Visualizar conteúdo
Código-fonte T-SQL:

Código-fonte CLR:

Saída:

Comparação de performance no SELECT

Comparação de performance no WHERE

fncUltimoDiaMes

Visualizar conteúdo
Código-fonte T-SQL:

Código-fonte CLR:

Saída:

Comparação de performance no SELECT

Comparação de performance no WHERE

fncSplit

Visualizar conteúdo
Código-fonte T-SQL:

Código-fonte CLR:

Saída:

Comparação de performance no SELECT
Reparem que, após esperar 5 mins, acabei cancelando e iniciando os testes novamente, mas com apenas 10.000 registros para a função T-SQL, comparando com a função do CLR rodando em 100.000 registros. Mas mesmo a função do CLR sendo executada 10x mais, vejam o que aconteceu nos resultados…

Comparação de performance no WHERE

fncBase64_Encode

Visualizar conteúdo
Código-fonte T-SQL:

Código-fonte CLR:

Saída:

Comparação de performance no SELECT

fncBase64_Decode

Visualizar conteúdo
Código-fonte T-SQL:

Código-fonte CLR:

Saída:

Comparação de performance no SELECT

fncValida_CPF

Visualizar conteúdo
Código-fonte T-SQL:

Código-fonte CLR:

Saída:

Comparação de performance no SELECT

fncRecupera_Numeros

Visualizar conteúdo
Código-fonte T-SQL:

Código-fonte CLR:

Saída:

Comparação de performance no SELECT

fncMes

Visualizar conteúdo
Código-fonte T-SQL:

Código-fonte CLR:

Saída:

Comparação de performance no SELECT

fncConverte_Em_Horas

Visualizar conteúdo
Código-fonte T-SQL:

Código-fonte CLR:

Saída:

Comparação de performance no SELECT

fncFormata_Documento

Visualizar conteúdo
Código-fonte T-SQL: