主页 / dba / 问题 / 73546
Accepted
crokusek
Asked: 2014-08-09 12:51:49 +0800 CST

为什么递归 CTE 只估计 1 行？

  • 772

给定两个级联的、独立的（没有真正的表）递归 CTE：

create view NumberSequence_0_100_View
as
with NumberSequence as
(
    select 0 as Number
    union all
    select Number + 1
      from NumberSequence    
     where Number < 100
)
select Number
  from NumberSequence;
go

create view NumberSequence_0_10000_View
as
select top 10001
       v100.Number * 100 + v1.Number as Number
  from Common.NumberSequence_0_100_View v100
 cross join Common.NumberSequence_0_100_View v1
 where v1.Number < 100
   and v100.Number * 100 + v1.Number <= 10000
    -- please resist complaining about "order by in view" for this question
 order by v100.Number * 100 + v1.Number 
go

然后生成估计/实际计划：

select * from NumberSequence_0_10000_View

估计 在此处输入图像描述 实际 级联CtePlan

运行时间 23 毫秒，但估计只有一行用于最终输出（仅第一个视图有 2 行）。

问题是，当它被用作连接真实数据的子查询时（例如“DaysAgo”），计划通常是一个非常慢的嵌套循环，我经常需要添加连接提示/反向顺序等。

有没有办法在保持 CTE 方法的同时改进估计？是否曾经请求过“with (AssumeMinRows=N)”提示？对于许多情况（不仅仅是 CTE）来说，这似乎是一个很好的通用帮助程序。

sql-server sql-server-2012

1 个回答

  1. Best Answer

    为什么递归 CTE 只估计 1 行？

    递归公用表表达式的基数估计非常有限。

    在原始基数估计模型下，估计是锚点和递归部分的基数估计的简单总和。这相当于假设递归部分恰好执行一次。

    在 SQL Server 2014 中，启用新的基数估计模型后，逻辑略有变化，假设递归部分执行了三次，每次迭代返回的行数相同。

    这两个都是没有根据的猜测，因此递归 CTes 的使用通常会导致质量不佳的估计也就不足为奇了。更一般地说，估计递归过程的结果几乎是不可能的，因此优化器甚至不会尝试。这不会通过使用特别简单的递归结构来改变，递归结构显然是为了产生序列号——优化器没有逻辑来检测这种模式。

    在您的特定情况下，最终估计是一个，因为优化器进一步猜测谓词的选择性，例如[Recr1007]<(100)（在节点 ID 3 的过滤器中）和([Recr1003]*(100)+[Recr1007])<=(10000)（节点 ID 2 的嵌套循环连接上的剩余谓词）。同样，这些都是猜测，结果很不幸，但并不令人惊讶。

    有没有办法在保持 CTE 方法的同时改进估计？

    不是我知道的。

    是否曾经请求过“with (AssumeMinRows=N)”提示？

    不是直接用这些术语。已经有很多要求具体化 CTE的请求，如果这种具体化伴随着自动统计生成，这将有所帮助。我不会列出其他的，因为你似乎已经对其中的大部分建议发表了评论:)

    也有关于selectivity hints的建议，但还没有像这样的东西进入产品。

    正如对该问题的评论中所指出的，您现在最好的选择是使用实数表，而不是使用递归 CTE 即时生成一个表。第二种选择是使用手动实现 - 一个临时表 - 我相信你已经知道了。

    • 7

相关问题