对于某些类型的SQL查询,辅助数字表可能非常有用.它可以创建为具有特定任务所需的行数的表,也可以创建为返回每个查询所需行数的用户定义函数.
创建这样一个函数的最佳方法是什么？
嘿......对不起,我回复一篇旧帖子的时间太晚了.并且,是的,我必须回应,因为在这个帖子上最受欢迎的答案(当时,递归CTE答案与14种不同的方法的链接),嗯......性能充其量挑战.
首先,使用14种不同解决方案的文章可以很好地查看动态创建Numbers/Tally表的不同方法,但正如文章和引用的帖子中所指出的,有一个非常重要的引用......
"关于效率和性能的建议通常是主观的.无论查询的使用方式如何,物理实现都会决定查询的效率.因此,不必依赖有偏见的指导原则,而是必须测试查询并确定查询表现更好."
具有讽刺意味的是,文章本身包含许多主观陈述和"有偏见的指导方针",例如"递归CTE可以非常有效地生成数字列表"和"这是使用来自Itzik Ben-Gen的新闻组发布的WHILE循环的有效方法 "(我相信他只是为了比较而张贴的).来吧人们......只要提到Itzik的好名字,就可能会让一些可怜的人变成实际使用这种可怕的方法.作者应该练习他所宣扬的内容,并且在做出如此荒谬的错误陈述之前应该进行一些性能测试,尤其是面对任何可扩展性.
考虑到在对任何代码做什么或者"喜欢"做出任何主观主张之前实际做了一些测试,这里有一些代码可以自己进行测试.为您正在运行测试的SPID设置探查器并自行检查...只需为您的"收藏"号码执行数字1000000的"Search'n'Replace"并查看...
--===== 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循环持续时间和CPU,并且具有逻辑读取形式的内存压力的8倍而不是While循环.它是类固醇的RBAR,应该不惜一切代价避免任何单行计算,就像应该避免使用While循环一样. 有些地方递归是非常有价值的,但这不是其中之一.
作为一个侧栏,Denny先生绝对是一个......正确大小的永久号码或Tally表是大多数事情的方法.正确大小意味着什么？好吧,大多数人使用Tally表来生成日期或在VARCHAR(8000)上进行拆分.如果您在"N"上创建一个11,000行Tally表并使用正确的聚集索引,那么您将拥有足够的行来创建超过30年的日期(我可以使用抵押贷款,所以30年对我来说是一个关键数字)当然足以处理VARCHAR(8000)拆分.为什么"正确的尺寸"如此重要？如果Tally表被大量使用,它很容易适应缓存,这使得它在没有太大的内存压力的情况下非常快速.
最后但并非最不重要的是,每个人都知道,如果你创建一个永久的Tally表,你使用哪种方法建立它并不重要,因为1)它只会被制作一次而2)如果它像11,000行那样表,所有方法都将运行"足够好". 那么为什么我所有关于使用哪种方法的原因所有？
答案是,一些不太了解并且只需要完成工作的穷人/ gal可能会看到类似递归CTE方法的东西,并决定将它用于比建筑更大更常用的东西一个永久的Tally表,我试图保护这些人,他们的代码运行的服务器,以及拥有这些服务器上的数据的公司.是的......这是一笔大买卖.它也应该适用于其他所有人.教导正确的做事方式,而不是"足够好".在发布或使用帖子或书籍中的某些内容之前进行一些测试...实际上,您节省的生命可能是您自己的,特别是如果您认为递归CTE是这样的方式.;-)
谢谢收听...
最优的功能是使用表而不是函数.使用函数会导致额外的CPU负载,以便为返回的数据创建值,尤其是在返回的值覆盖非常大的范围时.
本文提供了14种不同的可能解决方案，并对每种解决方案进行了讨论。重要的一点是：
关于效率和性能的建议通常是主观的。无论如何使用查询，物理实现方式都会确定查询的效率。因此，必须依赖于有偏见的准则，您必须测试查询并确定哪个查询的性能更好。
我个人喜欢：
WITH Nbrs ( n ) AS (
SELECT 1 UNION ALL
SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )
京公网安备 11010802040832号 | 京ICP备19059560号-6 