SQL, вспомогательная таблица чисел

Хех... извините, что я так поздно отвечаю на старый пост. И, да, я должен был ответить, потому что самый популярный ответ (в то время, рекурсивный ответ CTE со ссылкой на 14 различных методов) в этом потоке, МММ... производительность оспаривается в лучшем случае.

во-первых, статья с 14 различными решениями отлично подходит для просмотра различных методов создания таблицы чисел / подсчета на лету, но, как указано в статье и в процитированном потоке, есть очень важная цитата...

"предложения по повышению эффективности и производительность часто субъективна. Независимо от того, как выполняется запрос используется, физическая реализация определяет эффективность запроса. Поэтому, вместо того, чтобы полагаться на предвзятые рекомендации, это крайне важно что вам нужно протестировать запрос и определить какой из них работает лучше."

Как ни странно, сама статья содержит много субъективных утверждений и "предвзятые рекомендации", такие как "рекурсивный CTE может генерировать список чисел довольно эффективно" и "Это эффективный метод использования цикла WHILE из публикации группы новостей Ицик Бен-ген" (который, я уверен, он опубликовал только для сравнения). Давайте, ребята... Просто упоминание хорошего имени Ицика может привести к тому, что какой-то бедный неряха действительно использует этот ужасный метод. Автор должен практиковать то, что он проповедует и должен делать небольшое тестирование производительности, прежде чем делать такие смехотворно неправильные заявления, особенно перед лицом любой масштабируемости.

С мыслью о том, чтобы на самом деле провести некоторое тестирование, прежде чем делать какие-либо субъективные заявления о том, что делает любой код или что кому-то "нравится", вот какой код вы можете сделать свое собственное тестирование. Установите профилировщик для SPID, из которого вы запускаете тест, и проверьте его самостоятельно... просто сделайте "Search'n'Replace" из числа 1000000 для вашего "любимого" номера и видеть...

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

пока мы на нем, вот цифры, которые я получаю от SQL Profiler для значений 100, 1000, 10000, 100000 и 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

Как видите, рекурсивный метод CTE является вторым худшим только для цикла While по длительности и процессору и имеет 8-кратное давление памяти в виде логических чтений, чем цикл While. Это RBAR на стероидах и следует избегать, любой ценой, для любых расчетов одной строки так же, как некоторое время Петли следует избегать. есть места, где рекурсия весьма ценна, но это не один из них.

в качестве бокового бара, г-н Денни абсолютно на месте... правильный размер постоянных чисел или таблицы подсчета-это путь для большинства вещей. Что означает правильный размер? Ну, большинство людей используют таблицу подсчета для генерации дат или для разбиения на VARCHAR(8000). Если вы создадите таблицу подсчета 11 000 строк с правильным кластеризованным индексом на "N", вам будет достаточно строки для создания более чем 30-летних дат(я работаю с ипотекой немного, поэтому 30 лет-это ключевое число для меня) и, конечно же, достаточно, чтобы справиться с разделением VARCHAR (8000). Почему "правильный размер" так важен? Если таблица подсчета используется много, она легко помещается в кэш, что делает ее невероятно быстрой без особого давления на память вообще.

и последнее, но не менее важное: каждый знает, что если вы создадите постоянную таблицу подсчета, не имеет большого значения, какой метод вы используете для ее создания потому что 1) это будет сделано только один раз и 2) если это что-то вроде таблицы строк 11,000, все методы будут работать "достаточно хорошо". так почему же все несварение с моей стороны о том, какой метод использовать???

ответ заключается в том, что какой-то бедный парень / девушка, который не знает ничего лучше и просто должен выполнить свою работу, может увидеть что-то вроде рекурсивного метода CTE и решить использовать его для чего-то гораздо большего и гораздо более часто используемого, чем создание постоянная таблица подсчета, и я пытаюсь защитите этих людей, серверы, на которых работает их код, и компанию, которая владеет данными на этих серверах. Угу... это очень важно. Это должно быть для всех остальных. Научите правильно делать вещи, а не "достаточно хорошо". Сделайте некоторое тестирование перед публикацией или использованием чего-то из сообщения или книги... жизнь, которую вы спасаете, на самом деле может быть вашей собственной, особенно если вы думаете, что рекурсивный CTE-это способ пойти на что-то подобное. ; -)

Спасибо, что выслушали...