Existen diferencias importantes al diseñar un procedure con tablas temporales. El tiempo de ejecución puede variar considerablemente si seleccionamos incorrectamente el tipo de tabla a utilizar.

Si creamos una tabla temporal dentro de un mismo batch, sql server debe recompilar el query, ya que desconoce la definición de la tabla en la primera compilación, este no es el caso para una tabla temporal y es una de las razones por la que introdujo en la versión 2005 (para reducir las recompilaciones).

El echo de evitar recompilar un procedure no siempre significa que su tiempo de ejecución va a disminuir, tal vez puede ayudarnos cuando tenemos un sistema altamente transaccional, donde los registros de las tablas no son muy grandes y el procedure de ejecuta varias veces por segundo, en este caso posiblemente funcione mucho mejor con una tabla tipo variable, pero como dije anteriormente "Siempre debemos probar las dos opciones".
Voy a exponer un ejemplo donde en mis pruebas el mismo query con una tabla temporal, se ejecuto en menos de 1 segundo y con una tabla variable 1 minuto 20 segundos. Como pueden ver sus tiempos de ejecución son completamente diferentes.

Primero vamos a crear una tabla e insertar 20,000 registros en ella y anexamos un índice clustered.

CREATE TABLE Prueba(ID int)
DECLARE @i int
SET @i = 0
SET NOCOUNT ON
WHILE @i < 20000
BEGIN
INSERT INTO Prueba (ID) Values (@i)
SET @i = @i + 1
END


CREATE CLUSTERED INDEX IX1 ON dbo.Prueba (ID)

Ahora expongo los dos queries a ejecutar, donde si generamos su plan de ejecución estimado, vamos a poder observar que son iguales. Query 1

DECLARE @Tmp1 TABLE (ID int)
INSERT INTO @Tmp1(ID)
SELECT ID
FROM Prueba
SELECT *
FROM Prueba 
WHERE ID NOT IN (SELECT ID FROM @Tmp1)

Query 2

CREATE TABLE #Tmp1(ID int)
INSERT INTO #Tmp1(ID)
SELECT ID
FROM Prueba
SELECT *
FROM Prueba
WHERE ID NOT IN (SELECT ID FROM #Tmp1)
DROP TABLE #Tmp1

El primer query crea una tabla variable mientras que el segundo una tabla temporal. Si ejecutamos los dos queries habilitando el plan de ejecución actual, podremos observar que cambia completamente el plan para el query con la tabla temporal, y este utiliza un Hash Match en lugar del Nested Loop del query 1. Que podemos concluir con esto, que debemos hacer uso de tablas variables cuando estamos seguros que el plan de ejecución que se genera es el más adecuado y queremos evitar las recompilaciones. Pero siempre es mejor probar de las dos maneras antes de decidirnos por una de ellas.

Algunas de las limitantes de una tabla tipo variable son las siguientes:

-No se puede hacer uso de ellas en esta situación:

INSERT @table EXEC sp_Procedure SELECT * INTO @table FROM Table 

-No se puede truncar -No pueden modificarse una vez que están creadas.
- No podemos agregar un índice a menos que sea por medio de una llave primaria o como constraints unicos.

DECLARE @myTable TABLE 
( 
    CPK1 int, 
    CPK2 int, 
    PRIMARY KEY (CPK1, CPK2) 
)

- No podemos usar funciones definidas por el usuario (UDF) en un check constraint, columna calculada o default constraint.
- No podemos usar tipos de datos de usuario (UDT)
- No podemos eliminar la tabla una vez creada
- No podemos generar una tabla tipo variable al vuelo.

SELECT * INTO @tableVariable 
 
Server: Msg 170, Level 15, State 1, Line 1 
Line 1: Incorrect syntax near '@tableVariable'

- No se crean estadísticas.
- La tabla variable siempre tiene cardinalidad 1, porque no existe en su compilación.
- Las tablas no se ven en procedures anidados

En este ejemplo expuse los problemas de hacer uso de tablas tipo variable, pero en muchos casos como en sistemas altamente transaccionales que contienen pocos registros en las tablas involucradas, el tiempo de ejecución es mucho menor en tablas tipo variable que en tablas temporales.

 

You are not authorised to post comments.

Comments powered by CComment

DBASupport - Foro

Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...

Estadisticas en la base de datos
Buenos días, estoy teniendo problemas de performance en mis queries y quisiera saber como puedo validar que mis tablas tienen las estadisticas...
URGE AYUDA
  • Topic started by
  • in General
  • 15 years ago.
  • 15 years ago.
  • 30 replies
HOLA ESPERO ME PUEDAN AYUDAR. TEN GO UNA TABLA CON una estructura similar a la siguiente: id_mov cargo subtotal total...

Duada de Memoria de SQL Server 2005
  • Topic started by Erzengel
  • in General
  • 11 years ago.
  • 11 years ago.
  • 30 replies
Buen Día Recurro nuevamente a su sabiduría. Quiero saber el porque tiene un tope SQL Serever de 2GB en Windows server 2003 de 32 bit y si alguien...

SqlDateTime overflow
  • Topic started by
  • in General
  • 15 years ago.
  • 15 years ago.
  • 11 replies
Alguno de ustedes ha tenido este problema: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.' Les...

problemas para iniciar el agente sql
  • Topic started by acgch
  • in General
  • 14 years ago.
  • 14 years ago.
  • 11 replies
Recientemente para asegurar el sql server 2005, cree una cuenta sql con derechos de sysadmin y borre el grupo BUILTIN\Administradores para evitar...

Problemas para levantar mirror
  • Topic started by acgch
  • in General
  • 14 years ago.
  • 14 years ago.
  • 16 replies
Hola amigos, tengan un buen día, les comento que estoy configurando mirror siguiendo los pasos a través del wizard del managment studio y también...
Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...

Estadisticas en la base de datos
Buenos días, estoy teniendo problemas de performance en mis queries y quisiera saber como puedo validar que mis tablas tienen las estadisticas...
Deshabilitar todos los jobs
  • Topic started by agamez
  • in Jobs
  • 3 years ago.
  • 3 years ago.
  • 1 reply
Buenos días, alguien sabe como puedo deshabilitar rapidamente todos los jobs de SQL Server, gracias.

Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...