Início / dba / Perguntas / 73546
Accepted
crokusek
Asked: 2014-08-09 12:51:49 +0800 CST

Por que o CTE recursivo estima apenas 1 linha?

  • 772

Dados dois CTEs recursivos em cascata, autocontidos (sem tabelas reais):

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

Em seguida, gere planos estimados/reais para:

select * from NumberSequence_0_10000_View

Estimativa insira a descrição da imagem aqui real CascadingCtePlan

Tempo de execução de 23 ms, mas estimando apenas uma linha para a saída final (2 linhas apenas para a primeira exibição).

O problema é que, quando isso é usado como uma subconsulta para unir dados reais (por "DaysAgo", por exemplo), o plano geralmente é um loop aninhado muito lento e geralmente preciso adicionar uma dica de junção/ordem reversa etc.

Existe alguma maneira de melhorar a estimativa, mantendo a abordagem CTE? Já houve uma solicitação de dica "with (AssumeMinRows = N)"? Isso parece ser um ótimo auxiliar de propósito geral para muitos casos (não apenas CTEs).

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Por que o CTE recursivo estima apenas 1 linha?

    A estimativa de cardinalidade para expressões de tabela comuns recursivas é extremamente limitada.

    Sob o modelo original de estimativa de cardinalidade, a estimativa é uma soma simples das estimativas de cardinalidade para a âncora e as partes recursivas. Isso é equivalente a assumir que a parte recursiva é executada exatamente uma vez.

    No SQL Server 2014, com o novo modelo de estimativa de cardinalidade habilitado, a lógica é levemente alterada para assumir três execuções da parte recursiva, com o mesmo número de linhas retornadas a cada iteração.

    Ambos são suposições não educadas, então não é surpresa que o uso de CTes recursivos geralmente resulte em estimativas de baixa qualidade. De forma mais geral, estimar o resultado de um processo recursivo é quase impossível, então o otimizador nem tenta. Isso não é alterado pelo uso de uma estrutura recursiva particularmente simples, claramente destinada a produzir números sequenciais - o otimizador não tem lógica para detectar esse padrão.

    Em seu caso particular, a estimativa final é uma porque o otimizador faz mais suposições sobre a seletividade de predicados como [Recr1007]<(100)(no filtro no ID do nó 3) e ([Recr1003]*(100)+[Recr1007])<=(10000)(o predicado residual na junção de loops aninhados no ID do nó 2). Novamente, essas são suposições e os resultados são infelizes, embora não surpreendentes.

    Existe alguma maneira de melhorar a estimativa, mantendo a abordagem CTE?

    Não que eu saiba.

    Já houve uma solicitação de dica "with (AssumeMinRows = N)"?

    Não diretamente nesses termos. Tem havido muitos pedidos de materialização de um CTE , o que ajudaria se tal materialização viesse com geração automática de estatísticas. Não vou listar as outras porque parece que você já comentou a maioria dessas sugestões :)

    Também houve sugestões para dicas de seletividade , mas nada como isso chegou ao produto ainda.

    Conforme observado nos comentários sobre a pergunta, sua melhor aposta agora é usar uma tabela de números reais em vez de gerar uma dinamicamente usando um CTE recursivo. Uma segunda opção é usar a materialização manual - uma tabela temporária - como tenho certeza de que você sabe.

    • 7

relate perguntas