StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>It's not entirely clear whether you're trying to optimize for depth-first or breadth-first search; the question suggests depth-first, but the comments at the end are about breadth-first.</p> <p>You have all the indexes you need for depth-first (just index the <code>hierarchyid</code> column). For breadth-first, it's not enough just to <em>create</em> the computed <code>level</code> column, you have to index it too:</p> <pre><code>ALTER TABLE Message ADD [Level] AS MessageID.GetLevel() CREATE INDEX IX_Message_BreadthFirst ON Message (Level, MessageID) INCLUDE (...) </code></pre> <p>(Note that for non-clustered indexes you'll most likely need the <code>INCLUDE</code> - otherwise, SQL Server may resort to doing a clustered index scan instead.)</p> <p>Now, if you're trying to find all <em>ancestors</em> of a node, you want to take a slightly different tack. You can make these searches lightning-fast, because - and here's what's cool about <code>hierarchyid</code> - each node already "contains" all of its ancestors.</p> <p>I use a CLR function to make this as fast as possible, but you can do it with a recursive CTE:</p> <pre><code>CREATE FUNCTION dbo.GetAncestors ( @h hierarchyid ) RETURNS TABLE AS RETURN WITH Hierarchy_CTE AS ( SELECT @h AS id UNION ALL SELECT h.id.GetAncestor(1) FROM Hierarchy_CTE h WHERE h.id <> hierarchyid::GetRoot() ) SELECT id FROM Hierarchy_CTE </code></pre> <p>Now, to get all of the ancestors and descendants, use it like this:</p> <pre><code>DECLARE @MessageID hierarchyID /* passed in from application */ SELECT m.MessageID, m.MessageComment FROM Message as m WHERE m.MessageId.IsDescendantOf(@MessageID) = 1 OR m.MessageId IN (SELECT id FROM dbo.GetAncestors(@MessageID.GetAncestor(1))) ORDER BY m.MessageID </code></pre> <p>Try it out - this should solve your performance problems.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload