varias - SQL, tabla auxiliar de números

update tabla temporal sql server

Question

Para ciertos tipos de consultas sql, una tabla auxiliar de números puede ser muy útil. Se puede crear como una tabla con tantas filas como necesite para una tarea en particular o como una función definida por el usuario que devuelve el número de filas requeridas en cada consulta.

¿Cuál es la forma óptima de crear tal función?

Answer 1

Aún mucho más tarde, me gustaría contribuir con un CTE "tradicional" ligeramente diferente (no toca tablas base para obtener el volumen de filas):

--===== Hans CROSS JOINED CTE method
WITH Numbers_CTE (Digit)
AS
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
SELECT HundredThousand.Digit * 100000 + TenThousand.Digit * 10000 + Thousand.Digit * 1000 + Hundred.Digit * 100 + Ten.Digit * 10 + One.Digit AS Number
INTO #Tally5
FROM Numbers_CTE AS One CROSS JOIN Numbers_CTE AS Ten CROSS JOIN Numbers_CTE AS Hundred CROSS JOIN Numbers_CTE AS Thousand CROSS JOIN Numbers_CTE AS TenThousand CROSS JOIN Numbers_CTE AS HundredThousand

Este CTE realiza más lecturas luego el CTE de Itzik pero menos que el CTE tradicional. Sin embargo, realiza consistentemente menos ESCRITURA luego las otras consultas. Como usted sabe, las escrituras son consistentemente mucho más caras que las lecturas.

La duración depende en gran medida del número de núcleos (MAXDOP) pero, en mi 8core, se realiza de forma consistente más rápida (menos duración en ms) que las otras consultas.

Estoy usando:

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
May 14 2014 18:34:29 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

en Windows Server 2012 R2, 32 GB, Xeon X3450 a 2,67 Ghz, 4 núcleos habilitados para HT.

Answer 2

Al usar SQL Server 2016+ para generar una tabla de números, puede usar OPENJSON :

-- range from 0 to @max - 1
DECLARE @max INT = 40000;

SELECT rn = CAST([key] AS INT) 
FROM OPENJSON(CONCAT(''[1'', REPLICATE(CAST('',1'' AS VARCHAR(MAX)),@max-1),'']''));

LiveDemo

Idea tomada de ¿Cómo podemos usar OPENJSON para generar series de números?

Answer 3

Esta vista es súper rápida y contiene todos los valores int positivos.

CREATE VIEW dbo.Numbers
WITH SCHEMABINDING
AS
    WITH Int1(z) AS (SELECT 0 UNION ALL SELECT 0)
    , Int2(z) AS (SELECT 0 FROM Int1 a CROSS JOIN Int1 b)
    , Int4(z) AS (SELECT 0 FROM Int2 a CROSS JOIN Int2 b)
    , Int8(z) AS (SELECT 0 FROM Int4 a CROSS JOIN Int4 b)
    , Int16(z) AS (SELECT 0 FROM Int8 a CROSS JOIN Int8 b)
    , Int32(z) AS (SELECT TOP 2147483647 0 FROM Int16 a CROSS JOIN Int16 b)
    SELECT ROW_NUMBER() OVER (ORDER BY z) AS n
    FROM Int32
GO

Answer 4

Heh ... lo siento, estoy tan retrasado respondiendo a una publicación anterior. Y, sí, tuve que responder porque la respuesta más popular (en ese momento, la respuesta CTE recursiva 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 Numbers / Tally sobre la marcha, pero como se señala en el artículo y en el hilo citado, hay una cita muy importante ...

"Las sugerencias sobre la eficiencia y el rendimiento a menudo son subjetivas. Independientemente de cómo se utiliza una consulta, la implementación física determina la eficiencia de una consulta. Por lo tanto, en lugar de basarse en directrices sesgadas, es imperativo que pruebe la consulta y determine cuál se desempeña mejor ".

Irónicamente, el artículo en sí contiene muchas afirmaciones subjetivas y "pautas 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 el bucle WHILE de una publicación de un grupo de noticias por Itzik Ben-Gen" ( que estoy seguro publicó solo para propósitos de comparación). Vamos gente ... Solo mencionar el buen nombre de Itzik puede llevar a un pobre desgraciado a usar ese método horrible. El autor debe practicar lo que predica y debe hacer una pequeña prueba de rendimiento antes de hacer declaraciones tan ridículamente incorrectas, especialmente frente a cualquier escalablilidad.

Con la idea de hacer algunas pruebas antes de hacer afirmaciones subjetivas sobre lo que hace cualquier código o lo que "le gusta" a alguien, aquí hay un código con el que puede realizar sus propias pruebas. Configure el generador de perfiles para el SPID desde el que está ejecutando la prueba y compruébelo usted mismo ... simplemente haga una "búsqueda en reemplazo" del número 1000000 para su número "favorito" y vea ...

--===== 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 esto, 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 Recursive CTE es el segundo peor solo para While Loop for Duration y CPU y tiene 8 veces la presión de memoria en forma de lecturas lógicas que el While Loop . Es RBAR con esteroides y debe evitarse, cueste lo que cueste, para cualquier cálculo de una sola fila, como debería evitarse While Loop. Hay lugares donde la recursividad es bastante valiosa, pero este NO ES uno de ellos .

Como barra lateral, el Sr. Denny es absolutamente acertado ... una mesa permanente de números o Tally correctamente dimensionada es el camino a seguir para la mayoría de las cosas. ¿Qué significa tamaño correcto? Bueno, la mayoría de las personas usa una tabla Tally para generar fechas o para hacer divisiones en VARCHAR (8000). Si crea una tabla Tally 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 bastante, así que 30 años es un número clave para mí ) y sin duda suficiente para manejar una división VARCHAR (8000). ¿Por qué el "tamaño correcto" es tan importante? Si la tabla Tally 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, todos saben que si creas una tabla Tally permanente, no importa mucho qué método utilices para compilarla porque 1) solo se hará una vez y 2) si es algo así como una fila de 11,000 tabla, todos los métodos van a ejecutar "lo suficientemente bueno". Entonces, ¿por qué toda la indiginación de mi parte sobre qué método usar?

La respuesta es que un chico / chica pobre que no conoce mejor y solo necesita hacer su trabajo podría ver algo como el método Recursive CTE y decidir usarlo para algo mucho más grande y mucho más usado que construir una tabla Tally permanente y estoy tratando de proteger a esas personas, los servidores en los que se ejecuta su código y la compañía propietaria de los datos en esos servidores . Sí ... es un gran problema. Debería ser para todos los demás, también. Enseñe el camino correcto para hacer las cosas en lugar de "lo suficientemente bueno". Realice algunas pruebas antes de publicar o usar algo de una publicación o libro ... la vida que ahorre puede, de hecho, ser suya, especialmente si cree que un CTE recursivo es el camino a seguir para algo como esto. ;-)

Gracias por su atención...

Answer 5

La función más óptima sería usar una tabla en lugar de una función. El uso de una función genera una carga de CPU adicional para crear los valores de los datos que se devuelven, especialmente si los valores que se devuelven cubren un rango muy grande.

Answer 6

editar: vea el comentario de Conrad a continuación.

La respuesta de Jeff Moden es genial ... pero en Postgres encuentro que el método Itzik falla a menos que elimines la fila E32.

Un poco más rápido en postgres (40ms vs 100ms) es otro método que encontré here adaptado para postgres:

WITH 
    E00 (N) AS ( 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),
    E01 (N) AS (SELECT a.N FROM E00 a CROSS JOIN E00 b),
    E02 (N) AS (SELECT a.N FROM E01 a CROSS JOIN E01 b ),
    E03 (N) AS (SELECT a.N FROM E02 a CROSS JOIN E02 b 
        LIMIT 11000  -- end record  11,000 good for 30 yrs dates
    ), -- max is 100,000,000, starts slowing e.g. 1 million 1.5 secs, 2 mil 2.5 secs, 3 mill 4 secs
    Tally (N) as (SELECT row_number() OVER (ORDER BY a.N) FROM E03 a)

SELECT N
FROM Tally

Como me estoy moviendo de SQL Server a Postgres World, puede haber perdido una mejor manera de hacer tablas de conteo en esa plataforma ... INTEGER ()? SECUENCIA()?

Answer 7

Este artículo ofrece 14 soluciones posibles diferentes con discusión de cada una. El punto importante es que:

las sugerencias con respecto a la eficiencia y el rendimiento a menudo son subjetivas. Independientemente de cómo se use una consulta, la implementación física determina la eficacia de una consulta. Por lo tanto, en lugar de basarse en directrices sesgadas, es imperativo que pruebe la consulta y determine cuál se comporta mejor.

Personalmente me gustó:

WITH Nbrs ( n ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )