Forçar spool para evitar a criação de tabelas temporárias?

Uma pergunta muito semelhante foi feita e respondida no StackOverflow: Como posso forçar uma subconsulta a funcionar tão bem quanto uma tabela #temp? Para resumir,

1. O método mais confiável é simplesmente usar uma #tempmesa e materializá-la você mesmo.
2. Caso contrário, consulte Fornecer uma dica para forçar a materialização intermediária de CTEs ou tabelas derivadas . O uso de TOP(large_number) ... ORDER BYmuitas vezes pode encorajar o resultado a ser colocado em spool em vez de repetidamente reavaliado. Mesmo que funcione, no entanto, não há estatísticas no carretel.
3. Outra opção é usar uma função definida pelo usuário. As funções de várias instruções (conforme descritas em Como compartilhar dados entre procedimentos armazenados ) parecem forçar o SQL Server a materializar os resultados de sua subconsulta. Além disso, eles permitem que você especifique chaves primárias e índices na tabela resultante para ajudar o otimizador de consulta. Essa função pode então ser usada em uma instrução select como parte de sua visualização. Por exemplo:

CREATE FUNCTION SalesByStore (@storeid varchar(30))
   RETURNS @t TABLE (title varchar(80) NOT NULL PRIMARY KEY,
                     qty   smallint    NOT NULL)  AS
BEGIN
   INSERT @t (title, qty)
      SELECT t.title, s.qty
      FROM   sales s
      JOIN   titles t ON t.title_id = s.title_id
      WHERE  s.stor_id = @storeid
   RETURN
END

CREATE VIEW SalesData As
SELECT * FROM SalesByStore('6380')

O SQL Server não tem uma dica force_spool.

Você pode "codificar" manualmente um plano de execução que inclua um spool e, em seguida, forçar seu uso com uma dica de plano. No entanto, ainda não experimentei esse método.

Editar:

A codificação manual de um plano de execução está além do escopo deste fórum. Aqui estão algumas dicas para você começar:

• O plano de execução do SQL Server é representado como XML. Você pode pensar nisso como uma linguagem de programação complexa.
• A especificação do esquema XML explica a sintaxe desta linguagem: http://schemas.microsoft.com/sqlserver/2004/07/showplan/
• dentro de cada plano de execução você tem uma hierarquia de nós "RelOp" com um único nó raiz. Este nó raiz é onde a execução começa. O operador relacional (RelOp) é executado e solicita linhas de seu filho (ou filhos). Esses, por sua vez, executam para chamar recursivamente seus filhos.

A melhor maneira de começar é ler planos de execução XML simples.

Para colocar um carretel lá, você deve ser capaz de injetar um carretel em qualquer lugar que faça sentido. O plano de execução, no entanto, será verificado pelo SQL Server para ser válido para a consulta em questão e pode ser rejeitado.

Novamente, esta é apenas uma sugestão de pesquisa e eu ainda não tentei.

O que há de errado em persistir os resultados intermediários em uma tabela temporária? Muitas vezes, é uma abordagem sólida ao ajustar o desempenho. Pode não fornecer a consulta mais rápida em comparação com uma consulta in-line "perfeita", mas proporcionará resultados mais previsíveis.

Suspeito que você esteja enfrentando um problema de cardinalidade devido à complexidade da consulta. Você tem grandes discrepâncias entre as contagens de linhas estimadas e reais em seu plano de execução? Você pode postar seu XML do Plano de Execução Real? Enquanto isso, uma das dicas de consulta pode ajudar, por exemplo

OPTION ( HASH JOIN )

No meu equipamento simples , isso gerou uma consulta mais rápida, mas com uso mais intenso da CPU. Eu realmente não posso recomendar cortar seu próprio plano de execução. Levaria cerca de 30 segundos para refatorar sua consulta para trabalhar com tabelas temporárias, nas quais você poderia passar horas mexendo no xml do plano de execução e ainda assim não obter um resultado.