Heh ... îmi pare rău că răspund atât de târziu la un post vechi. Si, da, a trebuit sa raspund, pentru ca cel mai popular raspuns (in acel moment, raspunsul recursiv CTE cu legatura cu 14 metode diferite) pe acest thread este, ummm ... performanta contestata in cel mai bun caz.
În primul rând, articolul cu cele 14 soluții diferite este bine pentru a vedea diferitele metode de creare a unui tabel Numbers/Tally în zbor, dar după cum sa subliniat în articol și în firul citat, este foarte important important citat...
"sugestii privind eficiența și
performanțele sunt deseori subiective.
Indiferent de modul în care este o interogare
utilizate, implementarea fizică
determină eficiența unei interogări.
Prin urmare, mai degrabă decât să se bazeze pe
orientări părtinitoare, este imperativ
să testați interogarea și să determinați
care se comportă mai bine. "
În mod ironic, articolul în sine conține multe afirmații subiective și "orientări părtinitoare", cum ar fi "un CTE recursiv poate genera un număr destul de eficient " și o metodă eficientă de a folosi buclă WHILE de la postarea de grup de știri de către Itzik Ben-Gen " (despre care sunt sigur că a postat doar pentru scopuri comparative). Oameni buni ... Doar menționând numele bun al lui Itzik ar putea să-i ducă pe niște sloboși săraci să folosească de fapt metoda oribilă. Autorul ar trebui să practice ceea ce predică el și ar trebui să facă un test de performanță puțin înainte de a face astfel de declarații ridicol de incorecte, în special în fața oricărei scalabilități.
Cu gândul de a face unele teste înainte de a face orice afirmații subiective despre ceea ce face orice cod sau ceea ce cineva "îi place", iată câteva coduri pe care le puteți face propriile teste cu. Configurați profilul pentru SPID-ul pe care îl executați și verificați-l singur ... faceți doar un "Search'n'Replace" al numărului 1000000 pentru numărul "preferat" și vedeți ...
--===== 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
În timp ce suntem la el, iată numerele pe care le obțin de la SQL Profiler pentru valorile de 100, 1000, 10000, 100000 și 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
După cum puteți vedea, metoda CTE recursivă este a doua cea mai rea doar pentru Loop-ul de timp și CPU și are de 8 ori presiunea de memorie sub formă de citiri logice decât Loop-ul . Este vorba despre RBAR pe steroizi și ar trebui să fie evitată, cu orice preț, pentru orice calcule cu un singur rând, la fel cum ar trebui să fie evitată o buclă. Există locuri în care recursivitatea este destul de valoroasă, dar nu este una dintre ele .
Ca o bară laterală, domnul Denny este absolut la fața locului ... o tabelă numerică corectă Numbers sau Tally este calea de a merge pentru majoritatea lucrurilor. Ce înseamnă dimensiunea corectă? Ei bine, cei mai mulți oameni folosesc un tabel Tally pentru a genera date sau pentru a face splituri pe VARCHAR (8000). Dacă creați un tabel Tally de 11.000 de rânduri cu indexul cluster corect pe "N", veți avea suficiente rânduri pentru a crea date valabile mai mult de 30 de ani (lucrez cu ipoteci un pic echitabil, astfel încât 30 de ani sunt un număr important pentru mine ) și cu siguranță suficient pentru a face față unui split VARCHAR (8000). De ce este "dimensionarea corectă" atât de importantă? Dacă masa Tally este folosită foarte mult, ea se potrivește cu ușurință în memoria cache, ceea ce o face rapid, fără prea multă presiune asupra memoriei.
Last but not least, every one knows that if you create a permanent Tally table, it doesn't much matter which method you use to build it because 1) it's only going to be made once and 2) if it's something like an 11,000 row table, all of the methods are going to run "good enough". So why all the indigination on my part about which method to use???
Răspunsul este că un tip sărac care nu știe mai bine și are nevoie doar de a-și face treaba poate să vadă ceva asemănător metodei Recursive CTE și să decidă să o folosească pentru ceva mult mai mare și mult mai frecvent folosit decât clădirea un tabel permanent Tally și încerc să protejez acei oameni, serverele pe care le rulează codul și compania care deține datele de pe serverele respective . Da ... este o afacere atât de mare. Ar trebui să fie și pentru toți ceilalți. Învățați modul corect de a face lucrurile în loc de "suficient de bun". Faceți niște teste înainte de a posta sau de a folosi ceva dintr-un post sau o carte ... viața pe care o economisiți poate, de fapt, să fie a ta, mai ales dacă credeți că o CTE recursivă este calea de a merge pentru ceva de genul asta. ;-)
Vă mulțumim că ați ascultat ...