Генерация числовой последовательности T-SQL

#sql #sql_server #оптимизация

Написал запрос:

DECLARE
  @I INT = 10;
WITH N2 AS(
  SELECT NULL N UNION ALL SELECT NULL
)

SELECT TOP(@I) ROW_NUMBER()OVER(ORDER BY (SELECT 1)) N
FROM N2 a
  LEFT JOIN N2 b ON @I > 2
  LEFT JOIN N2 c ON @I > 4
  LEFT JOIN N2 d ON @I > 8
  LEFT JOIN N2 e ON @I > 16
  LEFT JOIN N2 f ON @I > 32
  LEFT JOIN N2 g ON @I > 64
  LEFT JOIN N2 h ON @I > 128
  LEFT JOIN N2 i ON @I > 256
  LEFT JOIN N2 j ON @I > 512
  LEFT JOIN N2 k ON @I > 1024
  LEFT JOIN N2 l ON @I > 2048
  LEFT JOIN N2 m ON @I > 4096
  LEFT JOIN N2 n ON @I > 8192
  LEFT JOIN N2 o ON @I > 16384
  LEFT JOIN N2 p ON @I > 32768
  LEFT JOIN N2 q ON @I > 65536
  LEFT JOIN N2 r ON @I > 131072
  LEFT JOIN N2 s ON @I > 262144
  LEFT JOIN N2 t ON @I > 524288
  LEFT JOIN N2 u ON @I > 1048576
  LEFT JOIN N2 v ON @I > 2097152 --etc
OPTION(FORCE ORDER, RECOMPILE, LOOP JOIN)


Благодаря RECOMPILE и FORCE ORDER фактически будет обработано столько nested loops,
сколько нужно, а не все.

И вот не знаю, куда его лучше поместить. В таблицную функцию или во вьюху и почему?
UPD: добавил сравнение трёх методов, 1)геометрической рекурсии, 2)обычной и 3)самоджойнов
с табличкой 1..2

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS TIME ON
;with Nums as(
 select * from (VALUES (1), (2), (3), (4), (5), (6), (7), (8)) AS X(N)
),
Q as(
 select 0 as N
 union all
 select Q.N*8+N.N
   from Q, Nums N
  where Q.N*8+N.N<=8180
)
select * from Q


;WITH CTE AS(
  SELECT 1 N UNION ALL SELECT N+1 FROM CTE WHERE N<8181
)
SELECT *
FROM CTE
OPTION(MAXRECURSION 10000)

;WITH N2 AS(
  SELECT NULL N UNION ALL SELECT NULL
)

SELECT TOP(8181) ROW_NUMBER()OVER(ORDER BY (SELECT 1)) N
FROM N2 a,N2 b,N2 c,N2 d,N2 e,N2 f,N2 g,N2 h,N2 i,N2 j,N2 k,N2 l,N2 m


результаты колеблются, но в среднем примерно такие:

--геометрическая рекурсия
(8181 row(s) affected)
   CPU time = 94 ms,  elapsed time = 143 ms.
--обычная рекурсия
(8181 row(s) affected)
   CPU time = 62 ms,  elapsed time = 170 ms.
--самоджойны с 1..2
(8181 row(s) affected)
   CPU time = 0 ms,  elapsed time = 53 ms.

    

Ответы

Ответ 1

Для интересующихся, можно посмотреть хороший разбор производительности различных
способов генерации числовых последовательностей от Aaron Bertrand:


Generate a set or sequence without loops – part 1
Generate a set or sequence without loops – part 2
Generate a set or sequence without loops – part 3


Сам предпочитаю использовать для генерации чисел такой запрос (можно при желании
обернуть в представление):

DECLARE @I INT = 1000000;

WITH                                   
   -- 10 значений
   num1(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
      ),               
   -- 10 * 10 значений
   num2(n) AS (SELECT 1 FROM num1 CROSS JOIN num1 AS a),
   -- 100 * 100 значений
   num3(n) AS (SELECT 1 FROM num2 CROSS JOIN num2 AS a),
  -- 10000 * 10000 значений
   num4(n) AS (SELECT 1 FROM num3 CROSS JOIN num3 AS a)
  SELECT TOP (@I)
         Number = ROW_NUMBER() OVER (ORDER BY N)
  FROM num4;