使用递归CTE的超快DISTINCT：
USE tempdb;
GO
DROP TABLE dbo.Test;
GO
CREATE TABLE
dbo.Test
(
data INTEGER NOT NULL,);
GO
CREATE CLUSTERED INDEX c ON dbo.Test (data);
GO
-- Lots of duplicated values
INSERT dbo.Test WITH (TABLOCK)
(data)
SELECT TOP (5000000)
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 117329
FROM master.sys.columns C1,master.sys.columns C2,master.sys.columns C3;
GO
SET STATISTICS TIME ON;
-- 1591ms cpu
SELECT DISTINCT
data
FROM dbo.Test;
– 15ms cpu
WITH RecursiveCTE
AS (
SELECT data = MIN(T.data)
FROM dbo.Test T
UNION ALL
SELECT R.data
FROM (
-- A cunning way to use TOP in the recursive part of a CTE Smile
SELECT T.data,rn = ROW_NUMBER() OVER (ORDER BY T.data)
FROM dbo.Test T
JOIN RecursiveCTE R
ON R.data < T.data
) R
WHERE R.rn = 1
)
SELECT *
FROM RecursiveCTE
OPTION (MAXRECURSION 0);
SET STATISTICS TIME OFF;
GO
DROP TABLE dbo.Test;
递归CTE是效率的100倍:-)这种加速对我目前的项目来说是非常有价值的,但我不知道在哪种情况下这种方法是有益的.
说实话：我不知道为什么这样加快了查询,为什么数据库不能做这个优化本身.你能解释一下这是如何工作的,为什么它如此有效？
编辑：我看到与sybase类似的效果,所以这种方法似乎不适用于sql-server.
子问题：递归CTE对其他数据库系统有用吗？
解决方法
Why the database cannot do this optimization itself?
Is the recursive CTE useful for other data base systems as well?
优化器并不完美,并没有实现所有可能的技术.人们要求微软实施.请参阅此连接项目Implement Index Skip Scan.它将被关闭,因为不会修复,但这并不意味着它将来不会被解决.其他DBMS可能已经实现了它(连接项表示Oracle实现了这个优化).如果在DBMS引擎中实现了这种优化,则不需要这种“技巧”,优化器将根据可用的统计信息选择最优的计算结果的方法.
I don’t get why this speeds up the query that much.
I am not sure in which cases this approach is beneficial
简单的DISTINCT查询扫描整个索引. “扫描”表示它从磁盘读取索引的每个页面,并聚合内存中的值(或tempdb)以获取不同值的列表.
如果你知道表有很多行,但是只有很少的不同的值,那么阅读所有这些重复值就是浪费时间.递归CTE强制服务器寻找第一个不同值的索引,然后寻找第二个值的索引等等. “Seek”表示服务器在索引中使用二进制搜索来查找该值.通常一个搜索需要从磁盘读取几页. “索引”是一棵平衡的树.
如果表只有几个不同的值,则比查阅索引的所有页面要更加快速寻找几次.另一方面,如果有很多不同的值,那么按顺序读取所有页面比搜索每个连续的值更快.这应该给你一个想法在什么情况下这种方法是有益的.
显然,如果桌子很小,扫描速度会更快.只有当桌子变得“足够大”时,才能看到性能上的差异.
dba.se有一个相关的问题：Is it possible to get seek based parallel plan for distinct/group by?
