post icon

Generar un rango de filas para usar como producto cartesiano o como conjuto de datos en SQL Server

Otra de las tantas interrogantes que todo programador de SQL suele hacerse, es como generar filas dinámicamente sin leer datos de tablas, para cruzarlo con otras tablas o vistas y así obtener un producto cartesiano o un conjunto de resultados para unirlos con otros conjuntos. La típica solución suele ser usar un bucle como un while o un cursor, para insertar datos en alguna variable tipo tabla o una tabla temporal los cuales presentan serios problemas de rendimiento. De hecho, haremos unas pruebas con un millón de filas creadas en discos distintos, el primer disco es Híbrido (Sata 3 5400 rpm + SSD)  y el segundo un RAID 10 compuesto por 4 discos Sata 3. En el primer ejemplo cargaremos los datos en una variable de tipo tabla, con el siguiente script

declare @fila int = 1
declare @tabla table (fila int)

while @fila <= 1000000
begin
	insert into @tabla ( fila ) values ( @fila )
	set @fila = @fila + 1
end

select * from @tabla as t

El Resultado:

  • Disco Híbrido: 50 segundos
  • RAID 10: 1 minuto 35 segundos

El segundo ejemplo utiliza básicamente la misma lógica, pero en vez de una variable de tipo tabla, crea una tabla temporal

declare @fila int = 1
create table #tabla (fila int)

while @fila <= 1000000
begin
	insert into #tabla ( fila ) values ( @fila )
	set @fila = @fila + 1
end

select * from #tabla as t

drop table #tabla

El Resultado:

  • Disco Híbrido: 1 minuto 1 segundo
  • RAID 10: 1 minuto 4 segundos

La tercer forma de generar el mismo resultado es utilizar las Tablas de Expresión Común, CTE por sus siglas en inglés, con el que podemos generar recursividad.

WITH CTENumerico AS
(
	SELECT 1 as nro
	UNION ALL
	SELECT nro + 1 as nro
	FROM CTENumerico
	WHERE nro < 1000000
)

SELECT * FROM CTENumerico
OPTION(MAXRECURSION 0);

PD: Hay que ser cauteloso con esta query, debido a que puede generar un bucle infinito por usar la opción MAXRECURSION El Resultado:

  • Disco Híbrido: 26 segundos
  • RAID 10: 33 segundos

Con ésto queda demostrado la eficiencia y facilidad del CTE para generación dinámica de filas. Como extra agrego un ejemplo para generar un conjunto de filas dinamicas basado en un rango de fechas, que es otra actividad común y semejante.

WITH CTEFechas AS
(
	SELECT CAST('20130101' AS DATE) as fecha
	UNION ALL
	SELECT DATEADD( d , 1 ,fecha ) as fecha
	FROM CTEFechas
	WHERE fecha < CAST('20131231' AS DATE)
)

SELECT * FROM CTEFechas
OPTION(MAXRECURSION 0);

Comentarios desde Facebook:

  1. avatar
    alex PERU Google Chrome Windows
    15 agosto 2013 at 11:43 #

    Gracias, me sirvió mucho!

Responder