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