sql – 递归相关表达式如何加快不同的查询？

使用递归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?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!