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!
Chris Tierney 