SQL, tabella ausiliaria di numeri

Per alcuni tipi di query SQL, una tabella ausiliaria di numeri può essere molto utile. Può essere creato come una tabella con tutte le righe necessarie per una determinata attività o come funzione definita dall’utente che restituisce il numero di righe richieste in ogni query.

Qual è il modo ottimale per creare tale funzione?

Heh … mi dispiace, sono così in ritardo nel rispondere a un vecchio post. E, sì, dovevo rispondere perché la risposta più popolare (al momento, la risposta CTE ricorsiva con il link a 14 metodi diversi) su questo thread è, ummm …, prestazioni sfidate al meglio.

Innanzitutto, l’articolo con le 14 diverse soluzioni va bene per vedere i diversi metodi di creazione di una tabella Numbers / Tally al volo ma, come indicato nell’articolo e nel thread citato, c’è una citazione molto importante …

“I suggerimenti relativi all’efficienza e alle prestazioni sono spesso soggettivi. Indipendentemente dal modo in cui viene utilizzata una query, l’implementazione fisica determina l’efficienza di una query. Pertanto, è fondamentale testare la query e determinare quale si comporta meglio. ”

Ironia della sorte, l’articolo contiene molte affermazioni soggettive e “linee guida distorte” come “un CTE ricorsivo in grado di generare un elenco di numeri abbastanza efficiente e “Questo è un metodo efficiente per utilizzare il ciclo WHILE da un newsgroup pubblicato da Itzik Ben-Gen” ( che sono sicuro ha postato solo per scopi di confronto). Forza gente … Solo menzionare il buon nome di Itzik può indurre qualche povero slob a usare quel metodo orribile. L’autore dovrebbe praticare ciò che predica e dovrebbe fare un piccolo test di performance prima di fare affermazioni così ridicolmente scorrette, specialmente di fronte a qualsiasi scalabilità.

Con l’idea di fare dei test prima di fare affermazioni soggettive su ciò che fa qualsiasi codice o su ciò che qualcuno “piace”, ecco un codice con cui puoi fare i tuoi test. Imposta il profiler per lo SPID da cui esegui il test e dai un’occhiata da solo … basta fare una “ricerca” e “sostituzione” del numero 1000000 per il tuo numero “preferito” e vedere …

--===== 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 

Mentre ci siamo, ecco i numeri che ottengo da SQL Profiler per i valori di 100, 1000, 10000, 100000 e 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 

Come si può vedere, il metodo Recursive CTE è il secondo peggiore solo per il While Loop per la Durata e per la CPU e ha una pressione della memoria 8 volte superiore a quella del readers logico rispetto al While Loop . È RBAR su steroidi e dovrebbe essere evitato, a tutti i costi, per qualsiasi calcolo a riga singola, proprio come dovrebbe essere evitato un ciclo While. Ci sono luoghi in cui la ricorsione è piuttosto preziosa, ma questo NON è uno di questi .

Come barra laterale, il signor Denny è assolutamente perfetto ... un numero permanente di numeri corretti o un tavolo Tally è la strada da percorrere per la maggior parte delle cose. Cosa significa dimensioni corrette? Bene, molte persone usano una tabella Tally per generare date o per fare divisioni su VARCHAR (8000). Se crei una tabella Tally di 11.000 righe con l'indice cluster corretto su "N", avrai abbastanza righe per creare più di 30 anni di date (lavoro con i mutui un bel po 'quindi 30 anni è un numero chiave per me ) e sicuramente abbastanza per gestire una divisione VARCHAR (8000). Perché il "dimensionamento corretto" è così importante? Se il tavolo Tally viene utilizzato molto, si adatta facilmente alla cache, il che lo rende incredibilmente veloce senza molta pressione sulla memoria.

Ultimo ma non meno importante, tutti sanno che se si crea una tabella di Tally permanente, non importa molto quale metodo si usa per crearlo perché 1) si farà solo una volta e 2) se è qualcosa come una 11.000 file tabella, tutti i metodi funzioneranno "abbastanza bene". Quindi, perché tutte le indigenze da parte mia su quale metodo utilizzare ??

La risposta è che un povero ragazzo che non sa fare di meglio e ha solo bisogno di fare il suo lavoro potrebbe vedere qualcosa come il metodo Recursive CTE e decidere di usarlo per qualcosa di molto più grande e molto più frequentemente usato che build una tabella di Tally permanente e sto cercando di proteggere quelle persone, i server su cui il loro codice gira e la società che possiede i dati su quei server . Sì ... è un grosso problema. Dovrebbe essere anche per tutti gli altri. Insegnare il modo giusto di fare le cose invece di "abbastanza buono". Esegui dei test prima di pubblicare o usare qualcosa da un post o un libro ... la vita che salvi potrebbe, in effetti, essere la tua specialmente se pensi che un CTE ricorsivo sia la strada da seguire per qualcosa di simile. 😉

Grazie per aver ascoltato...

La funzione ottimale sarebbe utilizzare una tabella anziché una funzione. L’utilizzo di una funzione causa un carico aggiuntivo della CPU per creare i valori per i dati che vengono restituiti, specialmente se i valori restituiti coprono un intervallo molto ampio.

Questo articolo offre 14 diverse soluzioni possibili con la discussione di ciascuna. Il punto importante è che:

i suggerimenti riguardanti l’efficienza e le prestazioni sono spesso soggettivi. Indipendentemente da come viene utilizzata una query, l’implementazione fisica determina l’efficienza di una query. Pertanto, piuttosto che basarsi su linee guida distorte, è imperativo testare la query e determinare quale si comporta meglio.

Personalmente mi è piaciuto:

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

Questa vista è super veloce e contiene tutti i valori int positivi.

 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 

Utilizzando SQL Server 2016+ per generare la tabella dei numeri è ansible utilizzare 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 tratta da Come possiamo usare OPENJSON per generare serie di numeri?

modifica: vedi il commento di Conrad sotto.

La risposta di Jeff Moden è fantastica … ma su Postgres trovo che il metodo Itzik non funziona a meno che non si rimuova la riga E32.

Leggermente più veloce su postgres (40ms vs 100ms) è un altro metodo che ho trovato qui adattato per 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 aN FROM E00 a CROSS JOIN E00 b), E02 (N) AS (SELECT aN FROM E01 a CROSS JOIN E01 b ), E03 (N) AS (SELECT aN 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 eg 1 million 1.5 secs, 2 mil 2.5 secs, 3 mill 4 secs Tally (N) as (SELECT row_number() OVER (ORDER BY aN) FROM E03 a) SELECT N FROM Tally 

Mentre mi sto spostando da SQL Server al mondo Postgres, forse ho perso un modo migliore per fare tabelle di controllo su quella piattaforma … INTEGER ()? SEQUENZA()?

Ancora molto tempo dopo, mi piacerebbe contribuire con un CTE “tradizionale” leggermente diverso (non toccare le tabelle di base per ottenere il volume di righe):

 --===== 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 

Questo CTE esegue più READ, quindi il CTE di Itzik ma meno il CTE tradizionale. Tuttavia, esegue costantemente meno SCRITTURA delle altre query. Come sai, le scritture sono sempre molto più costose di quelle lette.

La durata dipende pesantemente dal numero di core (MAXDOP) ma, sul mio 8core, si comporta in modo coerente più veloce (meno durata in ms) poi le altre query.

Sto 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  (Build 9600: ) 

su Windows Server 2012 R2, 32 GB, Xeon X3450 @ 2.67 Ghz, 4 core HT abilitati.