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

前端之家收集整理的这篇文章主要介绍了sql – 递归相关表达式如何加快不同的查询?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我发现 this post关于加快不同的查询

使用递归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对其他数据库系统有用吗?

解决方法

保罗·怀特在他的帖子 Performance Tuning the Whole Query Plan中非常详细地解释了这个“诡计”,在“寻找不同的价值”部分.

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?

原文链接:https://www.f2er.com/mssql/76066.html

猜你在找的MsSQL相关文章