SQL,数字辅助表

嗯…抱歉,我这么晚才回复一个旧邮件。而且,是的,我必须回答,因为这个线程上最流行的答案(当时,递归CTE的答案有14种不同方法的链接)是,嗯…性能最好是有挑战性的。

首先,有14个不同解决方案的文章可以看到动态创建数字/计数表的不同方法,但是正如文章和引用的线程中指出的,有一个 非常 重要引述…

“关于效率和 表现往往是主观的。 不管查询是如何进行的 使用,物理实现 确定查询的效率。 因此,而不是依赖 有偏见的指导方针,这是必要的 测试查询并确定 哪一个表现更好。”

具有讽刺意味的是,这篇文章本身包含了许多主观的陈述和“偏颇的指导方针”,例如 “递归CTE可以生成一个数字列表 效率相当高 “ 和 “这是 一种有效的方法 使用itzik ben gen发布的新闻组中的while循环” (我相信他只是为了比较的目的而张贴的)。伙计们…仅仅提到itzik的好名字,可能会导致一些可怜的笨蛋实际使用这种可怕的方法。作者应该实践他所宣扬的,在做出如此荒谬的错误陈述之前,特别是在面对任何可伸缩性之前,应该做一些性能测试。

考虑到在对任何代码做什么或某人“喜欢”做任何主观声明之前实际进行一些测试,下面是一些代码,您可以使用它们进行自己的测试。为运行测试的SPID设置探查器,并自己检查它…只需做一个“搜索‘不’替换’的数字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事件探查器中得到的值分别是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方法在持续时间和CPU方面仅次于while循环,其逻辑读取形式的内存压力是while循环的8倍。 . 它是类固醇的RBAR,应该不惜一切代价避免任何单列计算,就像应该避免while循环一样。 有些地方递归很有价值,但这不是其中之一 .

作为副酒吧,丹尼先生绝对是…一个大小正确的永久数字或计数表是大多数事情的方法。正确的尺寸是什么意思?好吧,大多数人使用计数表来生成日期或对varchar(8000)进行拆分。如果您创建一个11000行计数表,在“n”上使用正确的聚集索引,那么您将有足够的行来创建超过30年的日期(我处理的抵押贷款相当多,因此30年是我的一个关键数字),当然也足以处理varchar(8000)拆分。为什么“正确的尺寸”如此重要?如果Tally表被大量使用,那么它很容易放在缓存中,这使得它非常快,而不会对内存造成太大压力。

最后但并非最不重要的是,每个人都知道,如果创建一个永久的计数表,那么使用哪种方法来构建它并不重要,因为1)它只会生成一次;2)如果它类似于11000行表,那么所有的方法都会运行得“足够好”。 那么,为什么我对使用哪种方法的理解都是靛蓝的呢????

答案是,一些不太了解并且只需要完成工作的穷人/女孩可能会看到类似递归CTE方法的东西,并决定将其用于比构建永久计数表更大、更频繁使用的东西,我正在尝试 保护这些人,他们的代码运行的服务器,以及拥有这些服务器上数据的公司 . 是啊。。。这是一件大事。也应该是为其他人。教你正确的做事方法,而不是“足够好”。在发布或使用来自帖子或书籍的内容之前进行一些测试…事实上,你挽救的生命可能是你自己的,尤其是如果你认为递归CTE是实现这一目标的方法。;-)

感谢聆听…

最理想的函数是使用表而不是函数。使用一个函数会导致额外的CPU负载为要返回的数据创建值,特别是当要返回的值覆盖非常大的范围时。

This article 给出14种可能的解决方案,并对每种方案进行讨论。重要的是:

关于效率和 表现往往是主观的。 不管查询是如何进行的 使用,物理实现 确定查询的效率。 因此,而不是依赖 有偏见的指导方针,这是必要的 测试查询并确定 哪个表现更好。

我个人喜欢:

WITH Nbrs ( n ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )

这个视图是超快速的,包含了所有积极的 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

使用 SQL Server 2016+ 要生成数字表,可以使用 OPENJSON :

-- range from 0 to @max - 1
DECLARE @max INT = 40000;

SELECT rn = CAST([key] AS INT) 
FROM OPENJSON(CONCAT('[1', REPLICATE(CAST(',1' AS VARCHAR(MAX)),@max-1),']'));

LiveDemo

编辑:见下面康拉德的评论。

杰夫莫登的回答很好…但我在Postgres上发现,除非删除e32行,否则itzik方法会失败。

在Postgres上稍微快一点(40毫秒对100毫秒)是我在上发现的另一种方法 here 适合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 World时,可能错过了在该平台上做计数表的更好方法…整数()?序列(?)

稍后,我想贡献一个稍有不同的“传统”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的读取次数比itzik的CTE多,但比传统的CTE少。 但是,与其他查询相比,它始终执行较少的写入操作。 正如你所知道的,写作总是比阅读昂贵得多。

持续时间在很大程度上取决于核心数量(maxdop),但在我的8core上,执行速度比其他查询更快(ms持续时间更短)。

我正在使用:

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 GB,Xeon x3450@2.67GHz,启用4核HT。