SQL, Pomocnicza tablica liczb

Heh... przepraszam, że tak późno odpowiadam na stary post. I tak, musiałem odpowiedzieć, ponieważ najpopularniejszą odpowiedzią (w tym czasie rekurencyjna odpowiedź CTE z linkiem do 14 różnych metod) w tym wątku jest, hmmm... wydajność w najlepszym razie kwestionowana.

Po pierwsze, artykuł z 14 różnymi rozwiązaniami jest w porządku, aby zobaczyć różne metody tworzenia tabeli liczb/Tally w locie, ale jak wskazano w artykule i w cytowanym wątku, jest bardzo Ważne cytat...

"sugestie dotyczące efektywności i wyniki są często subiektywne. Niezależnie od tego, jak zapytanie jest używane, fizyczna realizacja określa skuteczność zapytania. Dlatego zamiast polegać na stronnicze wytyczne, konieczne jest że testujesz zapytanie i ustalasz który z nich jest lepszy."

Jak na ironię, sam artykuł zawiera wiele subiektywnych stwierdzeń i "stronniczych wytycznych", takich jak "rekurencyjny CTE potrafi skutecznie wygenerować listę liczb " i "jest to efektywna metoda użycia pętli WHILE z grupy dyskusyjnej wysłanej przez Itzika Ben-Gena" (którą jestem pewien, że opublikował tylko dla celów porównawczych). Chodźcie ludzie... Samo wspominanie o dobrym imieniu Itzika może doprowadzić jakiegoś biedaka do użycia tej okropnej metody. Autor powinien ćwiczyć to ,co (s)głosi i powinien zrobić małe testy wydajności przed dokonaniem tak absurdalnie błędne wypowiedzi zwłaszcza w obliczu skalowalności.

Z myślą o zrobieniu testów przed subiektywnym stwierdzeniem, co robi każdy kod lub co ktoś "lubi", oto kod, z którym możesz zrobić własne testy. Skonfiguruj profiler dla SPIDA, z którego uruchamiasz test i sprawdź sam... po prostu wykonaj "Szukaj miejsca" liczby 1000000 dla swojego "ulubionego" numeru i zobacz...

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

Skoro już o tym mowa, oto liczby, które otrzymuję od SQL Profiler dla wartości 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

Jak widać, rekurencyjna metoda CTE jest drugą najgorszą po pętli While dla czasu trwania i procesora i ma 8 razy większe ciśnienie pamięci w postaci logicznych odczytów niż pętla While. Jest to RBAR na sterydach i należy go unikać, za wszelką cenę, w przypadku obliczeń pojedynczych wierszy, podobnie jak należy unikać pętli While. są miejsca, gdzie rekurencja jest dość cenna, ale nie jest to jeden z nich .

Ostatni, ale nie mniej ważny, każdy wie, że jeśli stworzysz stałą tabelę Tally, nie ma znaczenia, której metody użyjesz do jej zbudowania, ponieważ 1) zostanie ona wykonana tylko raz i 2) jeśli jest to coś w rodzaju tabeli 11 000 wierszy, wszystkie metody będą aby uruchomić "wystarczająco dobry". więc po co ta cała indyginacja z mojej strony o jakiej metodzie użyć???

Odpowiedź jest taka, że jakiś biedny facet / dziewczyna, który nie zna się lepiej i po prostu musi wykonać swoją pracę, może zobaczyć coś w rodzaju rekurencyjnej metody CTE i zdecydować się użyć jej do czegoś znacznie większego i znacznie częściej używanego niż budowanie stałej tabeli Tally i staram się chronić tych ludzi, serwery, na których działa ich kod, i firmę, która jest właścicielem danych. na tych serwerach . Tak... to wielka sprawa. To powinno być również dla wszystkich innych. Naucz właściwego sposobu robienia rzeczy zamiast "wystarczająco dobrego". Zrób kilka testów przed opublikowaniem lub użyciem czegoś z postu lub książki... życie, które uratujesz, może być twoim własnym, zwłaszcza jeśli uważasz, że rekurencyjny CTE jest drogą do czegoś takiego. ;-)