Tablas+temporales

Tablas temporales vistas desde el SQL Versión 1.0

Índice ÍNDICE............................................................................................................................................................2 INTRODUCCIÓN...........................................................................................................................................3 PROBLEMÁTICA DE LAS TABLAS TEMPORALES...............................................................................4 POSIBLES SOLUCIONES.............................................................................................................................9 TIPOS DE TABLAS TEMPORALES..........................................................................................................11 FUNCIONAMIENTO DE TABLAS TEMPORALES.................................................................................12 OPTIMIZAR EL USO DE TABLAS TEMPORALES................................................................................13 VARIABLES DE TABLA.............................................................................................................................14

METODOLOGÍA DE DOCUMENTACIÓN

PÁGINA 2 DE 15

Introducción En el mundo de las bases de datos, dentro de SQL, es muy común el uso de tablas temporales. A pesar de que todo el mundo sabe que este tipo de estructuras hacen mas lento el funcionamiento de las consultas, los programadores no podemos evitar recurrir a ellas ya que en muchas oportunidades facilitan la resolución de problemas. Almacenar datos para usarlos posteriormente, guardar resultados parciales, analizar grandes cantidades de filas… Hay muchos casos en los que podemos necesitar estas tablas temporales, ¡Pero hay que utilizarlas correctamente!


PÁGINA 3 DE 15

Problemática de las tablas temporales

El consejo que tenemos que seguir a la hora de trabajar con tablas temporales es simple: no usarlas. ¿Y por qué no? Hay un montón de razones que iremos desarrollando a lo largo de este documento, pero para empezar veamos en que se traduce el utilizar una tabla temporal en SQL Server: 

Las tablas temporales se crean en la tempdb, y al crearlas se producen varios bloqueos sobre esta base de datos como por ejemplo en las tablas sysobjects y sysindexes. Los bloqueos sobre la tempdb afectan a todo el servidor.



Como con cualquier tabla:

o

o

o

o



Al crearlas es necesario que se realicen accesos de escritura al disco ( no siempre si las tablas son pequeñas) Al introducir datos en las tablas temporales de nuevo se produce actividad en el disco, y ya sabemos que el acceso a disco suele ser el “cuello de botella” de nuestro sistema. Al leer datos de la tabla temporal hay que recurrir de nuevo al disco. Además estos datos leídos de la tabla suelen combinarse con otros… Al borrar la tabla de nuevo hay que adquirir bloqueos sobre la base de datos tempdb y realizar operaciones en disco.

Al usar tablas temporales dentro de un procedimiento almacenado perdemos la ventaja de tener compilado el plan de ejecución de dicho procedimiento almacenado y se producirán recompilaciones más a menudo. Lo mismo pasará cuando el SQL Server intenta reutilizar el plan de ejecución de una consulta parametrizada. Si en la consulta tenemos una tabla temporal difícilmente se reutilizará dicho plan de ejecución.


PÁGINA 4 DE 15

Sobre este ultimo punto, vamos a mostrar un simple ejemplo que nos va a ayudar a entender mejor el tema, para eso vamos a utilizar el fantastico “trace” del SQL, que nos va a ayudar a mostrar lo que estamos escribiendo. Ejemplo 1:

Vamos a ver un ejemplo simple y alejado de la realidad pero que ilustre lo que queremos explicar en este texto. Vamos a utilizar la base de datos Northwind. En esta base de datos los pedidos se envían a través de tres compañías de trasnportes: Speedy Express(1), United Package(2) y Federal Shipping(3). La compañía Federal Shipping nos oferta realizar todos los envíos que hacemos a través de United Package al precio fijo de 10$. Decidimos que este ahorro merece la pena y vamos a cambiar en nuestra base de datos todos los pedidos que tienen que ser enviados por United Package para que sean enviados a través de Federal Shipping. Para hacer esta actualización de los datos tenemos varias opciones. Vamos a comparar tres formas de hacerlo. (Invertimos el ejemplo para el “Metodo 2”, así vamos a poder ejecutar la prueba seguida sin modificar los datos) Método 1: Tablas temporales

DECLARE @st datetime SET @st =getdate() CREATE TABLE #Actualizar (OrderId int, ShipVia int, Freight money) INSERT INTO #Actualizar SELECT OrderID, ShipVia, Freight FROM Orders WHERE ShipVia=2 UPDATE Orders SET ShipVia=3, Freight=10 WHERE OrderID IN (SELECT OrderID FROM #Actualizar) DROP TABLE #Actualizar PRINT 'Operacion completada en: ' + RTRIM(cast(datediff(ms,@st,getdate()) as char(10))) + ' milisegundos' Resultado: (581 row(s) affected) (581 row(s) affected) Operacion completada en: 110 milisegundos Trace: Text

Event Class

Duration


PÁGINA 5 DE 15

SET @st =getdate() CREATE TABLE #Actualizar (OrderId int, ShipVia int, Freight money) SELECT statman([ShipVia], [OrderID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [ShipVia], [OrderID] FROM [dbo].[Orders] WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [ShipVia],[OrderID]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAX SELECT statman([OrderID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [OrderID] FROM [dbo].[Orders] WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [OrderID]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) INSERT INTO #Actualizar SELECT OrderID, ShipVia, Freight FROM Orders WHERE ShipVia=2 SELECT statman([OrderId],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [OrderId] FROM [dbo]. [#Actualizar________________________ _________________________________ _________________________________ _______________000000003728] WITH(READUNCOMMITTED,SAMPLE 1.0000 UPDATE Orders SET ShipVia=3, Freight=10 WHERE OrderID IN (SELECT OrderID FROM #Actualizar) DROP TABLE #Actualizar PRINT 'Operacion completada en: ' + RTRIM(cast(datediff(ms,@st,getdate()) as char(10))) + ' milisegundos'

SQL:StmtCompleted

0

SQL:StmtCompleted

0

SP:StmtCompleted

0

SP:StmtCompleted

0

SQL:StmtCompleted

0

SP:StmtCompleted

0

SQL:StmtCompleted SQL:StmtCompleted

32 0

SQL:StmtCompleted

0


PÁGINA 6 DE 15

Método 2: Variables tipo tabla

DECLARE @st datetime SET @st =getdate() DECLARE @Actualizar TABLE(OrderId int, ShipVia int, Freight money) INSERT INTO @Actualizar SELECT OrderID, ShipVia, Freight FROM Orders WHERE ShipVia=3 UPDATE Orders SET ShipVia=2, Freight=10 WHERE OrderID IN (SELECT OrderID FROM @Actualizar) PRINT 'Operacion completada en: ' + rtrim(cast(datediff(ms,@st,getdate()) AS char(10))) + ' milisegundos' Resultado: (581 row(s) affected) (581 row(s) affected) Operacion completada en: 30 milisegundos Trace: Text SET @st =getdate() INSERT INTO @Actualizar SELECT OrderID, ShipVia, Freight FROM Orders WHERE ShipVia=3 UPDATE Orders SET ShipVia=2, Freight=10 WHERE OrderID IN (SELECT OrderID FROM @Actualizar) PRINT 'Operacion completada en: ' + rtrim(cast(datediff(ms,@st,getdate()) AS char(10))) + ' milisegundos'

Event Class SQL:StmtCompleted

Duration 0

SQL:StmtCompleted

0

SQL:StmtCompleted

31

SQL:StmtCompleted

0

Método 3: Sin tablas temporales

DECLARE @st datetime SET @st =getdate() UPDATE Orders SET ShipVia=2, Freight=10 WHERE OrderID IN (SELECT OrderID FROM Orders WHERE ShipVia=3 ) PRINT 'Operacion completada en: ' + rtrim(cast(datediff(ms,@st,getdate()) AS char(10))) + ' milisegundos' METODOLOGÍA DE DOCUMENTACIÓN

PÁGINA 7 DE 15

Resultado: (581 row(s) affected) Operacion completada en: 30 milisegundos Trace: Text SET @st =getdate() UPDATE Orders SET ShipVia=2, Freight=10 WHERE OrderID IN (SELECT OrderID FROM Orders WHERE ShipVia=3 ) PRINT 'Operacion completada en: ' + rtrim(cast(datediff(ms,@st,getdate()) AS char(10))) + ' milisegundos'

Event Class SQL:StmtCompleted

Duration 0

SQL:StmtCompleted

31

SQL:StmtCompleted

0

Desde luego este ejemplo no es significativo, y en cada caso hay que estudiar la situación y comparar los resultados obtenidos en un entorno de trabajo para saber cual es la mejor opción. NOTA: Sabemos que la mejor solución para dicha consulta es la siguiente: UPDATE Orders SET ShipVia=3, Freight=10 WHERE ShipVia=2 Lo que se intenta demostrar con estos ejemplos, son las diferencias de tiempos entre las distintas soluciones mostradas. Vistos estos problemas creo que no hace falta repetir nuestro consejo…


PÁGINA 8 DE 15

Posibles soluciones

En lugar de tablas temporales podemos mejorar nuestro código para que no sean necesarias, podemos usar subconsultas (normalmente usar una subconsulta mejora drásticamente el rendimiento respecto a usar tablas temporales), usar tablas permanentes, usar tablas derivadas ya que las mismas se resuelven en memoria y reducen el uso del disco. Hay que recordar siempre que cualquier alternativa es buena si evitamos usar tablas temporales (¡cursores excluidos por supuesto!, ya lo veremos en otra oportunidad)

Vamos a mostrar un ejemplo de cómo remplazar una tabla temporal por una subconsulta. Para ello vamos a utilizar también la base de datos de Northwind. Ejemplo 2: Método 1: Tablas temporales

-- Crea la tabla temporal CREATE TABLE #Temp_Ejemplo ( [CategoryID] INT NOT NULL, [Category_Count] INT NOT NULL ) -- Inserta valores dentro de la tabla temporal INSERT INTO #Temp_Ejemplo (CategoryID, Category_Count) SELECT C.CategoryID, COUNT(*) AS Category_Count FROM Categories C INNER JOIN Products P ON C.CategoryID = P.CategoryID GROUP BY C.CategoryID, C.CATEGORYNAME -- JOINEA la tabla temporal para obtener los valores SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, #Temp_Ejemplo.Category_Count FROM Categories C INNER JOIN Products P ON C.CategoryID = P.CategoryID INNER JOIN #Temp_Ejemplo ON C.CategoryID = #Temp_Ejemplo.CategoryID ORDER BY C.CategoryName -- borra la tabla temporal DROP TABLE #Temp_Ejemplo Método 2: Sin tablas temporales con subconsulta METODOLOGÍA DE DOCUMENTACIÓN

PÁGINA 9 DE 15

-- Reemplaza la tabla temporal con una subconsulta SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, CT.Category_Count FROM Categories C INNER JOIN Products P ON C.CategoryID = P.CategoryID INNER JOIN ( SELECT C.CategoryID, COUNT(*) AS Category_Count FROM Categories C INNER JOIN Products P ON C.CategoryID = P.CategoryID GROUP BY C.CategoryID, C.CategoryName )CT ON C.CategoryID = CT.CategoryID ORDER BY C.CategoryName Las dos consultas, son iguales, devuelven el mismo valor,

De todos modos si alguna vez tenemos que usarlas es mejor conocerlas bien. No se puede generalizar y decir que una consulta con tablas temporales será peor que otra consulta sin tablas temporales que extrae la misma información, lo ideal es siempre tener alternativas, revisar su plan de ejecución y sobre todo probar eso nos dará la experiencia necesaria para poder saber cuando usar un tipo de sentencia.


PÁGINA 10 DE 15

Tipos de tablas temporales

Las tablas temporales son de dos tipos en cuanto al alcance la tabla. Tenemos tablas temporales locales y tablas temporales globales.

#locales Las tablas temporales locales tienen una # como primer carácter en su nombre y sólo se pueden utilizar en la conexión en la que el usuario las crea. Cuando la conexión termina la tabla temporal desaparece. Por lo tanto se admiten tablas con el mismo nombre pero en distinta conexión aunque el user sea el mismo. Sugerir la creación y mostrar el nombre automático que le asigna a la tabla.

##globales Las tablas temporales globales comienzan con ## y son visibles por cualquier usuario conectado al SQL Server. Y una cosa más, estas tablas desaparecen cuando ningún usuario está haciendo referencias a ellas, no cuando se desconecta el usuario que la creo.

Temp Realmente hay un tipo más de tablas temporales. Si creamos una tabla dentro de la base de datos temp es una tabla real en cuanto a que podemos utilizarla como cualquier otra tabla en cualquier base de datos, y es temporal en cuanto a que desaparece en cuanto apagamos el servidor.


PÁGINA 11 DE 15

Funcionamiento de tablas temporales

Crear una tabla temporal es igual que crear una tabla normal. Veámoslo con un ejemplo: CREATE TABLE #TablaTemporal (Campo1 int, Campo2 varchar(50))

Y se usan de manera habitual. INSERT INTO #TablaTemporal VALUES (1,’Primer campo’) INSERT INTO #TablaTemporal VALUES (2,’Segundo campo’) SELECT * FROM #TablaTemporal

Como vemos no hay prácticamente limitaciones a la hora de trabajar con tablas temporales (una limitación es que no pueden tener restricciones FOREING KEY) NOTA: La deja crear pero con un warning


PÁGINA 12 DE 15

Optimizar el uso de tablas temporales

El uso que les podemos dar a este tipo de tablas es infinito, pero siempre teniendo en cuenta unas cuantas directivas que debemos seguir para que ralenticen nuestro trabajo lo menos posible. Por ejemplo no es mala costumbre crear las tablas temporales con comandos DDL como en el ejemplo anterior (CREATE TABLE) y luego rellenarlas comandos INSERT o con INSERT INTO. Es cierto que eso mismo lo podemos lograr en un único paso con SELECT INTO, pero esta opción es peor porque los bloqueos que se adquieren sobre objetos del sistema duran más tiempo. Como siempre es mejor pedir los campos que queremos y no poner el típico SELECT * FROM... De la misma manera es muy recomendable cualificar los registros que queremos y no tener registros que no vamos a utilizar en tablas temporales. Otra buena costumbre es borrar nosotros nuestras tablas. Sí que es cierto que al terminar la conexión las tablas temporales locales desaparecen, pero si tenemos un conjunto de sentencias largo y creamos una tabla temporal al principio y no la vamos a utilizar en el resto del tiempo no tiene sentido tener esa tabla ahí ocupando espacio y memoria. Si las tablas temporales son grandes una opción para aumentar el rendimiento es crear un índice que nos ayude a recuperar los datos de esa tabla (para tablas pequeñas es un gasto inútil porque nunca se usarán los índices). Colocar la base de datos tempdb en un disco dedicado solo para esta función aumentará el rendimiento global del sistema si se hace un uso intensivo de tablas temporales. Y por último pero no menos importante, no crear tablas temporales dentro de transacciones ni dentro de triggers… la concurrencia de nuestra base de datos sufrirá mucho si se utilizan.


PÁGINA 13 DE 15

Variables de tabla

Con SQL Server 2000 podemos declarar variables de tipo TABLE. Este tipo de variables tienen una serie de ventajas sobre las tablas temporales por lo que siempre que podamos escogeremos usar variables de tabla frente a tablas temporales. Usar variables temporales es sencillo: DECLARE @VariableTabla TABLE (Campo1 int, Campo2 char(50)) INSERT INTO @VariableTabla VALUES (1,'Primer campo') INSERT INTO @VariableTabla VALUES (2,'Segundo campo') SELECT * FROM @VariableTabla

Ventajas que encontraremos al usar variables de tipo tabla: 

Tienen un ámbito bien definido. El procedimiento almacenado, la función o el batch en el que se declaran.



Las variables de tipo tabla producen menos recompilaciones de los procedimientos almacenados en los que se encuentran que si utilizamos tablas temporales.



Las variables de tabla no necesitan de bloqueos ni de tantos recursos como las tablas temporales.



Lo mas notorio es que las variables del tipo tabla apuntan a estructuras en memoria con lo cual producen menos overhead que las tablas temporales

Las variables del tipo tabla al ser siempre locales al proceso que las creo no se pueden pasar directamente como parámetros de un stored Pero también tienen inconvenientes: 

No podemos cambiar la definición de la tabla una vez declarada



No podemos utilizar índices que no sean agrupados



No se pueden utilizar en INSERT INTO ni en SELECT INTO



No podemos utilizar funciones en las restricciones

NOTA:


PÁGINA 14 DE 15

Si ponemos en una balanza las ventajas y los inconvenientes vemos que en general es mejor utilizar las variables de tipo tabla que las tablas temporales. Solo en el caso de tener gran cantidad de datos en una tabla temporal y si la vamos a usar varias veces es preferible la opción de tablas temporales porque en ellas podemos definir índices. Espero que esto nos sirva al menos para conocer un poco mejor a las “tablas temporales”. NOTAS RELACIONADAS: http://www.sql-server-performance.com/jg_derived_tables.asp


PÁGINA 15 DE 15

Tablas+temporales

Recommend Documents