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:

Cada uno de los casos se ha probado contra datos almacenados en una tabla OnDisk, como en una tabla InMemory

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
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

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

 

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)

Viendo las gráficas podemos extraer varias conclusiones:

Consumo CPU(ms)Tiempo efectivo(ms)
UDF performance msUDFs 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 🙁
 El proyecto completo con todos los scripts se encuentran aqui SQLServerScalarUDFPerformance y los tiempos están aquí (excel file)

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
 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 🙂

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 🙂

 

 

Enrique Catalá

Enrique Catalá es Mentor en SolidQ, Microsoft Data Platform MVP desde 2013 e Ingeniero en informática. Es Microsoft Certified Trainer (MCT) y está centrado en el motor relacional SQL Server, tanto en la resolución de problemas de rendimiento y escalabilidad en sistemas OLTP como la definición e implementación de entornos de alta disponibilidad confiables, en donde ha llevado con éxito más de 100 proyectos no solo en España, sino en diferentes países como EEUU, Holanda, México, Arabia Saudí, Austria... Es el arquitecto principal de las soluciones para SolidQ llamadas HealthCheck, SQL2Cloud, SCODA y del generador de SSIS de SolidQ. Ponente habitual del SolidQ SUMMIT, miembro y ponente en SQL PASS tanto en España como Iberoamérica ponente en varios SQLSaturday

Latest posts by Enrique Catalá (see all)