Estoy tratando de implementar JerarquíaID en una tabla (dbo. [Mensaje]) que contiene aproximadamente 50,000 filas (crecerá sustancialmente en el futuro). Sin embargo, tarda entre 30 y 40 segundos en recuperar unos 25 resultados.

El nodo raíz es un relleno para proporcionar unicidad, por lo que cada fila subsiguiente es hija de esa fila ficticia.

Necesito poder atravesar la tabla primero en profundidad y hacer que la columna JerarquíaID (dbo. [Mensaje] .MensajeID) sea la clave primaria de agrupación, también he agregado una letra pequeña calculada (dbo. [Mensaje]. Jerarquía) que almacena el nivel del nodo.

Uso: Una aplicación .Net pasa a través de un valor de Jerarquía ID en la base de datos y quiero poder recuperar todos (si los hay) hijos Y padres de ese nodo (además de la raíz, ya que es un relleno).

Una versión simplificada de la consulta que estoy usando:

@MessageID hierarchyID   /* passed in from application */

SELECT 
m.MessageID, m.MessageComment 

FROM 
dbo.[Message] as m

WHERE 
m.Messageid.IsDescendantOf(@MessageID.GetAncestor((@MessageID.GetLevel()-1))) = 1

ORDER BY 
m.MessageID

Por lo que entiendo, el índice debe detectarse automáticamente sin una pista.

Al buscar en foros, he visto personas que utilizan sugerencias de índice cuando se trata de índices de amplitud, pero no he observado esta aplicación en situaciones de profundidad. ¿Sería un enfoque relevante para mi escenario?

He pasado los últimos días tratando de encontrar una solución para este problema, pero fue en vano. Agradecería mucho cualquier ayuda, y como esta es mi primera publicación, me disculpo de antemano si esto se considera una pregunta 'novata', he leído la documentación de MS y buscado en innumerables foros, pero no he encontrado una descripción sucinta de El problema específico.