Investigando un poco por la Web y a raíz de diferentes posiciones de clientes con respecto al uso o no de tablas temporales, cursores y variables de tipo tabla en Sql Server, me percaté de que allí también existen opiniones muy variadas, por lo que decidí darme a la tarea de investigar un poco por mi cuenta y hacer mis propias pruebas. Para los que no saben les cuento que las tablas temporales se almacenan en una base de datos de sistema llamada TempDB, en cuanto a las variables tipo tabla y los cursores se almacenan en memoria hasta cierto límite, para luego, en caso de que sea necesario utilizar igualmente la base de datos TempDB.
La prueba consistió en lo siguiente, creé una tabla con cuatro columnas, nada especial, de hecho tomé una tabla de pruebas que ya existía en la base de datos:
CREATE TABLE Test(
Col1 int NULL,
Col2 int NULL,
Col3 numeric(16, 2) NULL,
Col4 int NULL
) ON PRIMARY
E inserté ciertos valores, probé con cantidades tan variadas como 10.000 o 700.000 registros
DECLARE @i decimal(8), @j decimal(8)
SET @i = 1
SET @j = 300000
WHILE @i <= @j
BEGIN
INSERT INTO Test(Col1, Col2, Col3, Col4) VALUES(1,10,500,8)
SET @i = @i + 1
END
La prueba básica consistió en realizar lo que más comúnmente hacemos con este tipo de objetos, que es crear la estructura, llenarla y procesarla obteniendo la información de la misma.
Primero hice pruebas con tablas temporales y variables tipo tabla, en teoría lo recomendable es utilizar variables tipo tabla con cantidades pequeñas de registros y tablas temporales con cantidades más grandes, me sorprendió que en todos los casos fue más veloz el uso de tablas temporales, en el peor de los casos duró 22 seguros, mientras que el uso de variables tipo tabla duró 29. Si bien es cierto la diferencia no es mucha comparativamente hablando, si fue un comportamiento que siempre se dio:
-- 22
SELECT * INTO #Test FROM Test
SELECT * FROM #Test
DROP table #Test
-- 29
DECLARE @Test TABLE (
Col1 int NULL,
Col2 int NULL,
Col3 numeric(16, 2) NULL,
Col4 int NULL)
INSERT @Test SELECT * FROM Test
SELECT * FROM @Test
Para constatar que no estubiera afectando el hecho de que con el uso de tablas temporales se estaba omitiendo la creación explícita de la misma, además de la forma de llenado, también hice las mismas pruebas con un tabla temporal, pero creándola explícitamente y llenándola con una cláusula INSERT-SELECT en vez del SELECT-INTO, utilizado en el ejemplo anterior. En este caso los tiempos de respuesta fueron prácticamente iguales que los arrojados con la prueba de la variable tipo tabla
-- 29
CREATE TABLE #Test (
Col1 int NULL,
Col2 int NULL,
Col3 numeric(16, 2) NULL,
Col4 int NULL)
INSERT #Test SELECT * FROM Test
SELECT * FROM #Test
DROP table #Test
Para enriquecer aun más la prueba, hice lo mismo con tablas permanentes. En este caso, el peor escenario duró más que el procesamiento de la tabla temporal (42 segundos), pero le di el beneficio de la duda al haberse creado en la base de datos actual, misma que para este efecto en particular estaba más saturada que TempDB, que es donde se almacenan las tablas temporales, por lo que hice otra prueba en donde creaba la tabla permanente en la misma base de datos de las tablas temporales y el comportamiento fue básicamente el mismo de estas con un tiempo en el peor caso de 30 segundos:
-- 42
CREATE TABLE Test2 (
Col1 int NULL,
Col2 int NULL,
Col3 numeric(16, 2) NULL,
Col4 int NULL)
INSERT Test2 SELECT * FROM Test
SELECT * FROM Test2
DROP table Test2
-- 30
CREATE TABLE tempdb.dbo.Test2 (
Col1 int NULL,
Col2 int NULL,
Col3 numeric(16, 2) NULL,
Col4 int NULL)
INSERT tempdb.dbo.Test2 SELECT * FROM Test
SELECT * FROM tempdb.dbo.Test2
DROP table tempdb.dbo.Test2
Este conviene traer a la reflexión lo que muchas veces pasa con las tablas temporales, como ya se mencionó se almacenan en TempDB, demás de otros objetos, como variables, cursores, objetos internos del motor, versiones de filas generadas por las transacciones, etc. En la medida en que se utilicen en exceso este tipo de objetos, dicha base de datos puede ver afectado su rendimiento, en el ejemplo que hice pasó lo inverso, porque en dicho servidor se utiliza muy poco la base de datos TempDB, pero la base de datos del sistema si tendía a sobrecargarse. Muchas veces se sataniza el uso de tablas temporales, pero al igual que todo, es cuestión de moderación.
Para terminar la prueba faltaba el caso de los recorridos, los cursores siempre han sido muy criticados por causar latencia, esto es cierto por lo que en la manera de los posible hay que evitar su uso, lamentablemente no siempre es ta sencillo evitar usarlos, por ejemplo en casos en que hay que realizar algún tipo de cálculo por cada registro. Las pruebas se realizaron con los mismos escenarios de las anteriores y por lo general rondó el doble o más de que tardaba el proceso con tablas temporales, en el peor escenario duró 61 segundos:
-- 61
DECLARE @Col1 int, @Col2 int, @Col3 numeric(16, 2), @Col4 int
DECLARE cursor_test CURSOR FOR
SELECT *
FROM Test
OPEN cursor_test
FETCH NEXT FROM cursor_test INTO @Col1, @Col2, @Col3, @Col4
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cursor_test INTO @Col1, @Col2, @Col3, @Col4
END
CLOSE cursor_test
DEALLOCATE cursor_test
Para mostrar la alternativa a los cursores realicé otro caso con un recorrido a una variable de tipo tabla, a través de una lógica que me encontré en más de un sitio, a razón de ser sincero al ver dicho código no me parecía que fuera muy eficiente, de hecho esperaba una forma menos artesanal de llevar a cabo dicha labor, pero no encontré ninguna, sin embargo viendo un poco los comentarios de los artículos esperaba alguna sorpresa, sin embargo no fue así, de hecho no puedo decir cuánto duró el peor escenario porque no tuve la paciencia de esperar, pero tardó más de 6 minutos. Para poder ver resultados, con el fin de constatar que la lógica estubiera bien tuve que probar con 10.000 registros arrojando una duración de desesperantes 15 segundos, contra un tiempo menor a un segundo con los clásicos cursores.
-- > 300
DECLARE @rowId int, @maxRowId int
DECLARE @Col1 int, @Col2 int, @Col3 numeric(16, 2), @Col4 int
DECLARE @Test2 TABLE (
rowId int identity(1,1),
Col1 int NULL,
Col2 int NULL,
Col3 numeric(16, 2) NULL,
Col4 int NULL)
INSERT @Test2 SELECT * FROM Test
SELECT
@rowId = min(RowId),
@MaxRowId = max(RowId)
FROM @Test2
WHILE @RowId <= @MaxRowId BEGIN
SELECT @Col1 = Col1, @Col2 = Col2,
@Col3 = Col3, @Col4 = Col4
FROM @Test2
WHERE RowId = @RowId
SET @RowId = @RowId + 1
END
