Redescubriendo SQL: APPLY

Mientras curioseaba en las sentencias SQL que genera EF descubrí que utilizaba un operador un tanto olvidado: APPLY. A simple vista, su uso y aplicaciones pueden recordar a los de la cláusula JOIN y, como vamos a ver, en ocasiones pueden ser intercambiables en busca de un mejor rendimiento.

APPLY permite invocar por cada fila de una consulta una función que devuelva un tipo tabla. En otras palabras, hace una proyección de la expresión izquierda a través de la función (o expresión de tipo tabla) que se le indica.

Lo veremos mejor con un ejemplo sencillo. Vamos a crear una tabla que contiene en un campo de tipo cadena un array de enteros, separados por comas. En nuestra consulta, queremos obtener cada valor de esa lista en un registro diferente. Empecemos definiendo la tabla con unos datos de ejemplo y la función de separación de valores del array que proyectaremos con APPLY:

-- Tabla y datos de ejemplo
CREATE TABLE Arrays
(
  arrid INT NOT NULL IDENTITY PRIMARY KEY,
  array VARCHAR(7999) NOT NULL
)
INSERT INTO Arrays VALUES('')
INSERT INTO Arrays VALUES('10')
INSERT INTO Arrays VALUES('20,40,30')
INSERT INTO Arrays VALUES('-1,-3,-5')

-- Funcion de extraccion de arrays
CREATE FUNCTION fn_splitarr(@arr AS VARCHAR(7999))
  RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)
AS
BEGIN
  DECLARE @end AS INT, @start AS INT, @pos AS INT
  SELECT @arr = @arr + ',', @pos = 1,
    @start = 1, @end = CHARINDEX(',', @arr, @start)
  WHILE @end > 1
  BEGIN
    INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start))
    SELECT @pos = @pos + 1,
      @start = @end + 1, @end = CHARINDEX(',', @arr, @start)
  END
  RETURN
END
GO

Una vez tenemos la tabla de pruebas y la función, ya podemos ejecutar un ejemplo. Como vemos, es muy sencillo:

SELECT A.arrid, F.*
FROM Arrays AS A
  CROSS APPLY fn_splitarr(array) AS F

-- Salida
arrid       pos         value
-----       ---         -----
2           1           10
3           1           20
3           2           40
3           3           30
4           1           -1
4           2           -3
4           3           -5

No he explicado qué significa el CROSS. Al utilizar APPLY, podemos indicar con CROSS o con OUTER si queremos que en el resultado se incluyan las filas de la parte izquierda que no generan resultados para la función. Es análogo a la diferencia entre utilizar una LEFT (OUTER) JOIN o una INNER JOIN entre tablas. En el caso de ejemplo, al usar CROSS no se han devuelto resultados para el primer registro de la tabla Arrays, puesto que no generaba salida para fn_splitarr. De haber utilizado OUTER, la primera fila de resultados hubiera sido [1 , NULL , NULL ].

Esta cláusula nos puede permitir también optimizar algunas construcciones con JOIN. Hay una discusión muy interesante en este hilo de stackoverflow y un par de buenos ejemplos con los distintos planes de ejecución para las diferentes alternativas, muy ilustrativos: [Using CROSS APPLY to optimize joins on BETWEEN conditions] [INNER JOIN vs. CROSS APPLY].

En resumen, y como receta general, parece que si tenemos JOINs con SELECTs anidadas que dependen de campos que provienen de la parte izquierda de la JOIN tenemos una consulta candidata a ser optimizada con APPLY. En cualquier caso, la conveniencia de una u otra siempre dependerá del caso concreto: no hay más remedio que probar.

Hasta la próxima, happy coding!🙂

PD) Por cierto, la consulta generada por EF que hacía uso de APPLY no parece la más óptima. Cosas que pasan😛

Esta entrada fue publicada en Dev y etiquetada . Guarda el enlace permanente.

Una respuesta a Redescubriendo SQL: APPLY

  1. Israel dijo:

    Me gustaria saber si el cross apply solo sirve en funciones creadas o tambien sobre tablas
    Estoy buscando algo similar a lo que hace un applymap (qlikview)

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s