StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
2716842
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2010-04-26T20:58:57.590
FavoriteCount
0
LastActivityDate
2010-04-26T21:15:18.167
LastEditDate
2010-04-26T21:15:18.167
LastEditorUserId
322720
OwnerUserId
322720
ParentId
2714224
PostTypeId
2
Score
2
ViewCount
0
LastEditorDisplayName
text
Body
Found workaround here: <a href="http://connect.microsoft.com/SQLServer/feedback/details/532406/performance-issue-with-hierarchyid-fun-isdescendantof-in-where-clause#" rel="nofollow noreferrer">http://connect.microsoft.com/SQLServer/feedback/details/532406/performance-issue-with-hierarchyid-fun-isdescendantof-in-where-clause#</a> Just reminding that I started with a <code>heirarchyID</code> passed in from the application and my goal is to retrieve any and all relatives of that value (both Ancestors and Descendants). In my specific example, I had to add the following declarations before the <code>SELECT</code> statement: <pre><code>declare @topNode hierarchyid = (select @messageID.GetAncestor((@messageID.GetLevel()-1))) declare @topNodeParent hierarchyid = (select @topNode.GetAncestor(1)) declare @leftNode hierarchyid= (select @topNodeParent.GetDescendant (null, @topNode)) declare @rightNode hierarchyid= (select @topNodeParent.GetDescendant (@topNode, null)) </code></pre> The <code>WHERE</code> clause has been changed to: <pre><code>messageid.IsDescendantOf(@topNode)=1 AND (messageid > @leftNode ) AND (messageid < @rightNode ) </code></pre> The querying performance increase is very significant: For every result passed in, seek time is now 20ms on average (was from 120 to 420). When querying 25 values, it previously took 25 - 35 seconds to return all related nodes (in some cases each value had many relatives, in some there were none). It now takes only 2 seconds. Thank you very much to all who have contributed to this issue on this site and on others. 
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POQuestion about SQL Server HierarchyID depth-first performance
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USObjectiveCat
UserOwnerUserId
1. USObjectiveCat
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POQuestion about SQL Server HierarchyID depth-first performance
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.