Performance Function CLR x Function TSQL

Fala galera, mais uma dica rápida, hoje vamos demonstrar um teste de performance comparando funções criadas no SQL Server com Transact SQL e funções criadas em CLR C#.

Temos que ter em mente que cada uma das linguagens tem suas características, onde o TSQL é uma linguagem interpretada e otimizada para a lógica Set-based e acesso a dados, já CLR é uma linguagem procedural.

A ideia não é falar qual é a melhor tecnologia a ser utilizada, cada uma tem um lado positivo e negativo, então se a pergunta for qual é a melhor opção para ser utilizada, a resposta vai ser ‘Depende’.

O teste que será mostrado foi reproduzido em um SQL Server 2016 Developer Edition, processador i7-6500 2.5 GHz e 16 GB de RAM.

Como mencionado acima o TSQL é uma linguagem otimizada para acesso a dados, então quando precisamos realizar operações como por exemplo regex , cálculos e manipulações de texto esses são casos onde o TSQL não se sai tão bem quanto o CLR.

Para nosso teste vamos criar uma função que retorna o valor ‘1’ se o número for par e ‘0’ se o numero for ímpar.

IF DB_ID(‘DB_POWER’) IS NOT NULL
BEGIN
ALTER DATABASE DB_POWER SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE DB_POWER
END
GO

CREATE DATABASE DB_POWER

GO

USE DB_POWER

GO

CREATE TABLE TBL_INFO (ID INT IDENTITY(1,1),
TEXTO1 UNIQUEIDENTIFIER DEFAULT(NEWID()),
TEXTO2 UNIQUEIDENTIFIER DEFAULT(NEWID()),
TEXTO3 UNIQUEIDENTIFIER DEFAULT(NEWID()),
TEXTO4 UNIQUEIDENTIFIER DEFAULT(NEWID()),
TEXTO5 UNIQUEIDENTIFIER DEFAULT(NEWID()),
TEXTO6 UNIQUEIDENTIFIER DEFAULT(NEWID())
)

GO
CREATE INDEX IDX1_TBL_INFO ON TBL_INFO(ID) INCLUDE(TEXTO1) WITH (DATA_COMPRESSION = PAGE)
INSERT INTO TBL_INFO DEFAULT VALUES
GO 7000000

 

IF(OBJECT_ID(‘CFN_CONCAT_REVERSE’)) IS NOT NULL
DROP FUNCTION FN_CONCAT_REVERSE
GO

CREATE FUNCTION FN_IMPAR_PAR (@VALOR INT)
RETURNS INT
AS
BEGIN
RETURN (CASE WHEN @VALOR % 2 = 0 THEN 1 ELSE 0 END)
END

Preparando nosso ambiente de teste, inserindo uma grande massa de dados e depois criando a função em TSQL.

 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 SqlFunction1(SqlInt32 number)
{
// Put your code here
return new SqlInt32((number % 2 == 0)?1:0);
}

Código bem simples em C# para criação da nossa função CLR, compilando esse código no Visual Studio será gerado uma .DLL que iremos importar para dentro do SQL Server.

 

CREATE ASSEMBLY ASSENBLY_IMPARPAR from ‘c:\FN_IMPAR_PAR_CLR.dll’ WITH PERMISSION_SET = UNSAFE

GO

CREATE FUNCTION FN_IMPAR_PAR_CLR (@VALOR INT)
RETURNS INT
AS
EXTERNAL NAME ASSENBLY_IMPARPAR.UserDefinedFunctions.SqlFunction1

Criando nossa função CLR no SQL Server.

 

Testando a performance.

SELECT ID FROM TBL_INFO WHERE dbo.FN_IMPAR_PAR(ID) = 1

GO

SELECT ID FROM TBL_INFO WHERE dbo.FN_IMPAR_PAR_CLR(ID) = 1

performance

(3800000 row(s) affected)
Table ‘TBL_INFO’. Scan count 1, logical reads 35273, 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 = 18578 ms, elapsed time = 22122 ms.
(3800000 row(s) affected)
Table ‘TBL_INFO’. Scan count 5, logical reads 35603, 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 = 6439 ms, elapsed time = 14960 ms.

Podemos ver o número de leituras bem próximas, porém a função em CLR executou bem mais rápido e consumiu bem menos recurso de CPU do que a function em TSQL.

É isso ai pessoal, tenha em mente que em CLR você tem um grande poder de programação, mas lembre-se que quando falamos de banco de dados sempre que possível temos que focar nosso pensamento em Set-based onde nosso TSQL é fortíssimo, sabendo conciliar a utilização dessas duas linguagens e saber quando uma linguagem pode se sair melhor que a outra, você pode ter ganhos de performance consideráveis em seu ambiente.

Caso queira ver testes mais detalhados de performance deixarei os links na referência.

Realize seu teste e deixe seu feedback. Obrigado e até a próxima.

Referência:

Performance of CLR Integration

https://msdn.microsoft.com/en-us/library/ms131075.aspx

CLR performance testing

https://www.simple-talk.com/sql/t-sql-programming/clr-performance-testing/

CLR vs TSQL: performance considerations

http://aboutsqlserver.com/2013/07/22/clr-vs-t-sql-performance-considerations/

Pensando Set-based

https://www.youtube.com/watch?v=tgF9TeCcdkc

 

Reginaldo Silva

 

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s