Pediram-me para testar uma alteração em uma consulta, movendo uma função de valor de tabela (TVF) para um CTE (não recursivo). Meu entendimento era que cada referência CTE é executada separadamente, então havia a possibilidade da mudança levar à contenção do tempdb...
Fiquei muito surpreso ao ver o desempenho da consulta melhorar ao usar o CTE. IE:
;WITH cte AS (
SELECT t.col FROM dbo.getValues() t)
SELECT ...
JOIN cte c ...
UNION ALL
SELECT ...
JOIN cte c ...
Alguém pode lançar alguma luz sobre por que isso está acontecendo?
Acho que o comportamento é que "espera-se" que o CTE seja avaliado várias vezes, mas nem sempre funciona dessa maneira (por exemplo, não é garantido). Provavelmente existem muitas variáveis que ditam o comportamento de uma forma ou de outra. Você pode compartilhar um plano de execução real?