Funciones escalares en TSQL, JOINS, CROSS APPLY, y la madre que parió al topo.

Nota: Si eres desarrollador de TSQL puedes saltarte la introducción e ir directamente al tajo 😉

Introducción

Una de las cosas que más me gustan de SQL server -partiendo de la base de que uno es developer- son las funciones. Existen funciones de dos tipos: Aquellas que devuelven un valor escalar (Scalar-valued) y aquellas que devuelven un valor de tipo tabla (Table-valued), es decir, un conjunto de registros.

Aunque las funciones aparecieron en SQL Server 2000, no fue hasta la aparición de SQL Server 2005 cuando empecé a usarlas de forma más o menos intensa. A partir de entonces decidí que casi todo el código que tenía en TSQL en realidad podía estar en funciones, no en procedimientos almacenados como hasta entonces. Y cuando digo casi todo me refiero a que aquí hay que diferenciar el código que principalmente PROCESA datos para devolver un resultado (FUNCIONES), del código del que meramente realiza ACCIONES (inserts, updates, deletes) sobre los datos (SP).

En mi caso particular, resultó que más de un 85% del código TSQL era candidato a ser encapsulado en funciones, así que con los años he ido creando una capa de funciones escalares y de tipo tabla para acceder a los datos de varias bases de datos. Y es que desde el punto de vista de un desarrollador resulta muy tentador poder llevar la encapsulación, uno de nuestros principios favoritos de la orientación a objetos al mundo de las base de datos: Por ejemplo, crear una función de tipo tabla en base a unos parámetros de entrada y poder tratar el resultado como una tabla o vista, haciendo joins, wheres y orders sobre ella es brutal. Y crear una función escalar que realice una serie de complejas acciones a partir de los parámetros que recibe y devuelva un resultado que va a ser usado en multitud de ocasiones no tiene precio.

CREATE function [dbo].[GetDescriptionScalar](@oId int)

RETURNS VARCHAR(max)

AS

BEGIN

    DECLARE @res VARCHAR(max)

    SET @res= 

        (SELECT 

        [From] + ': ' + [Subject] + 

        CASE WHEN HasAttachment = 1 THEN

        ' (with data)'

        ELSE

        ''

        END 

        FROM [dbo].[ServerSideGridTest] 

        WHERE OID = @oId)

    RETURN @res

END

Ejemplo de función escalar: A partir de un identificador componemos una descripción basada en algunos campos de la tabla.

Rendimiento versus un buen diseño

Sin embargo, estamos haciendo bien las cosas? En ocasiones no nos damos cuenta de que podemos estar pagando un precio muy alto (rendimiento) a cambio de tener un buen diseño que facilita el mantenimiento. O lo que es peor, a veces damos por asumida una pérdida de rendimiento a sabiendas de que ganamos en diseño y reutilización de código. Y esto -en ocasiones- se lleva al extremo con el uso de funciones escalares.

Nadie duda de que la anterior función es perfectamente válida, y aunque sea muy simple ilustra perfectamente los beneficios de la encapsulación que permite TSQL. Ahora bien ¿que ocurre cuando lanzamos una consulta como esta?

SELECT t.*, dbo.GetDescriptionScalar(t.oid)

FROM [ServerModeGridProjects].[dbo].[ServerSideGridTest] t

Supongo que ya os imagináis que es una animalada, no? Estamos ejecutando la función por cada fila de la tabla (en mi caso de ejemplo 100.000 filas), de modo que por muy optimizada que esté la función, el rendimiento es bastante penoso, y me recuerda más a un cursor (otro de los demonios particulares de SQL Server) que a otra cosa. Sobre todo si lo comparamos con la misma instrucción en una sola sentencia SQL, ya que el resultado es prácticamente inmediato.

Demos una ojeada al plan de ejecución:

1) Usando la función escalar

SELECT t.*, dbo.GetDescriptionScalar(t.oid)

FROM [ServerModeGridProjects].[dbo].[ServerSideGridTest] t

2) Usando una sola sentencia SQL

SELECT t.*, [From] + ': ' + [Subject] + 

        CASE WHEN HasAttachment = 1 THEN

        ' (with data)'

        ELSE

        ''

        END AS [Description]

FROM [ServerModeGridProjects].[dbo].[ServerSideGridTest] t

A simple vista parecen idénticos, sin embargo en el primer caso la consulta tarda 01:04 (si, ¡más de un minuto!), y en el segundo apenas tres segundos… WTF?

Vamos a ver que nos dicen las estadísticas:

1) Usando la función escalar

2) Usando una sola sentencia SQL

Aquí ya podemos observar alguna diferencia no sea con demasiado detalle, sobretodo en las tres últimas filas. Con todo, todavía no hemos visto realmente qué es lo que sucede DENTRO del servidor cuando lanzamos ambas consultas. Vamos a bajar un poco más a nivel de detalle usando el SQL Profiler. Concretamente nos interesa capturar dos eventos, SQL:BatchCompleted y SP:Completed, además de algunas columnas cómo la CPU, Duration y sobretodo Reads y Writes.

1) Usando la función escalar

Más claro agua. Realmente ahora si que estamos viendo cómo la función escalar se ejecuta por cada fila, provocando lo que en inglés se llama RBAR (row-by-agonizing-row). Pero lo más preocupante es el número total de lecturas y escrituras. Fijaros en la imagen siguiente y podréis comprobar que la proporción de lecturas es de 9.603.581 a 2.243. Casi 4.300 veces más!

2) Usando una sola sentencia SQL

Bárbaro! No hay comparación posible… Entonces ¿creéis que en este caso compensa crear una función escalar? En absoluto.

¿Significa esto que no hay que usar funciones escalares en ningún caso? No, pero significa que debemos evaluar con mucho cariño dónde hay que usarlas.

¿Y entonces, dónde no hay que usar funciones escalares?

Sobretodo en valores de columna en un SELECT, como era el caso anterior, pero tampoco en la clausula WHERE ya que también provocará que se ejecute por cada fila, a fin de evaluar si cumple o no la condición. Tampoco ORDER BY… Entonces que nos queda? En valores predeterminados de columna? Bueno, tal vez, si no nos vamos a dedicar a realizar INSERTS masivos :-S

Tal vez alguien que sepa más que yo de SQL pueda aclararnos dónde usar funciones escalares, pero por el momento voy a descartar su uso. Máxime cuando existen alternativas 🙂

Funciones de tabla

Bien, todo esto es muy bonito pero en la vida real NECESITAMOS USAR FUNCIONES. Esta abstracción es primordial en muchos casos, sobre todo cuándo la lógica de negocio es compleja. Y que conste que no pretendo iniciar la eterna discusión acerca de si es recomendable o no implementar ésta en la capa de persistencia de datos o en otra capa de la aplicación. De hecho, siempre he defendido que contra menos código exista en la BD mejor 😛

Sin embargo hay ocasiones en las que el aumento de rendimiento no se puede obviar. En un proyecto en el que estoy trabajando actualmente, hay tal cantidad de datos y se realizar unos procesos tan bestias que uno de los algoritmos de cálculo -optimizado- tardaba cerca de un minuto. Moviendo esta algoritmo de la aplicación a una serie de funciones hemos conseguido que sea casi instantáneo.

Vale, y cómo hemos hecho esto? Usando funciones de tabla.

Existen dos tipos de funciones de tabla, pero ahora las trataremos por un igual, ya que en ambos casos lo que se devuelve ese un conjunto de filas y columnas. De este modo sería perfectamente transformar la anterior función escalar en esto:

CREATE FUNCTION [dbo].[GetDescriptionTable](@oId int)

RETURNS TABLE

AS

RETURN

SELECT 

    [From] + ': ' + [Subject] + 

    CASE WHEN HasAttachment = 1 THEN

    ' (with data)'

    ELSE

    ''

    END as Description

FROM 

    [dbo].[ServerSideGridTest] 

WHERE 

    OID = @oId

Observar que aunque se trata de una función de tipo tabla en realidad estamos devolviendo un sólo campo y un sólo registro. En cualquier caso esto no es para nada importante, y de hecho la verdadera potencia del uso de funciones de tipo tabla radica en devolver conjuntos de registros, y cómo no, realizar sobre éstos operaciones de tipo JOIN, WHERE, etc.

¿No sería genial poder hacer un JOIN de una tabla con el retorno de una función de tipo tabla? Pues es posible hacerlo… salvo en el caso que la función espere un parámetro que referencie la fila. Por ejemplo, el siguiente código no es válido (supongamos que la función anterior de tipo tabla también devuelve el Id):

SELECT 

    t.*, dt.[Description] AS [Description]

FROM 

    [ServerModeGridProjects].[dbo].[ServerSideGridTest] t

INNER JOIN GetDescriptionTable(t.oid) dt

ON t.OId = dt.OId

Se producirá un error, ya que SQL Server no es capaz de resolver GetDescriptionTable(t.oid).

CROSS APPLY al rescate!

Aquí entra este operador (bueno, en realidad son dos) que permite combinar los valores del lado izquierdo con los del lado derecho, creando el conjunto de resultados deseado. Utilizarlo es tan sencillo como esto:

SELECT t.*, dt.[Description] AS [Description]

FROM [ServerModeGridProjects].[dbo].[ServerSideGridTest] t

CROSS APPLY GetDescriptionTable(t.oid) dt

Sencillo y hermoso. Pero, que tal anda de rendimiento?

Pues muy bien, la verdad. Tarda exactamente tres segundos. Lo mismo que el caso anterior en el que teníamos una sola sentencia SQL. Es más, si mostramos las estadísticas vemos que se comporta de forma muy parecida:

Se puede apreciar un ligero aumento de las lecturas, pero en realidad no es nada significativo en términos de rendimiento, ya que la duración es exactamente la misma.

Conclusión

Las funciones escalares pueden suponer un problema serio de performance. Sobre todo cuando no se tiene en mente cómo funcionan. Una alternativa a tener en cuenta es utilizar funciones de tipo tabla en combinación con CROSS APPLY, ya que proporcionan un buen diseño a la vez el rendimiento no se ve penalizado en absoluto.

BTW creo que voy a tener que replantearme esto de escribir… cada vez me salen posts más ‘tochos’.

Enhorabuena si has llegado hasta aquí 😉

Nos leemos,