Returning Top SQL HierarchyID Ancestor

Posted: October 12, 2012 in SQL
Tags: , ,

In an attempt to optimize a query that is taking me 10 seconds, I was looking for any way possible to shave off time.

The result will populate into a SOLR collection, and we narrow results based upon the top category ID. For example a product in the category “/7/15/92/” would be give a category of “7” in the SOLR collection. Thus we could do a search for a specific belt inside all vacuum parts for example.

In order to achieve this, our first attempt was the following:

REPLACE( SUBSTRING( HID.ToString(), CHARINDEX( '/', HID.ToString() ), CHARINDEX( '/', HID.ToString(), 1 + CHARINDEX( '/', HID.ToString() ) ) ), '/', '' )

This worked great, but I noticed that it was a bit intensive. So I did a little more research and found a method that shaved a second off of the result time:

REPLACE( HID.GetAncestor( HID.GetLevel() - 1 ).ToString(), '/', '' )

I actually couldn’t find this example anywhere and thought it up for this use. If you have a better method, feel free to comment. Thanks!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s