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)
Tiempo efectivo(ms)
[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) |
 |
 |
- JAMAS, NUNCA, DE NINGUNA FORMA UTILICES FUNCIONES ESCALARES T-SQL (las del caso 1)
- 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 :))
- 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…
- 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? :))
- 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 🙁
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):
El código las funciones está aquí publicado:
Viendo esto podemos extraer las siguientes conclusiones:
- 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
- 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
- CLR funciona todavía mas eficiente cuando los datos están en objetos InMemory
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 🙂
