Eu sou um grande fã de CTEs. Eles são os melhores. Você sabe, eu sei, todos nós sabemos.
Hoje, me queimei com uma consulta que é cerca de 1000x mais rápida se o CTE for gravado em uma tabela real.
A consulta em si está no link, mas suspeito que não seja importante. Para simplificar, eu precisava de uma lista de todos os sinistros em uma apólice de seguro em que um sinistro atendesse a uma determinada condição (cat = 1).
Uma versão simplificada fica assim:
with cat_claims as (select distinct pol_nbr from claims where cat = 1)
select * from claims c
inner join policies p on p.polnbr = c.polnbr
inner join cat_claims cat on c.pol_nbr= cat.pol_nbr
que foi super duper lento. Se eu reescrever para ficar assim:
with cat_claims as (select distinct claim_nbr from claims where cat = 1)
select * into cat_claims
from cat_claims
select * from claims c
inner join policies p on p.polnbr = c.polnbr
inner join cat_claims cat on c.pol_nbr = cat.pol_nbr
drop table cat_claims
é muito, muito mais rápido. Isso é estranho para mim.
Pergunta Por que gravar o CTE em uma tabela real forneceria uma melhoria de desempenho?
Plano lento:
https://www.brentozar.com/pastetheplan/?id=HkyrpAtH4
Plano Rápido:
Apesar de serem planos estimados e não reais, a diferença para mim parece que vem da junção das tabelas:
RATING_CONTRIB_LOSS,RATING_CONTRIB_USR,cmbgrp,rating_revision_set, e aRatingtabela das classificaçõesCTE.Juntar-se a essas tabelas gera enormes carretéis de mesa (carretel preguiçoso), que podem ser ainda maiores no plano real.
Para cada linha na parte superior do operador de loops aninhados, o conjunto de resultados inferior é verificado, resultando em
3361 linhas * 3359 na primeira
table spool-->Nested loops-->Top(1)e
1541 linhas * 3359 na segunda
table spool-->Nested loops-->Top(1)Esses spools de tabela não estão presentes em nenhum dos dois planos nas consultas de tabela temporária.
O que estou chegando é que a parte da consulta que é inserida na tabela é semelhante à parte da consulta correspondente do arquivo
CTE. Existem algumas diferenças, mas acho que a principal causa da desaceleração é por causa dos carretéis da mesa.Um exemplo de diferença é que a varredura de tabela completa
RATING_CONTRIB_LOSSnoCTEplano é alterada para uma varredura com um predicado residual no plano de tabela temporária.CTE
Tabela temporária
E você pode argumentar que a segunda parte da consulta que usa a tabela temporária pode acessar a
RATING_CONTRIB_LOSStabela novamente. E eu teria que concordar porque ele fará uma pesquisa de chave cara.No entanto , o segundo spool de tabela no
CTEplano também é baseado em uma junção de loops aninhados com aRATING_CONTRIB_LOSStabela, que não está presente no plano de tabela temporária, e isso é uma grande vantagem.Conclusão
Eu acredito que a diferença no tempo de execução vem da consulta usando o resultado da tabela temporária de tal forma que os operadores caros não são usados. Mais especificamente os carretéis de mesa. Eu precisaria do plano real para ter 100% de certeza.
Como uma nota lateral, reescrever CTEs em tabelas temporárias ou apenas dividir consultas em geral pode, em muitos casos, fornecer melhores tempos de execução.
É mais fácil adivinhar quantas maçãs estão em um saco com maçãs e pêras do que adivinhar quantas maçãs estão em um saco com maçãs, pêras, mangas, laranjas, pêssegos, .... (Sim a fruta = mesas).
Eu também usaria tabelas #temp reais para armazenar seus resultados intermediários.
Comparação de peças de consulta de inserção de tabela CTE e temporária
Planeje com CTE parte 1
Planeje com CTE parte 2
Planeje com a tabela temporária parte 1
Diferença na junção de correspondência de RATING_CONTRIB_LOSS e hash <> Junção de loops aninhados. Resto é muito parecido, Mesmas operações (2x) na tabela de classificação