SQL, Tabla auxiliar de números

Je... siento llegar tan tarde respondiendo a un viejo post. Y, sí, tuve que responder porque la respuesta más popular (en ese momento, la respuesta recursiva CTE con el enlace a 14 métodos diferentes) en este hilo es, ummm... rendimiento desafiado en el mejor de los casos.

Primero, el artículo con las 14 soluciones diferentes está bien para ver los diferentes métodos de crear una tabla de Números / Recuento sobre la marcha, pero como se señala en el artículo y en el hilo citado, hay un muy importante citar...

" sugerencias relativas a la eficiencia y el rendimiento suele ser subjetivo. Independientemente de cómo se está realizando una consulta usado, la implementación física determina la eficiencia de una consulta. Por lo tanto, en lugar de confiar en directrices sesgadas, es imperativo que pruebe la consulta y determine cuál funciona mejor."

Irónicamente, el artículo en sí contiene muchas declaraciones subjetivas y "directrices sesgadas" como " un CTE recursivo puede generar un listado de números de manera bastante eficiente" y "Este es un método eficiente de usar WHILE loop de un grupo de noticias publicado por Itzik Ben-Gen" (que estoy seguro que publicó solo para fines de comparación). Vamos, amigos... Solo mencionar el buen nombre de Itzik puede llevar a algún pobre vago a usar ese horrible método. El autor debe practicar lo que predica y debe hacer un poco de prueba de rendimiento antes de hacer tan ridículamente incorrecto declaraciones especialmente frente a cualquier escalabilidad.

Con la idea de realmente hacer algunas pruebas antes de hacer afirmaciones subjetivas sobre lo que hace cualquier código o lo que a alguien "le gusta", aquí hay un código con el que puede hacer sus propias pruebas. Configure el perfilador para el SPID desde el que está ejecutando la prueba y compruébelo usted mismo... acaba de hacer una "Búsqueda n''Replace" el número 1000000 para su "favorito" número y ver...

--===== Test for 1000000 rows ==================================
GO
--===== Traditional RECURSIVE CTE method
   WITH Tally (N) AS 
        ( 
         SELECT 1 UNION ALL 
         SELECT 1 + N FROM Tally WHERE N < 1000000 
        ) 
 SELECT N 
   INTO #Tally1 
   FROM Tally 
 OPTION (MAXRECURSION 0);
GO
--===== Traditional WHILE LOOP method
 CREATE TABLE #Tally2 (N INT);
    SET NOCOUNT ON;
DECLARE @Index INT;
    SET @Index = 1;
  WHILE @Index <= 1000000 
  BEGIN 
         INSERT #Tally2 (N) 
         VALUES (@Index);
            SET @Index = @Index + 1;
    END;
GO
--===== Traditional CROSS JOIN table method
 SELECT TOP (1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
   INTO #Tally3
   FROM Master.sys.All_Columns ac1
  CROSS JOIN Master.sys.ALL_Columns ac2;
GO
--===== Itzik's CROSS JOINED CTE method
   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 SELECT N
   INTO #Tally4
   FROM cteTally
  WHERE N <= 1000000;
GO
--===== Housekeeping
   DROP TABLE #Tally1, #Tally2, #Tally3, #Tally4;
GO

Mientras estamos en ello, aquí están los números que obtengo de SQL Profiler para los valores de 100, 1000, 10000, 100000 y 1000000...

SPID TextData                                 Dur(ms) CPU   Reads   Writes
---- ---------------------------------------- ------- ----- ------- ------
  51 --===== Test for 100 rows ==============       8     0       0      0
  51 --===== Traditional RECURSIVE CTE method      16     0     868      0
  51 --===== Traditional WHILE LOOP method CR      73    16     175      2
  51 --===== Traditional CROSS JOIN table met      11     0      80      0
  51 --===== Itzik's CROSS JOINED CTE method        6     0      63      0
  51 --===== Housekeeping   DROP TABLE #Tally      35    31     401      0

  51 --===== Test for 1000 rows =============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method      47    47    8074      0
  51 --===== Traditional WHILE LOOP method CR      80    78    1085      0
  51 --===== Traditional CROSS JOIN table met       5     0      98      0
  51 --===== Itzik's CROSS JOINED CTE method        2     0      83      0
  51 --===== Housekeeping   DROP TABLE #Tally       6    15     426      0

  51 --===== Test for 10000 rows ============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method     434   344   80230     10
  51 --===== Traditional WHILE LOOP method CR     671   563   10240      9
  51 --===== Traditional CROSS JOIN table met      25    31     302     15
  51 --===== Itzik's CROSS JOINED CTE method       24     0     192     15
  51 --===== Housekeeping   DROP TABLE #Tally       7    15     531      0

  51 --===== Test for 100000 rows ===========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method    4143  3813  800260    154
  51 --===== Traditional WHILE LOOP method CR    5820  5547  101380    161
  51 --===== Traditional CROSS JOIN table met     160   140     479    211
  51 --===== Itzik's CROSS JOINED CTE method      153   141     276    204
  51 --===== Housekeeping   DROP TABLE #Tally      10    15     761      0

  51 --===== Test for 1000000 rows ==========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method   41349 37437 8001048   1601
  51 --===== Traditional WHILE LOOP method CR   59138 56141 1012785   1682
  51 --===== Traditional CROSS JOIN table met    1224  1219    2429   2101
  51 --===== Itzik's CROSS JOINED CTE method     1448  1328    1217   2095
  51 --===== Housekeeping   DROP TABLE #Tally       8     0     415      0

Como puede ver, el método CTE Recursivo es el segundo peor solo después del Bucle While para Duración y CPU y tiene 8 veces la presión de memoria en forma de lecturas lógicas que el Bucle While. Es RBAR en esteroides y debe evitarse, a toda costa, para cualquier cálculo de una sola fila al igual que un Bucle While debe evitarse. Hay lugares donde la recursión es bastante valiosa, pero esto NO lo ES uno de ellos.

Como barra lateral, el Sr. Denny es absolutamente perfecto... un número permanente de tamaño correcto o tabla de Conteo es el camino a seguir para la mayoría de las cosas. ¿Qué significa tamaño correcto? Bueno, la mayoría de la gente usa una tabla de Conteo para generar fechas o para hacer divisiones en VARCHAR(8000). Si crea una tabla de recuento de 11,000 filas con el índice agrupado correcto en "N", tendrá suficientes filas para crear fechas de más de 30 años (trabajo con hipotecas un poco, por lo que 30 años es un número clave para mí) y ciertamente suficiente para manejar una división VARCHAR(8000). ¿Por qué es tan importante el "tamaño correcto"? Si la tabla de conteo se usa mucho, cabe fácilmente en la memoria caché, lo que la hace increíblemente rápida sin mucha presión en la memoria.

Por último, pero no menos importante, todos saben que si creas una tabla de Conteo permanente, no importa mucho qué método uses para construirla porque 1) solo se hará una vez y 2) si es algo así como una tabla de 11,000 filas, todos los métodos van para ejecutar "lo suficientemente bueno". Entonces, ¿por qué toda la indigencia de mi parte sobre qué método usar???

La respuesta es que algún pobre chico / chica que no sabe nada mejor y solo necesita hacer su trabajo podría ver algo como el método CTE Recursivo y decidir usarlo para algo mucho más grande y mucho más utilizado que construir una tabla de Conteo permanente y estoy tratando de proteger a esas personas, los servidores en los que se ejecuta su código y la empresa que posee los datos en esos servidores. Sí... es así de importante. Debería ser para todos los demás, también. Enseñe la manera correcta de hacer las cosas en lugar de "lo suficientemente bueno". Haz algunas pruebas antes de publicar o usar algo de una publicación o libro... la vida que salves puede, de hecho, ser la tuya, especialmente si crees que un CTE recursivo es el camino a seguir para algo como esto. ;-)

Gracias por escuchar...