0
Shares
0
0
0

Las funciones de usuario (UDFs) son un recurso ampliamente utilizado por cualquier desarrollador hoy en día. Es lógico ya que nos ayudan a encapsular lógica, nos dan claridad de código y nos permiten ver nuestras consultas como en nuestro lenguaje de programación procedural favorito :). Lamentablemente no en pocas ocasiones esas inofensivas e inocuas funciones, mal utilizadas son los focos de problemas de rendimiento que lastran la escalabilidad de nuestro sistema. Dado que la aparición de SQL Server 2016 es mas o menos reciente y que en esta edición del motor disponemos ahora del recurso de “UDFs nativamente compiladas”…vamos a ver qué tal rinden este tipo de funciones. Para este primer post, vamos a centrarnos únicamente en funciones UDF sin acceso a datos.

En SQL Server 2016 podemos programar las funciones de usuario de 3 formas:

No es momento este post de explicar al detalle cuando no podemos utilizar cada una de ellas sino de centrarme en qué podemos esperar del rendimiento en cada caso, para saber a qué atenernos. Para ello, he hecho una prueba de rendimiento bastante sencilla pero ilustrativa…encapsular la lógica de sumar dos valores y devolver si el resultado es un valor par. Quiero dejar claro desde el principio que lo hacemos sobre funciones escalares muy sencillas y sin acceso a datos, puramente lógica sencilla que uno esperaría que ejecutase rápido…Voy a hacer este escenario programándolo en todas las formas que tenemos disponibles con SQL Server:

[box type=”info”] Cada uno de los casos se ha probado contra datos almacenados en una tabla OnDisk, como en una tabla InMemory[/box]

1) Funcion escalar T-SQL (UDF)

Muy sencilla, nada especial

CREATE FUNCTION [dbo].IsSumEvenNumber
(
  @num INT,
  @num2 int
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
  RETURN (CASE WHEN (@num+@num2) %2 = 0 THEN 1 ELSE 0 END);
END

Un ejemplo de uso podría ser:

SELECT  COUNT(*)
FROM    dbo.Numbers_OnDisk
WHERE   dbo.IsSumEvenNumber(n, n2) = 1;

 

2) Funcion escalar CLR (UDFclr)

En este caso deberemos programarnos un ensamblado y desplegárnoslo. El código c# a desplegar en SQL Server es:

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

public partial class UserDefinedFunctions
{
    /// <summary>
    /// Por defecto DataAccess = NO
    /// </summary>
    /// <param name="num"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction(IsPrecise = true, IsDeterministic = true, DataAccess = DataAccessKind.None)]
    public static SqlBoolean IsSumEvenNumberClr(SqlInt32 num, SqlInt32 num2)
    {
        return new SqlBoolean((num+num2) % 2 == 0 ? true : false);
    }

}

Un ejemplo de uso podría ser:

SELECT  COUNT(*)
FROM    dbo.Numbers_OnDisk
WHERE   dbo.IsSumEvenNumberClr(n, n2) = 1;

 

3) Funcion de tabla T-SQL (UDFInline)

A priori es igual que la escalar T-SQL del punto 1…pero se utilizará mediante CROSS APPLY

CREATE FUNCTION [dbo].[IsSumEvenNumberInline] ( @num INT , @num2 int)
RETURNS TABLE
AS
RETURN
    ( SELECT    CASE WHEN (@num+@num2) % 2 = 0 THEN 1
                     ELSE 0
                END AS result
    );

Un ejemplo de uso podría ser

SELECT  COUNT(*)
FROM    Numbers_OnDisk f
        CROSS APPLY dbo.IsSumEvenNumberInline(n, n2) e
WHERE   e.Result = 1;
GO

 

4) Función escalar compilada nativa para In-Memory OLTP (UDFInmemory)

Aquí vemos una pequeña adaptación necesaria en In-Memory debido a algunas carencias existentes, pero en esencia el resultado es exactamente el mismo

CREATE FUNCTION inmemory.[IsSumEvenNumberInMemory]
(
  @num INT,
  @num2 int
)
RETURNS bit
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (
  TRANSACTION ISOLATION LEVEL = SNAPSHOT,
  LANGUAGE = N'English')
  --RETURN (CASE WHEN @num %2 = 0 THEN 1 ELSE 0 END); -- CASE IS NOT SUPPORTED
  
  DECLARE @return bit

  IF (@num+@num2) %2 = 0
     SET @return = 1
  ELSE
     SET @return = 0

  RETURN(@return)
END
[box type=”info”] En In-Memory OLTP no está implementada la cláusula CASE para funciones escalares…piensa que el código nativamente compilado es directamente C…es altamente eficiente[/box]

Un ejemplo de uso podría ser

SELECT  COUNT(*)
FROM    dbo.Numbers_OnDisk
WHERE   inmemory.IsSumEvenNumberInMemory(n, n2) = 1;	
GO

 

5) Expansión de código (UDFExpanded)

Se trata de evitar utilizar la función y optar por meter el código en cada query, con el problema de no tener lógica encapsulada, claro. Un ejemplo de uso sería:

SELECT  COUNT(*)
FROM    dbo.Numbers_OnDisk
WHERE   ( n + n2 ) % 2 = 0;
GO

 

Veamos las comparativas de rendimiento (se basan en medias de varias ejecuciones):

Coste de CPU(ms)

UDFs performance
Comparativa de tiempos CPU

Tiempo efectivo(ms)

Comparativa tiempos UDF
Comparativa tiempos UDF

 

[box type=”info”] El tiempo efectivo puede ser menor al tiempo de coste CPU si hay un plan de ejecución paralelo que aproveche todos los cores (en este ejemplo 8)[/box]

Viendo las gráficas podemos extraer varias conclusiones:

Consumo CPU(ms) Tiempo efectivo(ms)
UDF performance ms UDFs elapsed time ms
  1. JAMAS, NUNCA, DE NINGUNA FORMA UTILICES FUNCIONES ESCALARES T-SQL (las del caso 1)
  2. El mejor caso siempre es expandir el código…pasar de funciones de usuario y por tanto no encapsular lógica (supongo que no quieres oírlo pero así es la vida :))
  3. La que mejor rendimiento da encapsulando siempre es la versión UDF de tabla (la que en los ejemplos he llamado UDFInline) gracias a su uso mediante CROSS APPLY, que permite paralelismo y estimación de filas generando un plan de ejecución mas eficiente
    • IMPORTANTE: No siempre se puede transformar una función escalar a Inline…
  4. La siguiente opción por tanto será irnos a programar en CLR.
    • Depende del código, pero la misma función en CLR es 10x mas eficiente que con T-SQL. Esto tiene que ver con el contexto de ejecución, que es mucho mas eficiente procesando código fila a fila con CLR vs T-SQL (lo sabias? :))
  5. La gran decepción para funciones escalares que no acceden a datos…las funciones nativamente compiladas van bastante peor que el peor caso que teníamos antes 🙁
[box type=”download”] El proyecto completo con todos los scripts se encuentran aqui SQLServerScalarUDFPerformance y los tiempos están aquí (excel file)[/box]

Entonces…para qué sirven las funciones nativamente compiladas sin acceso a datos? Bueno, la gracia de las funciones nativamente compiladas en este caso pasa porque precisamente cuanto mas complejo es el código a evaluar de la función, mayor rendimiento aportan respecto a la solución T-SQL. Además permiten ejecución en paralelo (cuando se estíma oportuno por el optimizador).  En los ejemplos previos he tomado como referencia código muy sencillo y monohilo (que por otro lado es la mayoría de escenarios que suelo ver) y lo he ejecutado sobre unos cuantos millones de filas, haciendo que el peso del tiempo recaiga sobre todo en los cambios de contexto. ¿Qué pasa si la función hace código “complejo” en términos de CPU? Algo así, por ejemplo

DECLARE @steps INT = @num1 % 123456
  DECLARE @no_of_sides_in_the_polygon INT =@num2 % 54321
  DECLARE @return2 FLOAT = 1.0
  DECLARE @return FLOAT 
  SET @return = SIN(pi()/180*(180.0 - 360/(@no_of_sides_in_the_polygon + @steps))/2)/sin(pi()*2/(@no_of_sides_in_the_polygon + @steps))
  IF(@return < 1.0)
    SET @return2 = 1.0
  IF (@return > 1.0 AND @return < 5000)
    SET @return2 = 5.0
  IF (@return >= 5000 AND @return < 100000)
    SET @return2 = 6.0
  IF(@return >=100000 AND @return < 200000)
    SET @return2 = 7.0
  IF (@return >=200000)
    SET @return2 = 9.0

  SET @return = @return * (1.0 -@return2)

En este caso probaremos con funciones escalares T-SQL, funciones nativamente compiladas y funciones CLR. Veamos los tiempos (todo monohilo):

UDFs performance CPU calculation

El código las funciones está aquí publicado:

Viendo esto podemos extraer las siguientes conclusiones:

  1. Las funciones nativamente compiladas son mas eficientes SIEMPRE comparadas contra las TSQL normales cuanto mas complejo sea el código.
    • Generalmente permiten hacer copy-paste del código T-SQL como función compilada por lo que invirtiendo poco o nada tiempo, podemos obtener un buen beneficio
  2. Las funciones escalares CLR vuelven a ser la mejor alternativa de nuevo
    • Pero requieren de alguien que sea capaz de reescribir el código a c# y desplegar CLR en SQL Server, cuyas implicaciones no hemos tratado en este post
  3. CLR funciona todavía mas eficiente cuando los datos están en objetos InMemory
[box type=”info”] El rendimiento mas eficiente siempre lo da utilizar un procedimiento almacenado nativamente compilado expandiendo el código, puedes verlo aqui: TESTING-PERFORMANCE.sql , pero ya no hablamos entonces de UDFs 🙂 [/box]

Recuerda que este post está centrado en funciones que no acceden a datos…sino que realizan operaciones sobre los datos que reciben como parámetros de entrada. Para hablar del rendimiento de funciones escalares con acceso a datos hay que esperar al segundo post 🙂

 

 

0 Shares:
Share 0
Tweet 0
Share 0

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

— Previous article

In-Memory OLTP Utils

Next article —

Monitorizando SSIS like a boss

You May Also Like
AArtículos de Data
Leer más
  • 2 minute read

Hilando fino en SSAS multidimensional

El equipo de SolidQ ha estado buscando la mejor manera de implementar una jerarquía padre-hijo de cuentas contables con un operador unitario que tuviera un buen rendimiento, a pesar de la gran cantidad de datos a la que tenía que enfrentarse. Veremos cómo aplanar la jerarquía, cómo implementarlo con SSAS, con una alternativa MDX, cómo añadir ordenación a las cuentas basadas en otro atributo, Time Balance Average y algún otro truco de tuning.