Tablas Temporales vs Variables Tipo Tabla

Desde que era programador pensé que las variables de tipo tabla eran mejores que las tablas temporales porque alguna vez escuché que las primeras eran tablas que vivían en la memoria, mientras que las tablas temporales almacenaban sus datos en disco. Sin embargo, este es un mito que debe ser desterrado ya que la verdad es completamente opuesta y a continuación lo vamos a comprobar.

Primero vamos a demostrar que ambos tipos de tabla se guardan en la base de datos de sistema “tempdb” y por ende ninguna de ellas es solamente almacenada en la memoria RAM. Para esto vamos a consultar la cantidad de lecturas que tienen los datafiles de la “tempdb”, luego crearemos una tabla temporal o variable tipo tabla donde insertaremos algunas filas de prueba que luego retornaremos, sin antes ejecutar un comando DBCC que limpiara nuestro cache de datos, y finalmente volveremos a consultar las estadísticas de los datafiles para verificar cuantas lecturas se necesitaron para efectuar las operaciones realizadas.

Tabla Temporal:

select
DB_NAME(mf.database_id)
,sum(fs.num_of_reads) as total_reads
from sys.master_files mf
cross apply sys.dm_io_virtual_file_stats(mf.database_id,NULL) fs
where mf.database_id = 2
and mf.type_desc = 'ROWS'
group by mf.database_id
GO
CREATE TABLE #temp1 (col1 int)
insert into #temp1 select database_id from sys.databases
DBCC DROPCLEANBUFFERS
select count(*) from #temp1
DROP TABLE #temp1
GO
select
DB_NAME(mf.database_id)
,sum(fs.num_of_reads) as total_reads
from sys.master_files mf
cross apply sys.dm_io_virtual_file_stats(mf.database_id,NULL) fs
where mf.database_id = 2
and mf.type_desc = 'ROWS'
group by mf.database_id
GO

2018-08-24 17_46_40

Variable Tipo Tabla:

select
DB_NAME(mf.database_id)
,sum(fs.num_of_reads) as total_reads
from sys.master_files mf
cross apply sys.dm_io_virtual_file_stats(mf.database_id,NULL) fs
where mf.database_id = 2
and mf.type_desc = 'ROWS'
group by mf.database_id
GO
DECLARE @temp1 TABLE (col1 int)
insert into @temp1 select database_id from sys.databases
DBCC DROPCLEANBUFFERS
select count(*) from @temp1
GO
select
DB_NAME(mf.database_id)
,sum(fs.num_of_reads) as total_reads
from sys.master_files mf
cross apply sys.dm_io_virtual_file_stats(mf.database_id,NULL) fs
where mf.database_id = 2
and mf.type_desc = 'ROWS'
group by mf.database_id
GO

2018-08-24 17_48_12

Como se puede observar, en ambos casos se realizaron 24 lecturas de los datafiles de la tempdb, lo cual demuestra que ambas tablas se guardan en disco.

Ahora, ¿por qué digo que las tablas temporales son mejores que las variables tipo tabla en cuanto a la performance? Bueno la principal razón es que las tablas temporales guardan estadísticas, mientras que las variables tipo tabla no tienen estadísticas, entonces como ya se conoce, las estadísticas son cruciales para que SQL Server pueda elaborar planes de ejecución que sean óptimos, por lo que si usamos variables tipo tabla normalmente obtendremos planes de ejecución poco eficientes.  En mi experiencia personal, el solo cambiar el uso de las variables tipo tabla por tablas temporales ha mejorado considerablemente la ejecución de código Transac-SQL.

Tabla Temporal:

creamos tabla temporal 1 y la llenamos
select object_id, name
into #tmp1
from sys.all_objects
creamos tabla temporal 2 y la llenamos
select object_id, column_id, name
into #tmp2
from sys.all_columns
SET STATISTICS IO ON
obtenemos los datos
select t1.name, count(t2.column_id)
from #tmp1 t1
left join #tmp2 t2
on t1.object_id = t2.object_id
group by t1.name
SET STATISTICS IO OFF

La cantidad de lecturas realizada para ejecutar la última consulta es la siguiente:

2018-08-24 18_09_17

Esto se dio debido a que como las tablas temporales guardan estadísticas, SQL Server sabía que para obtener los datos eficientemente debía ejecutar el join utilizando un operador “Hash Match”. Revisemos el plan de ejecución:

2018-08-24 18_13_32

Variables Tipo Tabla:

creamos las variables tipo tabla
DECLARE @tmp1 TABLE (object_id int, name sysname)
DECLARE @tmp2 TABLE (object_id int, column_id int, name sysname)
insertamos la informacion en las variables tipo tabla
insert into @tmp1
select object_id, name
from sys.all_objects
insert into @tmp2
select object_id, column_id, name
from sys.all_columns
obtenemos los datos
SET STATISTICS IO ON
select t1.name, count(t2.column_id)
from @tmp1 t1
left join @tmp2 t2
on t1.object_id = t2.object_id
group by t1.name
SET STATISTICS IO OFF

La cantidad de lecturas realizadas por la última consulta es la siguiente:

2018-08-24 18_19_59

Tenemos más de 140 mil lecturas comparadas con las menos de 100 lecturas obtenidas con las tablas temporales. Ahora veremos en el plan de ejecución que la razón de la marcada diferencia es por la falta de estadísticas donde el número estimado de filas es muy diferente al número real, ya que las variables tipo tabla al no tener estadísticas su estimado de filas siempre es “1”.

2018-08-24 18_24_29

Espero este post haya ayudado a entender la diferencia entre ambos tipos de tablas temporales, sin embargo hay otras diferencias entre ellas las cuales revisaremos en otro post. Como conclusión final debemos llevarnos que es recomendable 100% utilizar tablas temporales en vez de variables tipo tabla dentro del código Transac-SQL.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Salir /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Salir /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Salir /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Salir /  Cambiar )

Conectando a %s