Hola, chicos,
¿Cómo están chicos?

En esta publicación, que será la número 200 del blog, me gustaría hablar sobre dos temas que me gustan particularmente en SQL Server, que es la creación de rutinas .NET (CLR) dentro de la base de datos de SQL Server y el ajuste del rendimiento.

Coincidencia o no, mi publicación número 100 fue Introducción a SQL CLR (Common Language Runtime) en SQL Server, que fue mi primera publicación de blog sobre CLR.

Mi intención en esta publicación es demostrar la ganancia de rendimiento que se puede obtener al usar funciones escalares de CLR en lugar de la función escalar escrita en T-SQL (UDF – Función definida por el usuario). En las pruebas realizadas, la gran mayoría de las funciones T-SQL que se migraron a funciones CLR tuvieron una gran ganancia de rendimiento simplemente debido a las optimizaciones de Microsoft .NET Framework en comparación con el motor SQL Server.

En muchas situaciones, vemos que los índices se crean correctamente, las estadísticas se actualizan y, sin tener que reescribir la consulta, se puede lograr una gran ganancia de rendimiento simplemente cambiando el lenguaje de programación de la función escalar.

¿La función en CLR siempre será mejor?

Hay quienes pueden tener algunas dudas sobre el desempeño de las funciones escalares CLR, las cuales aclaro:

– ¿Las funciones escalares CLR siempre funcionarán mejor que las funciones?
¡Definitivamente no! Ya he hecho varias pruebas comparando diferentes funciones, para diferentes propósitos y en mi experiencia, las funciones escalares escritas en C# en el CLR, GENERALMENTE presentan mejor rendimiento, pero he visto algunos casos en los que incluso optimizando el código al máximo, la función T-SQL presenta mejor rendimiento.

– ¿Qué pasa si comparamos el rendimiento de las funciones nativas con las funciones CLR?
Las funciones, en general, siempre perjudican el rendimiento al consultar la base de datos. Sin embargo, aunque las funciones escalares de CLR son muy eficaces, GENERALMENTE las funciones nativas de SQL Server (por ejemplo, CONVERTIR, CAST, DATEADD, etc.) presentan un mejor rendimiento en comparación.

Comparación del desempeño en la práctica

Después de hacer esta introducción al tema, es hora de demostrar realmente lo que aquí se dijo. Y nada mejor que realizar pruebas de rendimiento en la práctica para convencerse de que CLR puede, en muchos casos, proporcionar una enorme ganancia de rendimiento en sus consultas de SQL Server.

Para generar la masa de datos de prueba, utilicé este script:

IF (OBJECT_ID('dirceuresende.dbo.Teste_Performance') IS NOT NULL) DROP TABLE dirceuresende.dbo.Teste_Performance
CREATE TABLE dirceuresende.dbo.Teste_Performance (
    Id INT IDENTITY(1, 1),
    Nome VARCHAR(200),
    Numero INT,
    [Data] DATETIME,
    Observacao VARCHAR(MAX),
    CPF VARCHAR(11)
)

INSERT INTO dirceuresende.dbo.Teste_Performance ( Nome, Numero, [Data], Observacao, CPF )
SELECT 
    CAST(NEWID() AS VARCHAR(50)),
    dbo.fncRand(99999999),
    DATEADD(SECOND, (CAST(RAND() * 31536000 AS INT)), '2017-01-01') AS Dt_Venda,
    CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)),
    RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF


DECLARE @Contador INT = 1, @Total INT = 20


WHILE(@Contador <= @Total)
BEGIN
 
    INSERT INTO dirceuresende.dbo.Teste_Performance ( Nome, Numero, [Data], Observacao, CPF )
    SELECT 
        CAST(NEWID() AS VARCHAR(50)),
        dbo.fncRand(99999999),
        DATEADD(SECOND, (CAST(RAND() * 31536000 AS INT)), '2017-01-01') AS Dt_Venda,
        CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)),
        RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF
    FROM
        dirceuresende.dbo.Teste_Performance


    SET @Contador += 1
 

END

El código fuente de la función fncRand(), así como la explicación de por qué uso esta función en lugar de RAND(), se puede encontrar en la publicación SQL Server: mensaje 443 Uso no válido de un operador de efectos secundarios "rand" dentro de una función.

fncPrimerDíaMes

Ver contenido
Código fuente T-SQL:
CREATE FUNCTION [dbo].[fncPrimeiroDiaMes](@Dt_Referencia DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(DAY,-(DAY(@Dt_Referencia)-1), CAST(FLOOR(CAST(@Dt_Referencia AS FLOAT)) AS DATETIME))
END

Código fuente CLR:

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime fncPrimeiro_Dia_Mes(SqlDateTime Dt_Referencia)
    {
        if (Dt_Referencia.IsNull)
            return SqlDateTime.Null;

        var data = Dt_Referencia.Value;
        return new DateTime(data.Year, data.Month, 1);
    }
}

Salida:

Comparación de rendimiento en SELECT

Comparación de rendimiento en DONDE


fncÚltimoDíaMes

Ver contenido
Código fuente T-SQL: 
create function [dbo].[fncUltimoDiaMes](@Dt_Referencia datetime)
RETURNS datetime
as
begin
    return dateadd(day,-1,dateadd(month,+1,DATEADD(DAY,-(DAY(@Dt_Referencia)-1), CAST(FLOOR(CAST(@Dt_Referencia AS FLOAT)) AS DATETIME))))
end

Código fuente CLR:

using System;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime fncUltimo_Dia_Mes(DateTime data)
    {
        return new DateTime(data.Year, data.Month, DateTime.DaysInMonth(data.Year, data.Month));
    }
}

Salida:

Comparación de rendimiento en SELECT

Comparación de rendimiento en DONDE


fncDividir

Ver contenido
Código fuente T-SQL: 
CREATE FUNCTION [dbo].[fncSplit] ( @String varchar(8000), @Separador varchar(8000), @PosBusca int )
RETURNS varchar(8000)
AS BEGIN
    
    DECLARE @Index int, @Max int, @Retorno varchar(8000)

    DECLARE @Partes as TABLE ( Id_Parte int identity(1,1), Texto varchar(8000) )

    SET @Index = charIndex(@Separador,@String)

    WHILE (@Index > 0) BEGIN	
        INSERT INTO @Partes SELECT SubString(@String,1,@Index-1)
        SET @String = Rtrim(Ltrim(SubString(@String,@Index+Len(@Separador),Len(@String))))
        SET @Index = charIndex(@Separador,@String)
    END

    IF (@String != '') INSERT INTO @Partes SELECT @String

    SELECT @Max = Count(*) FROM @Partes

    IF (@PosBusca = 0) SET @Retorno = Cast(@Max as varchar(5))
    IF (@PosBusca < 0) SET @PosBusca = @Max + 1 + @PosBusca
    IF (@PosBusca > 0) SELECT @Retorno = Texto FROM @Partes WHERE Id_Parte = @PosBusca

    RETURN Rtrim(Ltrim(@Retorno))

END

Código fuente CLR:

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncSplit(SqlString Ds_Palavra, SqlString Ds_Delimitador, SqlInt32 Qt_Indice)
    {

        if (Ds_Palavra.IsNull || Qt_Indice.IsNull)
            return SqlString.Null;

        if (Ds_Delimitador.IsNull)
            return Ds_Palavra.Value;

        var indice = (Qt_Indice.Value == 0) ? 1 : Qt_Indice.Value;
        var palavra = Ds_Palavra.Value.Split(Ds_Delimitador.Value.ToCharArray());

        if (Qt_Indice.Value == 0)
            return palavra.Length.ToString();

        if (palavra.Length < Qt_Indice.Value)
            return SqlString.Null;

        return indice > 0 ? palavra[indice - 1] : palavra[palavra.Length - Math.Abs(indice)];

    }
}

Salida:

Comparación de rendimiento en SELECT
Observe que, después de esperar 5 minutos, terminé cancelando e iniciando las pruebas nuevamente, pero con solo 10,000 registros para la función T-SQL, en comparación con la función CLR ejecutándose en 100,000 registros. Pero a pesar de que la función CLR se realizó 10 veces más, mira lo que sucedió en los resultados...

Comparación de rendimiento en DONDE

fncBase64_Encode

Ver contenido
Código fuente T-SQL:
CREATE FUNCTION [dbo].[fncBase64_Encode] (
    @string VARCHAR(MAX)
) 
RETURNS VARCHAR(MAX)
AS BEGIN

    DECLARE 
        @source VARBINARY(MAX), 
        @encoded VARCHAR(MAX)
        
    set @source = convert(varbinary(max), @string)
    SET @encoded = CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')

    RETURN @encoded

END

Código fuente CLR:

using System;
using System.Data.SqlTypes;
using System.Text;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncBase64_Encode(SqlString Ds_Texto)
    {
        return Ds_Texto.IsNull ? null : Convert.ToBase64String(Encoding.UTF8.GetBytes(Ds_Texto.Value));
    }
}

Salida:

Comparación de rendimiento en SELECT

fncBase64_Decode

Ver contenido
Código fuente T-SQL:
CREATE FUNCTION [dbo].[fncBase64_Decode] (
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS BEGIN

    DECLARE @decoded VARCHAR(MAX)
    SET @decoded = CAST('' AS XML).value('xs:base64Binary(sql:variable("@string"))', 'varbinary(max)')

    RETURN convert(varchar(max), @decoded)
    
END

Código fuente CLR:

using System;
using System.Data.SqlTypes;
using System.Text;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncBase64_Decode(SqlString Ds_Texto)
    {
        return Ds_Texto.IsNull ? null : Encoding.UTF8.GetString(Convert.FromBase64String(Ds_Texto.Value));
    }
}

Salida:

Comparación de rendimiento en SELECT


fncValida_CPF

Ver contenido
Código fuente T-SQL: 
CREATE FUNCTION [dbo].[fncValida_CPF](@Nr_Documento [varchar](11))
RETURNS [bit]
AS 
BEGIN

    
    IF (ISNUMERIC(@Nr_Documento) = 0)
        RETURN 0


    DECLARE
        @Contador_1 INT,
        @Contador_2 INT,
        @Digito_1 INT,
        @Digito_2 INT,
        @Nr_Documento_Aux VARCHAR(11)

    SET @Nr_Documento_Aux = LTRIM(RTRIM(@Nr_Documento))
    SET @Digito_1 = 0

    IF LEN(@Nr_Documento_Aux) <> 11
        RETURN 0
    ELSE
    BEGIN

        -- Cálculo do segundo dígito
        SET @Nr_Documento_Aux = SUBSTRING(@Nr_Documento_Aux, 1, 9)

        SET @Contador_1 = 2

        WHILE @Contador_1 <= 10
        BEGIN 
            SET @Digito_1 = @Digito_1 + ( @Contador_1 * CAST(SUBSTRING(@Nr_Documento_Aux, 11 - @Contador_1, 1) AS INT) )
            SET @Contador_1 = @Contador_1 + 1
        END 

        SET @Digito_1 = @Digito_1 - ( @Digito_1 / 11 ) * 11

        IF @Digito_1 <= 1
            SET @Digito_1 = 0
        ELSE
            SET @Digito_1 = 11 - @Digito_1

        SET @Nr_Documento_Aux = @Nr_Documento_Aux + CAST(@Digito_1 AS VARCHAR(1))

        IF @Nr_Documento_Aux <> SUBSTRING(@Nr_Documento, 1, 10)
            RETURN 0
        ELSE 
        BEGIN 

            -- Cálculo do segundo dígito
            SET @Digito_2 = 0
            SET @Contador_2 = 2

            WHILE (@Contador_2 <= 11)
            BEGIN 
                SET @Digito_2 = @Digito_2 + ( @Contador_2 * CAST(SUBSTRING(@Nr_Documento_Aux, 12 - @Contador_2, 1) AS INT) )
                SET @Contador_2 = @Contador_2 + 1
            END 

            SET @Digito_2 = @Digito_2 - ( @Digito_2 / 11 ) * 11


            IF @Digito_2 < 2
                SET @Digito_2 = 0
            ELSE
                SET @Digito_2 = 11 - @Digito_2
    

            SET @Nr_Documento_Aux = @Nr_Documento_Aux + CAST(@Digito_2 AS VARCHAR(1))


            IF @Nr_Documento_Aux <> @Nr_Documento
                RETURN 0

        END
                    
    END

    RETURN 1

END

Código fuente CLR:

using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean fncValida_CPF(SqlString Ds_CPF)
    {

        if (Ds_CPF.IsNull)
            return false;


        var valor = Ds_CPF.Value.Replace(".", "").Replace("-", "").Replace(" ", "");


        if (valor.Length != 11)
            return false;


        long resultadoConversaoInt;
        if (!long.TryParse(valor, out resultadoConversaoInt))
            return false;


        var igual = true;
        for (var i = 1; i < 11 && igual; i++)
            if (valor[i] != valor[0])
                igual = false;

        if (igual || valor == "12345678909") return false;


        var numeros = new int[11];
        for (var i = 0; i < 11; i++)
            numeros[i] = int.Parse(valor[i].ToString());


        var soma = 0;
        for (var i = 0; i < 9; i++)
            soma += (10 - i) * numeros[i];


        var resultado = soma % 11;
        if (resultado == 1 || resultado == 0)
        {
            if (numeros[9] != 0)
                return false;
        }
        else if (numeros[9] != 11 - resultado)
            return false;


        soma = 0;
        for (var i = 0; i < 10; i++)
            soma += (11 - i) * numeros[i];


        resultado = soma % 11;
        if (resultado == 1 || resultado == 0)
        {
            if (numeros[10] != 0)
                return false;
        }
        else if (numeros[10] != 11 - resultado)
            return false;


        return true;

    }

}

Salida:

Comparación de rendimiento en SELECT

fncRecupera_Numeros

Ver contenido
Código fuente T-SQL:
CREATE function [dbo].[fncRecupera_Numeros](@str varchar(500))  
returns varchar(500)  
begin  

    declare @startingIndex int  
    set @startingIndex=0  
    while 1=1  
    begin  
        set @startingIndex= patindex('%[^0-9]%',@str)  
        if @startingIndex <> 0  
        begin  
            set @str = replace(@str,substring(@str,@startingIndex,1),'')  
        end  
        else    break;   
    end  

    return NULLIF(@str, '')

end

Código fuente CLR:

using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncRecupera_Numeros(SqlString Ds_Texto)
    {


        if (Ds_Texto.IsNull)
            return SqlString.Null;


        var retorno = "";
        var palavra = Ds_Texto.Value;
        var len = palavra.Length;

        for (var i = 0; i < len; ++i)
        {
            var letra = palavra[i];

            if (letra >= '0' && letra <= '9')
                retorno += letra;
        }

        return string.IsNullOrEmpty(retorno) ? SqlString.Null : retorno;
    }
};

Salida:

Comparación de rendimiento en SELECT

fncMes

Ver contenido
Código fuente T-SQL:
-- @Fl_Tipo define como é o tipo de formatação
-- 1: Janeiro Fevereiro Marco Abril...
-- 2: JANEIRO FEVEVEIRO MARCO ABRIL
-- 3: Jan Fev Mar Abr
-- 4: JAN FEV MAR ABR
-- 5: January, February ... (cubo)

CREATE FUNCTION [dbo].[fncMes] (@Dt_Referencia DATETIME, @Fl_Tipo TINYINT, @Fl_Incluir_Ano BIT = 0, @Fl_Incluir_Dia BIT = 0)
RETURNS VARCHAR(30)
AS BEGIN

    
    DECLARE @Mes TINYINT
    SET @Mes = DATEPART(MONTH, @Dt_Referencia)


    DECLARE @Ds_Mes as varchar(30)
    SET @Ds_Mes = CASE 
        WHEN @Mes =  1 THEN 'Janeiro'
        WHEN @Mes =  2 THEN 'Fevereiro'
        WHEN @Mes =  3 THEN 'Março'
        WHEN @Mes =  4 THEN 'Abril'
        WHEN @Mes =  5 THEN 'Maio'
        WHEN @Mes =  6 THEN 'Junho'
        WHEN @Mes =  7 THEN 'Julho'
        WHEN @Mes =  8 THEN 'Agosto'
        WHEN @Mes =  9 THEN 'Setembro'
        WHEN @Mes = 10 THEN 'Outubro'
        WHEN @Mes = 11 THEN 'Novembro'
        WHEN @Mes = 12 THEN 'Dezembro'	
        ELSE NULL
    END
    IF (@Fl_Tipo IN (3,4)) SET @Ds_Mes = SubString(@Ds_Mes,1,3)
    IF (@Fl_Tipo IN (2,4)) SET @Ds_Mes = Upper(@Ds_Mes)

    IF (@Fl_Tipo = 5) BEGIN
        DECLARE @Date datetime 
        SET @Date = '2001'+Right('0'+Cast(@Mes as varchar(2)),2)+'01' 
        SET @Ds_Mes = DateName(Month,@Date)
    END
    
    
    IF (@Fl_Incluir_Ano = 1)
        SET @Ds_Mes = @Ds_Mes + ' ' + CAST(DATEPART(YEAR, @Dt_Referencia) AS VARCHAR(4))
    
    
    IF (@Fl_Incluir_Dia = 1)
        SET @Ds_Mes =   CAST(DATEPART(DAY, @Dt_Referencia) AS VARCHAR(4)) + '/' + @Ds_Mes
    

    RETURN @Ds_Mes
    
END

Código fuente CLR:

using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncMes(SqlDateTime Dt_Referencia, SqlInt32 Fl_Tipo, SqlBoolean Fl_Incluir_Ano, SqlBoolean Fl_Incluir_Dia)
    {


        #region Validações

        if (Dt_Referencia.IsNull)
            return null;


        if (Fl_Tipo.IsNull)
            Fl_Tipo = 1;


        if (Fl_Incluir_Ano.IsNull)
            Fl_Incluir_Ano = false;


        if (Fl_Incluir_Dia.IsNull)
            Fl_Incluir_Dia = false;

        #endregion


        var mes = Dt_Referencia.Value.Month;
        string retorno;


        if (Fl_Tipo == 5)
        {

            switch (mes)
            {
                case 1:
                    retorno = "January";
                    break;

                case 2:
                    retorno = "February";
                    break;

                case 3:
                    retorno = "March";
                    break;

                case 4:
                    retorno = "April";
                    break;

                case 5:
                    retorno = "May";
                    break;

                case 6:
                    retorno = "June";
                    break;

                case 7:
                    retorno = "July";
                    break;

                case 8:
                    retorno = "August";
                    break;

                case 9:
                    retorno = "September";
                    break;

                case 10:
                    retorno = "October";
                    break;

                case 11:
                    retorno = "November";
                    break;

                case 12:
                    retorno = "December";
                    break;

                default:
                    retorno = null;
                    break;
            }
               
        }
        else
        {

            switch (mes)
            {
                case 1:
                    retorno = "Janeiro";
                    break;

                case 2:
                    retorno = "Fevereiro";
                    break;

                case 3:
                    retorno = "Março";
                    break;

                case 4:
                    retorno = "Abril";
                    break;

                case 5:
                    retorno = "Maio";
                    break;

                case 6:
                    retorno = "Junho";
                    break;

                case 7:
                    retorno = "Julho";
                    break;

                case 8:
                    retorno = "Agosto";
                    break;

                case 9:
                    retorno = "Setembro";
                    break;

                case 10:
                    retorno = "Outubro";
                    break;

                case 11:
                    retorno = "Novembro";
                    break;

                case 12:
                    retorno = "Dezembro";
                    break;

                default:
                    retorno = null;
                    break;
            }

            if (Fl_Tipo == 3 || Fl_Tipo == 4)
                retorno = retorno?.Substring(0, 3);

            if (Fl_Tipo == 2 || Fl_Tipo == 4)
                retorno = retorno?.ToUpper();

        }

        
        if (Fl_Incluir_Ano.Value)
            retorno += " " + Dt_Referencia.Value.Year.ToString("0000");


        if (Fl_Incluir_Dia.Value)
            retorno = Dt_Referencia.Value.Day.ToString("00") + "/" + retorno;


        return retorno;

    }
}

Salida:

Comparación de rendimiento en SELECT

fncConverte_Em_Horas

Ver contenido
Código fuente T-SQL:
CREATE FUNCTION [dbo].[fncConverte_Em_Horas] (@Qt_Tempo BIGINT, @Tp_Tempo VARCHAR(10))
RETURNS VARCHAR(MAX)
BEGIN 

    DECLARE @ResultadoNegativo TINYINT = 0

    IF (@Qt_Tempo < 0)
    BEGIN
        SET @ResultadoNegativo = 1
        SET @Qt_Tempo = @Qt_Tempo * (-1)
    END 
    

    DECLARE @Diferenca BIGINT = @Qt_Tempo, 
            @Segundos BIGINT = 0, 
            @Minutos BIGINT = 0, 
            @Horas BIGINT = 0

    
    IF(@Tp_Tempo IN('ss','second'))
    BEGIN
        
        SET @Horas = @Diferenca / 3600 
        SET @Diferenca = @Diferenca - (@Horas * 3600) 

        SET @Minutos = @Diferenca / 60 
        SET @Diferenca = @Diferenca - (@Minutos * 60) 

        SET @Segundos = @Diferenca 
    
    END
    
    IF(@Tp_Tempo IN('mm','minute'))
    BEGIN
        
        SET @Horas = @Diferenca / 60
        SET @Diferenca = @Diferenca - (@Horas * 60)

        SET @Minutos = @Diferenca

        SET @Segundos = 0 
    
    END
    
    IF(@Tp_Tempo IN('hh','hour'))
    BEGIN
        
        SET @Horas = @Diferenca 

        SET @Minutos = 0

        SET @Segundos = 0
    
    END
    
        
    RETURN 
        (CASE WHEN @ResultadoNegativo = 1 THEN '-' ELSE '' END) + 
        (CASE WHEN @Horas <= 9 THEN RIGHT('00' + CAST(@Horas AS VARCHAR(1)), 2) ELSE CAST(@Horas AS VARCHAR(MAX)) END + ':' + 
         RIGHT('00' + CAST(@Minutos AS VARCHAR(2)), 2) + ':' + 
         RIGHT('00' + CAST(@Segundos AS VARCHAR(2)), 2)) 
        
END

Código fuente CLR:

using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncConverte_Em_Horas(SqlInt64 Qt_Tempo, SqlString Tp_Tempo)
    {

        if (Qt_Tempo.IsNull)
            return SqlString.Null;


        if (Tp_Tempo.IsNull)
            return SqlString.Null;


        var resultadoNegativo = false;
        var tempo = Qt_Tempo.Value;

        if (tempo < 0)
        {
            resultadoNegativo = true;
            tempo = tempo * -1;
        }


        var diferenca = tempo;
        long segundos = 0;
        long minutos = 0;
        long horas = 0;

        switch (Tp_Tempo.Value)
        {

            case "ss":
            case "second":
                horas = diferenca / 3600;
                diferenca -= (horas * 3600);

                minutos = diferenca / 60;
                diferenca -= (minutos * 60);

                segundos = diferenca;

                break;


            case "mm":
            case "minute":
                horas = diferenca / 60;
                diferenca -= (horas * 60);

                minutos = diferenca;

                break;


            case "hh":
            case "hour":
                horas = diferenca;

                break;

        }

        return ((resultadoNegativo) ? "-" : "") + horas.ToString("00") + ":" + minutos.ToString("00") + ":" + segundos.ToString("00");

    }
}

Salida:

Comparación de rendimiento en SELECT

fncFormato_Documento

Ver contenido
Código fuente T-SQL:
CREATE FUNCTION [dbo].[fncFormata_Documento](
    @Nr_Documento varchar(max)
)
RETURNS varchar(max)
AS BEGIN

    SET @Nr_Documento = Replace(@Nr_Documento,'.','')
    SET @Nr_Documento = Replace(@Nr_Documento,'/','')
    SET @Nr_Documento = Replace(@Nr_Documento,'-','')

    DECLARE @Nr_Formatado varchar(max)
    
    
    IF (LEN(@Nr_Documento) = 14) BEGIN
        SET @Nr_Formatado = 
            substring(@Nr_Documento,1,2) + '.' + 
            substring(@Nr_Documento,3,3) + '.' + 
            substring(@Nr_Documento,6,3) + '/' + 
            substring(@Nr_Documento,9,4) + '-' + 
            substring(@Nr_Documento,13,2)
    END
    
    
    IF (LEN(@Nr_Documento) = 11) BEGIN	
        SET @Nr_Formatado = 
            substring(@Nr_Documento,1,3) + '.' + 
            substring(@Nr_Documento,4,3) + '.' + 
            substring(@Nr_Documento,7,3) + '-' + 
            substring(@Nr_Documento,10,2)
    END
    
    IF (@Nr_Formatado IS NULL) SET @Nr_Formatado = @Nr_Documento
    
    RETURN @Nr_Formatado
    
END

Código fuente CLR:

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncFormata_Documento(SqlString Nr_Documento)
    {

        if (Nr_Documento.IsNull)
            return SqlString.Null;


        var documento = Nr_Documento.Value.Replace(".", "").Replace("-", "").Replace("/", "").Replace(" ", "");

        switch (documento.Length)
        {

            case 11:
                return Convert.ToUInt64(documento).ToString(@"000\.000\.000\-00");

            case 14:
                return Convert.ToUInt64(documento).ToString(@"00\.000\.000\/0000\-00");

        }

        return documento;

    }

}

Salida:

Comparación de rendimiento en SELECT

Conclusión

Después de 10 pruebas de rendimiento entre funciones escalares T-SQL x CLR, podemos concluir que en todos los casos presentados (elegí 10 de 90 funciones que tengo en ambos lenguajes, de forma aleatoria), la función CLR tuvo una enorme ganancia de rendimiento, tanto en CPU como en tiempo de ejecución. En la empresa donde trabajo como DBA, probé las 90 funciones y en 87 casos, la función escrita en C# se ejecutó en al menos la mitad del tiempo que la función T-SQL.

Sin embargo, como dije anteriormente, esto no significa que en el 100% de los casos esto vaya a ocurrir. Como ocurre con todo lo relacionado con el ajuste del rendimiento, no existe un consejo perfecto que se aplique al 100% de las situaciones. Antes de cambiar una función T-SQL a SQL, realice varias pruebas para asegurarse de que realmente hubo una ganancia de rendimiento significativa al crear la función en CLR.

En los primeros 2 ejemplos, donde las funciones son muy simples, también pude compararlas con funciones nativas de SQL Server, sin el uso de funciones UDF. En este caso se pudo observar que sin utilizar la función UDF y aplicando las funciones nativas directamente al SELECT y/o WHERE, termina siendo más rápido que el CLR. Sin embargo, las funciones suelen ser bastante complejas, lo que acaba dificultando e incluso imposibilitando su uso sin crear una función UDF.

Las funciones en SQL Server son una excelente manera de encapsular y reutilizar código, pero al mismo tiempo, pueden terminar comprometiendo el rendimiento de sus consultas. En este caso, la solución que actúa como punto medio entre la función nativa de SQL Server y la función escalar T-SQL sería la función escalar CLR, que tiene un rendimiento muy superior a la función T-SQL (en la gran mayoría de los casos) y cercano a la función nativa y al mismo tiempo, permite la reutilización y encapsulación de código.

A través de este post, espero haberte mostrado una forma real de optimizar las consultas SQL, sin tener que cambiar 1 línea de código, sin tener que cambiar nada en tu instancia. En muchos casos los índices se están usando como se esperaba, las estadísticas se actualizan, no hay advertencias en el plan de ejecución, pero al usar una función T-SQL el rendimiento de la consulta termina siendo pobre.

En este caso, el uso de funciones CLR puede representar una gran mejora en su aplicación/rutina y ahorrar valiosos segundos/ciclos de CPU a diario. Con esto, fue posible reducir varias horas de procesamiento/CPU diarias en mis instancias de trabajo, simplemente reemplazando funciones T-SQL con funciones CLR y esto puede ser una gran diferencia en la vida del DBA.

Espero que te haya gustado esta publicación.
Si tienes alguna duda o crítica déjala aquí en los comentarios.

¡Abrazo!