Suponha que você tenha uma função com valor de tabela com múltiplas instruções que faça uso intenso de variáveis de tabela. Que métodos existem para otimizá-los? Só sei o seguinte e todos me decepcionam.
- Converta as variáveis de tabela em variáveis de tabela com otimização de memória e reze para que isso faça uma grande diferença. Dado que eles não têm estatísticas, provavelmente não terá.
- Jogue
OPTION(RECOMPILE)em todos os lugares e espere pelo melhor. Isso lhe dará melhores estimativas de cardinalidade, mas essas não são soluções mágicas. - Truque: Remova as variáveis da tabela, converta a função em uma função embutida com valor de tabela e espere pelo melhor.
- Truque: Converta as variáveis da tabela em tabelas temporárias, converta a função em um procedimento armazenado e reze para que a mudança de interface não seja um problema.
Suponha que o SQL Server 2019, pois acho que foi a última vez que o desempenho de qualquer coisa mencionada nesta questão melhorou.
Estas não são fraudes, estas são as soluções.
Uma função não embutida (independentemente de ser escalar ou com valor de tabela) é ruim para o desempenho por dois motivos:
É executado RBAR (linha por linha agonizante). Portanto, normalmente ele precisa ser processado uma vez para cada linha do conjunto de dados em que é aplicado.
Ele cria uma zona serial no plano de execução em torno de onde a função com valor de tabela de múltiplas instruções é executada, o que significa que o paralelismo sai da janela para aquela área do plano.
Se você puder converter a função para ser inline, suas chances de um resultado melhor serão mensuravelmente melhores. Não é realmente uma situação de “ esperança pelo melhor ”. A mesma coisa para alterar a função para um procedimento. Ele abre muito mais opções para você trabalhar com o mecanismo do SQL Server durante o ajuste de consultas. Se a preocupação com um procedimento for a mudança na interface, em alguns casos de uso limitado você poderá agrupar o procedimento novamente em uma função para que seja a mesma interface para o consumidor. Porém, a melhor opção é começar a usar os recursos adequados (por exemplo, procedimentos, quando possível) desde o início.
Uma reescrita de consulta é quase certa se você estiver tendo problemas de desempenho com o código que foi escrito para a função com valor de tabela de múltiplas instruções.