Por qué no debería indexar sus tablas temporales.

Buenos días amigos !! en la entrada de hoy veremos el por qué no deberíamos indexar nuestras tablas temporales. Cuando tiene un proceso que usa tablas temporales y desea acelerarlo, puede ser tentador indexar la tabla temporal para ayudar a que el trabajo se realice más rápidamente. Sin embargo, en la mayoría de los casos, no en todos, pero en la mayoría, es una mala idea…

CREATE OR ALTER PROC dbo.usp_TempTable_Heap 
     @DisplayName NVARCHAR(40) AS
     BEGIN
     CREATE TABLE #MyUsers (Id INT, DisplayName NVARCHAR(40));
     INSERT INTO #MyUsers (Id, DisplayName)
         SELECT Id, DisplayName
         FROM dbo.Users;
 SELECT Id, DisplayName FROM #MyUsers WHERE DisplayName = @DisplayName; END
 GO

La primera declaración carga alrededor de 10 millones de filas en una tabla temporal, y la segunda declaración solo extrae las filas que coinciden con lo que estamos buscando. Activaremos las estadísticas de tiempo para poder tener una idea aproximada de dónde pasa el tiempo SQL Server: la primera declaración o la segunda:

SET STATISTICS TIME ON;
EXEC dbo.usp_TempTable_Heap N'Gpsos';

No suelo usar estadísticas de tiempo aquí en el blog, así que una explicación rápida: en la pestaña Mensajes en SSMS, obtienes una línea para cada declaración en el lote, más un total:

SQL Server Execution Times:
    CPU time = 4453 ms,  elapsed time = 4458 ms.
 SQL Server Execution Times:
    CPU time = 1031 ms,  elapsed time = 1017 ms.
 SQL Server Execution Times:
    CPU time = 5484 ms,  elapsed time = 5477 ms.

La primera declaración, cargar la tabla temporal, tomó aproximadamente 4.5 segundos.

La segunda declaración, encontrar a Gpsos, tomó alrededor de un segundo.

¿Podría un índice en DisplayName acelerar la segunda consulta?

Para averiguarlo, agreguemos una nueva versión de nuestro procedimiento almacenado, esta vez uno que crea un índice en DisplayName antes de que se carguen nuestros datos:

CREATE OR ALTER PROC dbo.usp_TempTable_IndexAddedBeforeLoad
     @DisplayName NVARCHAR(40) AS
     BEGIN
     CREATE TABLE #MyUsers (Id INT, DisplayName NVARCHAR(40));
     CREATE INDEX DisplayName ON #MyUsers(DisplayName); /* THIS IS NEW */
     INSERT INTO #MyUsers (Id, DisplayName)
         SELECT Id, DisplayName
         FROM dbo.Users;
 SELECT Id, DisplayName FROM #MyUsers WHERE DisplayName = @DisplayName; END
 GO

Ahora, cuando ejecutamos la nueva consulta:

EXEC dbo.usp_TempTable_IndexAddedBeforeLoad N'Gpsos';

Las estadísticas de tiempo pintan una imagen espantosa:

SQL Server Execution Times:
    CPU time = 56594 ms,  elapsed time = 56772 ms.
 SQL Server Execution Times:
    CPU time = 0 ms,  elapsed time = 75 ms.
 SQL Server Execution Times:
    CPU time = 56594 ms,  elapsed time = 56850 ms.

Claro, la segunda declaración cae de 1.017 milisegundos a 75, pero… ¡¿a quién le importa?!?! La sobrecarga adicional de crear el índice es mucho, mucho mayor, lo que hace que la consulta tarde diez veces más en general.

Parte del problema es que nuestro montón (tabla) debe cargarse primero, y luego los datos deben ordenarse por DisplayName, y luego debe crearse un índice en DisplayName. No podemos hacer ambos en paralelo al mismo tiempo porque el índice no agrupado debe poder apuntar a una fila específica en el montón, y para hacer eso, necesitamos su ubicación física.

Multa. ¿Qué pasa con un índice agrupado?

Podemos reducir la sobrecarga del proceso al tener solo una estructura para almacenar los datos. En lugar de un montón más un índice no agrupado en DisplayName, podemos simplemente definir una estructura única para la tabla temporal: un índice agrupado en DisplayName. No puede ser un índice único porque varios usuarios comparten el mismo DisplayName, pero está bien. Aquí vamos:

Por qué no debería indexar sus tablas temporales.

CREATE OR ALTER PROC dbo.usp_TempTable_ClusteredIndex
     @DisplayName NVARCHAR(40) AS
     BEGIN
     CREATE TABLE #MyUsers (Id INT, DisplayName NVARCHAR(40));
     CREATE CLUSTERED INDEX DisplayName ON #MyUsers(DisplayName); /* THIS IS NEW */
     INSERT INTO #MyUsers (Id, DisplayName)
         SELECT Id, DisplayName
         FROM dbo.Users;
 SELECT Id, DisplayName FROM #MyUsers WHERE DisplayName = @DisplayName; END
 GO

Y cuando lo ejecutamos:

Por qué no debería indexar sus tablas temporales

Al principio, parece mucho más rápido que el último método:

SQL Server Execution Times:
    CPU time = 42107 ms,  elapsed time = 18071 ms.
 SQL Server Execution Times:
    CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
    CPU time = 42107 ms,  elapsed time = 18145 ms.

El tiempo de ejecución se redujo de 57 a 18 segundos, pero hay una trampa. Una gran parte de la razón por la que es más rápido es que ahora la consulta va en paralelo. Tenga en cuenta que el tiempo de CPU es mayor que el tiempo transcurrido; esa es su pista de que la consulta se realizó en paralelo a través de más núcleos de CPU. Ahora vamos a tener un problema de CPU si se ejecutan varias de estas consultas simultáneamente.

Y todavía no es tan rápido como nuestra solución original, el montón.

Entonces, ¿cómo hacemos que las tablas temporales sean más rápidas?

Solo cárguelos con los datos que realmente necesita. Cuando cargue tablas temporales, o cualquier objeto, en realidad, sea despiadado al filtrar lo antes posible.

Si solo va a acceder a los datos una vez, déjelo en un montón.  Los índices tienen más sentido cuando la tabla temporal se va a reutilizar repetidamente en muchas declaraciones que filtran, unen o clasifican utilizando las mismas claves.

Si quieres saber más sobre índices no lo dudes y visita nuestra entrada: https://www.gpsos.es/2021/01/indices-sql-server-2019/

Más información en la documentación oficial : https://docs.microsoft.com/es-es/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15

Si no quieres perderte nuestras publicaciones, puedes suscribirte a nuestra newsletter. Con un email al mes estaréis informados. Tenéis más info en: GPS Open Source News. Suscribete a nuestra newsletter

¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.

Sígue a GPS en LinkedIn