Tenho uma tabela com representação hierárquica e para modelar a estrutura em árvore é utilizada a enumeração de caminhos. Apesar de o plano de consulta ser otimizado e a consulta em si ser direta, a execução da consulta leva 4 segundos. Porém, quando removo a classificação, a consulta é rápida, mas preciso dessa classificação.
Basicamente, a consulta deve retornar os principais pais relevantes cujos filhos satisfaçam determinados critérios.
Aqui está o link para a consulta e plano de consulta: https://www.brentozar.com/pastetheplan/?id=S1njIuPRT
SELECT TOP 50 Id
FROM Site root
WHERE Archive = 288
AND TypeId IN( 1 )
AND EXISTS (SELECT 1
FROM Site ch
WHERE ch.[path] LIKE concat(root.Path, '%')
AND ch.Commercial = 0
AND ch.Archive = 288)
ORDER BY root.Score DESC
4 segundos de tempo de CPU para classificar 6.718 linhas certamente parecem excessivos, mas minha suposição é que esse tempo foi de fato gasto avaliando os valores no escalar de computação.
Eu poderia produzir algo semelhante com Setup
Consulta
Plano de execução
Saída do gravador de desempenho do Windows
O gravador de desempenho do Windows mostra que a maior parte do tempo da CPU é consumida dentro do método Open do operador Sort quando ele está construindo a tabela de classificação e que isso é aproximadamente dividido igualmente pelos três métodos
Eles são definidos no escalar de computação e usados para a busca dinâmica .
O valor inserido na
Path
coluna acima é um lixo bastante longo e gerado aleatoriamente. A parte gerada aleatoriamente parece mais importante, poispath = CAST(CRYPT_GEN_RANDOM(20) AS NCHAR(10))
também é bastante lenta, mas, por outro lado, usarpath = CAST(CRYPT_GEN_RANDOM(150) AS CHAR(150))
para restringir os caracteres aos mais comuns é muito mais rápido.Portanto, minha conclusão é que é possível colocar coisas em uma
nvarchar
coluna que podem retardar consideravelmente o cálculo do intervalo de índice correto a ser buscado e que é provavelmente isso que está causando a longa duração da classificação no seu caso.O que foi dito acima provavelmente criará algumas strings inválidas
ucs-2
- mas não tenho certeza se elas são as únicas responsáveis pelo desempenho mais lento.