sql - 在 SQL Server 中生成序列，交叉应用性能不佳

Question

我多年前从网上获得了以下代码，它对我非常有用。它只是一个函数，可以生成从 1 到您传入的任何数字的序列。

基本上它是一种在 SQL 语句中执行 for 循环的方法。

CREATE FUNCTION [SequenceCreate]
(@MaxValue INT)
RETURNS TABLE
AS

RETURN
WITH
Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),
Num6 (n) AS (SELECT 1 FROM Num5 AS X, Num5 AS Y),
Nums (n) AS
(SELECT ROW_NUMBER() OVER(ORDER BY n)
FROM Num6)
SELECT n AS [Value] FROM Nums
WHERE n BETWEEN 1 AND @MaxValue;

这通常工作得很好而且速度很快，但我发现当使用交叉应用语句时它的性能很差，例如

DECLARE @T TABLE(StartNum INT, ItemCount INT)
INSERT INTO @T VALUES (100, 5)
INSERT INTO @T VALUES (110, 7)
INSERT INTO @T VALUES (55, 3)

SELECT Seq.Value + StartNum FROM @T
CROSS APPLY he.SequenceCreate(ItemCount) AS Seq

这在我的机器上非常慢。有谁知道为什么它在执行一次时工作正常但在通过交叉应用执行 3 次时运行非常糟糕？即使 @T 表只包含 1 行，性能仍然很糟糕。有没有更好的写法？

提前致谢，迈克尔

Answer 1

查询优化器认为最好只执行一次该函数，然后在连接中使用结果来获取所需的行。这样做是因为您的函数是一个内联表值函数。如果您改为将函数设为多语句值函数，它将为源表中的每一行执行一次该函数。但是，我建议您按照 Igor 的建议创建一个数字表。

CREATE FUNCTION [SequenceCreate]
(@MaxValue INT)
RETURNS @T TABLE ([Value] INT NOT NULL PRIMARY KEY)
AS
BEGIN

  WITH
  Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
  Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
  Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
  Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
  Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),
  Num6 (n) AS (SELECT 1 FROM Num5 AS X, Num5 AS Y),
  Nums (n) AS
  (SELECT ROW_NUMBER() OVER(ORDER BY n)
  FROM Num6)
  INSERT INTO @T
  SELECT n AS [Value] FROM Nums
  WHERE n BETWEEN 1 AND @MaxValue;

  RETURN
END