Tablas temporales vs tablas in-memory

Con la llegada de SQL Server 2014 y las tablas in-memory cabe preguntarse si podrán dichas tablas sustituir escenarios típicos donde se utilizan actualmente tablas temporales. En nuestra experiencia en consultoría vemos habitualmente como las tablas temporales se utilizan  como contenedores de resultados intermedios dentro de procesos más complejos. En estos casos se suele o bien crear la tabla temporal de forma explícita con un CREATE TABLE o bien se crea al vuelo mediante el uso de la estructura SELECT INTO.

Con las nuevas tablas in-memory tenemos la posibilidad de definir tablas cuya persistencia sea únicamente de esquema. Es decir, la información que grabemos en ellas nunca se materializará en disco, trabajarán únicamente en memoria. Este tipo de tablas considerarlas como una alternativa más a las tablas temporales tradicionales y usarlas en escenarios en los que la no persistencia de los datos en caso de caída del servicio no es un problema.

Para complementar el abanico de posibilidades que tenemos, añadiremos también otro escenario que consiste en almacenar tempdb en memoria volátil. Este sería el caso al utilizar un disco RAM o ciertos aceleradores basados en memoria RAM/flash conectados al bus PCI-E. En definitiva lo que buscamos es minimizar el impacto que tendría un sistema lento de entrada/salida sobre el rendimiento de tempdb. De esta forma podremos evaluar si tempdb, por diseño, nos limita considerablemente el rendimiento o es el sistema de entrada/salida el mayor limitante.

Durante las pruebas que hemos realizado hemos tenido en cuenta otros factores como por ejemplo el que la tabla deba crearse y destruirse cada vez o no o que los datos sean aleatorios o no. El objetivo es determinar si determinados patrones se ajustan o no a cada uno de los métodos que hemos comentado y si existe, en el caso de las tablas in-memory, de un impacto sustancial por colisiones de hash.

Comenzamos analizando el impacto que tiene la creación y destrucción del objeto temporal. El siguiente gráfico nos muestra como el coste en el caso de la tabla in-memory es muy superior al coste de crear una tabla temporal tradicional.

El coste fijo que tenemos que asumir por crear una tabla in-memory es de aproximadamente 1200 ms en mi equipo de prueba, lo cual hace la alternativa in-memory poco válida si tenemos que crear y destruir el objeto frecuentemente. Podemos ver también que el rendimiento del método SELECT INTO se hace más patente a medida que el número de filas aumenta gracias a las optimizaciones que el motor puede hacer en este caso.

Vamos a considerar en adelante que el objeto puede mantenerse creado permanentemente o que la frecuencia de creación/destrucción va a ser muy baja respecto al número de accesos.

El siguiente gráfico nos muestra los tiempos medios en función del tamaño de fila (100 bytes o 1000 bytes) y el número de filas insertadas:

Podemos ver como en todos los casos el SELECT INTO es la alternativa más rápida, seguida por las tablas in-memory y dejando en última posición la creación explícita de la tabla temporal y la posterior inserción de datos.

El siguiente gráfico muestra las duraciones según el tamaño de fila para los dos métodos de tablas temporales “clásicas” tanto con tempdb en RAM como en disco:

Podemos ver que realmente las diferencias no son apreciables para volúmenes pequeños y únicamente en el caso de que estemos declarando la tabla temporal de forma explícita y realizando un insert posteriormente notamos diferencias de rendimiento. Debemos tener en cuenta que estas pruebas se han realizado en un sistema que no tiene problemas de congestión de entrada/salida a disco. Si hubiésemos realizado estas pruebas en uno de los muchos servidores en los que la entrada/salida está infradimensionada el resultado sería muy distinto con seguridad.

La conclusión de este punto es que salvo que tengas un mal rendimiento de disco o necesites insertar millones de filas en tus tablas temporales el situar tempdb en un disco RAM o disco muy rápido no va a marcar una diferencia notable en el rendimiento.

Por último he querido comparar, para el caso de tablas in-memory, el impacto del uso de datos aleatorios (que minimizan las colisiones de hash) versus el uso de datos idénticos:

Los datos obtenidos indican que para pequeñas cantidades de filas, el impacto de las colisiones no es significativo. Únicamente en el caso de las inserciones de 1 millón de filas con tamaño de fila de 1000 bytes se aprecia una degradación si los datos no son aleatorios.

Si consideramos el volumen de bytes insertados (tamaño de fila multiplicado por el número de fila) podemos ver como para tablas de 1 MB o menos las diferencias son ínfimas pero a partir de 10 MB se van separando claramente cada alternativa, siendo la mejor en todos los casos el SELECT INTO, el intermedio la tabla in-memory y el peor la tabla temporal explícita más el insert:

Por último hemos querido medir también el rendimiento de un SELECT count (*) sobre las tablas en función del método utilizado. El siguiente gráfico muestra como en todos los casos la tabla temporal clásica es más rápida que la tabla in-memory:

A modo de resumen, destacaría las siguientes conclusiones y matizaciones:

• El método más eficiente en general para almacenar el resultado de una consulta de forma temporal es el SELECT INTO. El funcionamiento es bueno en todos los casos, para conjuntos pequeños de filas, grandes, con filas anchas o estrechas.
• Las operaciones tanto de inserción como de consulta en T-SQL nativo sobre tablas temporales resultan más eficientes con tablas temporales clásicas que con tablas in-memory.
• Si nuestro proceso completo se puede codificar en un procedimiento almacenado nativo donde únicamente accedemos a tablas in-memory el rendimiento con muy alta probabilidad será mejor. Por desgracia este es un escenario muy limitado y no puede considerarse como el caso habitual del uso de tablas temporales.
• En ocasiones se requiere crear indexación adicional sobre la tabla temporal. En estos casos en función del número de filas insertadas, el número de índices, etc. puede ser más eficiente definir primero la tabla con los índices o bien añadirlos posteriormente a la inserción de los datos.
• El rendimiento de tempdb se puede ver muy mermado si la entrada/salida a disco está muy saturada. Este no es el escenario que se muestra en las pruebas pero sí es un escenario que desgraciadamente nos encontramos en muchos entornos de producción. En estos casos aliviar esta saturación moviendo tempdb a un sistema no saturado o a un disco RAM puede dar grandes beneficios desde el punto de vista de rendimiento percibido por el usuario.
• Las colisiones hash en “tablas in-memory temporales” tienen un impacto bastante reducido, no siendo significativo salvo cuando el volumen es del orden de millones de filas. En un escenario de tablas con persistencia y/o con concurrencia de distintos procesos el impacto de las colisiones puede ser mucho más significativo.
• Las pruebas se han realizado sobre una versión CTP de SQL Server 2014, no sobre la versión final de SQL Server 2014 por lo que los resultados pueden variar en la versión final.