12 febrero 2013

TALLY TABLE: una excelnte alternativa a ciclos en SQL

Este post lo voy a dedicar a una alternativa poco conocida a la utilización de los ciclos en SQL, la TALLY TABLE. Como siempre en mis post no me gusta dar toda la información, en la red hay demasiada información como para repetir lo mismo una y otra vez. La intención es dar a conocer el uso de este recurso y fomentar la investigación y la imaginación.

Ciclos; en algún momento de nuestro trabajo debemos de utilizarlos ya que son una estructura de programación básica. Pero en consultas SQL no siempre son la mejor opción al momento de codificar una rutina, podrían ralentizar nuestro proceso, tal vez en escenarios con pocos datos todo marche sobre ruedas, pero al momento de realizar pruebas con altos volúmenes de información puede afectarnos gravemente. Alternativas, bueno pues en realidad depende mucho de lo que se vaya a realizar, pero una muy buena son las TALLY TABLES.

Bueno, y a todo esto que son las TALLY TABLE. Las TALLY TABLE son tablas que cuentan con una sola columna, la cual se recomienda indexar. En esta columna se ingresan números en forma ordenada a partir de 0 o 1, hasta un número que nos sea de utilidad. Ejemplo: si trabajaremos con una rutina que necesite recorrer todos los caracteres de un campo varchar, deberíamos de llenarla del 1 hasta el 8000, que es el número máximo de valores que puede ser ingresado en este campo con este tipo de datos, no es recomendable llenarla de más.

Basta de rollo y vamos a la acción.

Antes de ejemplificar como emplear estas tablas debemos de crearlas y llenarlas. En Internet hay muchos recursos en los cuales están documentados como crear de manera muy eficiente estas tablas, aquí les dejo una, igual y se les ocurre una forma mejor.


Para llenarla podríamos utilizar un ciclo, pero ¿qué no es lo que estamos tratando de evitar? por lo tanto vamos a llenarla utilizando un CROSS JOIN

SELECT TOP 8000 --cantidad de caracteres máximo en un campo varchar
     IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
    Master.dbo.SysColumns sc2

Agregamos el indice cluster a la tabla.

ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100


Listo, ya tenemos nuestra tabla llena, vamos a ponerla en práctica y a compararla con un ciclo normal.

Ejemplo 1

Obtener las posiciones de un caracter en especifico en una cadena

Primero hagamoslo con un ciclo normal


DECLARE @Variable VARCHAR(8000)
SET @Variable = ',Elemento01,Elemento02,Elemento03,Elemento04,Elemento05,'
DECLARE @N INT
SET @N = 1      

WHILE @N <= LEN(@Variable)
  BEGIN
      IF SUBSTRING(@Variable,@N,1) = ','
         BEGIN
               SELECT @N, SUBSTRING(@Variable,@N,1)
         END
           SET @N = @N + 1
  END


Si analizamos el proceso veremos que el select se ejecuta 6 veces, una vez por cada coma que se encuentre en la cadena. Ahora hagamos el ejemplo con una TALLY TABLE.


DECLARE @Variable VARCHAR(8000)
SET @Variable = ',Elemento01,Elemento02,Elemento03,Elemento04,Elemento05,'

 SELECT N, SUBSTRING(@Variable,N,1)
   FROM dbo.Tally
  WHERE N <= LEN(@Variable)
    AND SUBSTRING(@Variable,N,1) = ','
  ORDER BY N


Con esta solución solamente ejecutamos el select una sola vez. La tabla se utiliza para recorrer cada uno de los caracteres del parámetro y despliega las filas que contienen el carácter buscado.

Ejemplo 2

Hagamos con esto algo más interesante, realicemos el split de la cadena separada por comas.

DECLARE @Variable VARCHAR(8000)
SET @Variable = 'Elemento01,Elemento02,Elemento03,Elemento04,Elemento05'
set @Variable = ',' + @Variable + ','

SELECT SUBSTRING(@Variable,N+1,CHARINDEX(',',@Variable,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@Variable)
AND SUBSTRING(@Variable,N,1) = ',' 

Ejemplo 3

Si con este ejemplo no quedamos convencidos les invito a hacer uno con más registros y veremos la magia.

Les voy a ayudar un poco, les dejo el ejemplo con la TALLY TABLE, les queda hacer el ejemplo con un While, que no creo que batallen y comparen el tiempo


DECLARE @Variable VARCHAR(8000)
SET @Variable = 'Elemento01,Elemento02,Elemento03,Elemento04,Elemento05,'
SET @Variable = REPLICATE('Element01,Element02,Element03,Element04,Element05,',159)
set @Variable = ',' + @Variable 
SELECT SUBSTRING(@Variable,N+1,CHARINDEX(',',@Variable,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@Variable)
AND SUBSTRING(@Variable,N,1) = ',' 


¿Muy impresionante no? No tarda ni un segundo, la TALLY TABLE realmente ayuda a realizar más rápido este tipo de procesos. ¿Tienen alguna aplicación para una TALLY TABLE? ponganlo en los comentarios y compartan con los demás.

Espero les haya gustado este post y les sea de utilidad.

Si les gusta compartenlo.

Hasta la próxima semana.

Carlos E. Rodríguez