Eu tenho um CTE recursivo dentro de uma função com valor de tabela embutida. O ITVF está retornando uma lista de IDs contendo uma longa sequência de ancestrais de uma pessoa, geralmente retorna cerca de 12 a 18 vezes antes de chegar ao fim. É bastante rápido, mas há um erro nas estimativas que se acumula quando usado por muitas pessoas, tornando-se extremamente lento.
O CTE fica assim
WITH ancestors AS (
SELECT
IndID,
AncestorID
FROM
dbo.persons
UNION ALL
SELECT
IndID,
AncestorID
FROM
ancestors a
INNER JOIN dbo.persons p ON p.IndID = a.AncestorID
)
SELECT IndID, AncestorID FROM ancestors
Eu tenho uma dúzia de milhões de linhas, então é uma tabela bem grande. Quando solicito um IndID, o plano de execução diz que estimou 7 linhas, mas obteve 1.300 linhas reais. Para uma única solicitação, é aceitável (é executado em menos de um segundo), mas se eu me juntar a ela em outra solicitação para que ela seja chamada, digamos 100 vezes, a velocidade cai para um rastreamento, pois a estimativa está ficando cada vez pior.
Só para ficar claro, o erro de estimativa está presente mesmo fora do IVTF. Especifiquei apenas para deixar claro que não posso usar apenas uma tabela temporária. Ele precisa ficar em um IVTF para que eu possa integrá-lo em solicitações maiores e mais complexas e permanecer paralelizado. O que posso fazer para estimar melhor as linhas?
Atualização: cole o plano
Atualização 2: Menos simplificado
Estou meio preso entre dois problemas. Ou eu uso um MSTVF e todas as minhas consultas não podem ser paralelizadas, ou uso um ITVF e espero que os deuses do SQL sejam generosos e não subestimem terrivelmente a contagem de linhas, então tudo agora é trocado no disco rígido em vez de na RAM. Espero que seja apenas porque sou burro e que seja uma solução estúpida e fácil em algum lugar.
Atualização 3 Para responder, com o melhor de meu conhecimento, às perguntas feitas.
uno) Atualizado para a atualização cumulativa mais recente. Não mudou nada como esperado, mas é bom estar atualizado como você disse :)
dos) Estamos na edição Standard, mas tenho uma Column Store e não me lembro por que fiz isso. Está em IndID, FirstNameID, LastNameID. Vou tentar abandoná-lo, somos apenas 2 usuários no banco de dados hoje, podemos gerenciar os tempos de inatividade se ele travar alguma outra coisa.
Depois de remover o ColumnStore, ele economizou cerca de 30 segundos! Ainda lento, mas está melhor. Terei que verificar minhas anotações para descobrir por que fiz aquele ColumnStore.
dos: parte 2) A sensação de "caixa com pouca potência" que você tem é exatamente o que me pegou até agora. Achei que nossa máquina não tinha potência suficiente, mas depois de conversar com a TI aqui, eles disseram que não estávamos usando mais de 25% dos recursos disponíveis, então o gargalo estava definitivamente no nível SQL. Então, pedi uma atualização do SQL 2017 para 2022 no mês passado e então, agora que vi que a maioria das minhas consultas pesadas estavam sempre rodando serializadas, comecei a otimizar até chegar a esta. Eu tentei OPTION(USE HINT('DISALLOW_BATCH_MODE'), MAXDOP 8);e não vejo nenhuma mudança na velocidade.
tres) Essa solicitação deve retornar cerca de 14 milhões de linhas, então não se preocupe desse lado. Mas o fato de apenas 8 linhas terem sido estimadas na reserva de recursos não é uma razão pela qual ela é muito mais lenta do que deveria?
mais contexto) Eu estava usando um MSTVF antes de todo o meu trabalho este mês, quando mudei para um IVTF ele é mais rápido, mas a curva de tempo gasto versus linhas solicitadas é exponencial em vez de linear, se é que você me entende. Estou aberto para repensar como tudo isso é feito.
Trabalho para um grupo de pesquisa e parte do meu trabalho é extrair conjuntos de dados para pesquisadores. Sou praticamente o único usuário pesado do banco de dados, meus colegas estão mais envolvidos na parte do trabalho "inserir e limpar os dados". Portanto, posso fazer praticamente o que quiser com os índices, funções, etc., desde que a estrutura da tabela em si não seja muito alterada.
Atualização 4 - O quê? Não entendi, estava tentando fazer um belo gráfico para mostrar a curva exponencial "tempo gasto versus linhas solicitadas", então mudei minha consulta para obter bons números quadrados.
select
count(*)
FROM
(SELECT TOP 10000 * FROM individus.Individus WHERE AnneeNaissance > 1901 AND AnneeDeces < 1911) i CROSS APPLY
individus.GetAscendanceSimple(i.IndID) a
E isso funcionou em 10 segundos... Até tentei o TOP 10.000.000 e ainda rápido, então só tenho que colocar um número arbitrário grande para que todos os meus casos sejam cobertos e ele rode tão rápido quanto eu esperava (O TOP é importante). Antes de colocar isso como solução, devo estar errado, não? Essa é uma solução realmente estúpida se for tudo o que precisamos fazer para corrigir o planejamento.