Operador Apply. Cross Apply vs Outer Apply

El operador apply es uno de los más importantes y usados cuando elevas la complejidad de las consultas.

Cuando empiezas no te hace falta, y siempre encuentras maneras de acceder a la información a través de subconsultas, que parece que funcionan muy bien, hasta que llegas a la cruda realidad y tienes que «mezclar» conjuntos de miles y miles de filas, con decenas de conjuntos. El resultado, que las subconsultas, se te empiezan a quedar «justitas» y no sabes como hacer para que funcionen, a la velocidad que te demandan.

Y justo en ese momento, aparece el operador apply.

Creo que existen muchas definiciones para el mismo, por su versatilidad, o muchas maneras de expresarlo. Pero si piensas al principio, en el uso como una subconsulta correlativa. Con esta definición ya tienes suficiente para hacerte una idea de cómo y dónde lo puedes utilizar.

Imagen de eli maya en Pixabay

Lo primero para poder observar su comportamiento, es crear un escenario, y para esto vamos a generar una tabla para almacenar ventas, con productos y clientes y precios.

-- Code 1.0 Escenario

Create table dbo.ventaEj
(
  id int identity(1, 1)
, Cliente    int
, Producto   int
, descuento1 float
, descuento2 float
, descuento3 float
, totalneto  float
);

Ahora llenamos la misma con algunos valores.

-- Code 1.1

Insert into dbo.ventaEj
(
  Producto
, cliente
, descuento1
, descuento2
, descuento3
, totalneto
)
values
( 1, 1, 10 , 5.2 , 3.12, 1125.32 ),
( 2, 1, 20 , 12.2, 3.20, 2150.50 ),
( 3, 1, 10 , 6.47, 3.31, 1501.20 ),
( 1, 2,  0 , 3.22, 5.21, 1500.99 ),
( 2, 2, 40 , 9.41, 5.74, 1400.50 ),
( 3, 4, 11 , 6.12, 7.43, 7000.32 );

Ya tenemos un punto de partida.

Utilizar cross apply como alias de columnas

Supongo que es el escenario que menos se utiliza, pero al menos a mi me sorprendió la primera vez que, por error, lo puse en funcionamiento.

Vamos a utilizar cross apply para tratar todos los descuentos como uno solo, descuentoTotal y no tener que referirnos a cada uno de ellos, varias veces, para varias columnas. Además utilizaremos un union all, para combinar una consulta normal, con la del operador apply y ver los resultados.

-- Code 1.2

-- Consulta superior
Select 'normal' as modo
, v.id
, v.Producto
, ( v.descuento1+v.descuento2+v.descuento3) 
        as TotalDtos
, v.totalneto - 
    (v.descuento1+v.descuento2+v.descuento3) 
        as ImpDto
, v.totalneto as ImporteTotal
from dbo.ventaEj v
union all
-- Consulta inferior
select 'Apply'
, v.id
, v.Producto
, dtos.dto as TotalDtos
, v.totalneto - dtos.dto as ImpDto
, v.totalneto as ImporteTotal
from dbo.ventaEj v
cross apply 
(select v.descuento1+v.descuento2+v.descuento3 as dto)
         as dtos
order by id, modo desc

Resultado Query 1.2

Los resultados son los mismos. La consulta «inferior», es mucho más clara. Vamos a ver los planes de ejecución, por si su «coste» fuera importante.

Plan ejecución consulta superior «normal»

Plan de ejecución consulta inferior «Apply»

Los planes de ejecución son idénticos, y no se realiza ninguna operación de union entre conjuntos.

En conclusión: Siempre que la consulta no sea demasiado compleja o que utilice demasiadas filas (demasiadas es un concepto que equivale a muchisisisimas), es una idea genial. Si la consulta es muy compleja, habría que comprobar si tiene impacto en el analizador de consultas.

Utilizar apply subquery correlativa

Típico escenario, donde se necesitan las últimas ventas de un cliente.

-- Code 2.0

select top (3) v.id
			 , v.Cliente
			 , v.Producto
			 , v.descuento1
			 , v.descuento2
			 , v.descuento3
			 , v.totalneto
	   from dbo.ventaEj AS v
	   where v.Cliente = 1;

Esta sería una consulta normal para las ventas de un cliente (En el caso 3 ventas). Pero, ahora dirección de negocio nos solicita las última venta de varios clientes, y el importe de la venta mayor. O lo que es lo mismo, tenemos un conjunto de datos, y tenemos que sacar una subconsulta para cada fila. Esto para lo que se utilizan subQuerys, es algo que podemos hacer con el operador apply de una manera más eficiente, y muy concisa.

-- Code 2.1

select   
  v.id
, v.Cliente
, v.Producto
, v.descuento1
, v.descuento2
, v.descuento3
, v.totalneto
, c.totalneto as MaximaVenta
  from dbo.ventaEj AS v
 cross apply 
 (
 	select max(c.totalneto) as totalneto
 	from dbo.ventaEj c
 	where c.Cliente = v.cliente
 
 ) as c
where v.Cliente = 1;

Si observamos el conjunto «c», se obtiene el máx totalneto de ventasEj, donde cliente sea el mismo cliente que el conjunto exterior llamado v. Aquí está el juego. La subconsulta exterior es correlativa a la interior.

Aunque lo que leamos de la consulta sea esto, el plan de ejecución no tiene nada que ver. El motor lee la tabla del conjunto exterior. Crea otra tabla con el valor agregado por el conjunto interior y mezcla ambos conjuntos.

Otra de las ventajas que tiene el operador apply, es que el número de columnas que devuelve, no tiene que ser una, como podemos hacer con una subconsulta.

-- Code 2.2

select   
  v.id
, v.Cliente
, v.Producto
, v.descuento1
, v.descuento2
, v.descuento3
, v.totalneto
, c.*
  from dbo.ventaEj AS v
 cross apply 
 (
  select max(c.totalneto) as totalneto
       , min(c.totalneto) as minTotal
       , avg(c.totalNeto) as avgTotal
 	from dbo.ventaEj c
 	where c.Cliente = v.cliente
  ) as c;

Como puedes observar, ahora directamente devolvemos todas las columnas que devuelva el conjunto del cross apply, y para todos los clientes, de manera que tenemos, el máximo importe de venta por cliente, el mínimo, y la media.

OUTER APPLY vs CROSS APPLY

Cross apply = Inner Join como Outer Apply = Left Join

Es seguro, la definición más precisa que podamos encontrar. Cuando necesitamos que el conjunto del cross apply nos devuelva filas aunque no las tenga, utilizamos outer.

El operador apply es demasiado importante para 1 sólo artículo, pero como aproximación, creo que es suficiente para empezar.

🙂