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

Для некоторых типов sql-запросов может быть очень полезна вспомогательная таблица чисел. Он может быть создан в виде таблицы, содержащей столько строк, сколько необходимо для конкретной задачи, или в виде пользовательской функции, возвращающей количество строк, требуемое в каждом запросе.

Каков оптимальный способ создания такой функции?

7 ответов

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

  2. Данная статья дает 14 различных возможных решений с обсуждением каждого. Важным моментом является то, что:

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

    Мне лично понравилось:

    WITH Nbrs ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 500 )
    SELECT n FROM Nbrs
    OPTION ( MAXRECURSION 500 )
    
  3. Хе… извините, что так поздно отвечаю на старый пост. И, да, я должен был ответить, потому что самый популярный ответ (в то время рекурсивный ответ CTE со ссылкой на 14 различных методов) в этом потоке, ummm… производительность оспаривается в лучшем случае.

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

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

    По иронии судьбы, сама статья содержит много субъективных утверждений и «предвзятых рекомендаций», таких как «рекурсивный CTE может довольно эффективно генерировать список номеров» и » это эффективный метод использования WHILE loop из публикации группы новостей Itzik Ben-Gen» (который, я уверен, он разместил только для сравнения). Давайте, ребята… Одно упоминание доброго имени Ицика может привести к тому, что какой-нибудь бедолага воспользуется этим ужасным методом. Автор должен практиковать то, что он проповедует, и должен сделать небольшое тестирование производительности, прежде чем делать такие смехотворно неправильные утверждения, особенно перед лицом любой масштабируемости.

    С мыслью о том, чтобы на самом деле сделать некоторое тестирование, прежде чем делать какие-либо субъективные утверждения о том, что любой код делает или что кому-то «нравится», вот некоторый код, с которым вы можете сделать свое собственное тестирование. Настройте профилировщик для 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) split. Почему так важна» правильная калибровка»? Если таблица подсчета используется много, она легко помещается в кэш, что делает ее ослепительно быстрой без большого давления на память вообще.

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

    Ответ заключается в том, что какой-то бедный парень/девушка, которая не знает ни лучше, просто нужно сделать его или ее работу может увидеть что-то вроде рекурсивного CTE-выражения метода и решите использовать его для чего-то гораздо большего и гораздо чаще используется, чем строительство постоянного вяжутся стол и я пытаюсь защищать тех людей, серверы код, а также компании, владеющей данных на этих серверах. Угу… это очень важно. Это должно быть и для всех остальных. Учите правильно делать вещи вместо «достаточно хорошо». Проведите тестирование перед публикацией или использованием чего-либо из публикации или книги… жизнь, которую вы спасаете, на самом деле может быть вашей собственной, особенно если вы думаете, что рекурсивный CTE-это способ пойти на что-то подобное. 😉

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

  4. edit: смотрите комментарий Конрада ниже.

    Jeff Moden’s answer is great … но я нахожу на Postgres, что метод Itzik терпит неудачу, если вы не удалите строку E32.

    Немного быстрее на postgres (40ms vs 100ms) — это еще один метод, который я нашел здесь, адаптированный для 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
    

    Поскольку я перемещаюсь из SQL Server в мир Postgres, возможно, пропустил лучший способ сделать таблицы подсчета на этой платформе … INTEGER()? SEQUENCE ()?

  5. Это представление очень быстрое и содержит все положительные intзначения.

    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
    
  6. Еще много позже я хотел бы внести немного другой «традиционный» CTE (не трогает базовые таблицы, чтобы получить объем строк):

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

    Этот CTE выполняет больше чтений, чем CTE Itzik, но меньше, чем традиционный CTE.
    Однако он последовательно выполняет меньше записей, чем другие запросы.
    Как вы знаете, пишет последовательно гораздо дороже, чем читает.

    Длительность сильно зависит от количества ядер (MAXDOP), но, на my 8core, выполняет последовательно быстрее (меньше длительности в МС), чем другие запросы.

    Я использую:

    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: )
    

    на Windows Server 2012 R2, 32 ГБ, Xeon X3450 @2.67 ГГц, 4 ядра HT включен.