Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

HierarchyID performance problems... Really??? Expand / Collapse
Author
Message
Posted Sunday, October 2, 2011 11:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
I heard tales of performance problems associated with the HierarchyID datatype in SQL Server 2008. Since I haven't actually used the HierarchyID datatype (and, therefore, haven't used any of it methods up 'till now), I built myself a nice "little" million row "clean" table in the form of an Adjacency List (including an extra column called "Sales") and converted it to use the HierarchyID data-type. I used the indexes that Microsoft Suggested for such a thing.

Then, I built several queries just to try my hand at it (tried them on a smaller 14 node Hierarchy first, just to make sure things worked correctly). All of these queries are based on what I've been able to Google as "hierarchyid performance problems".

1. Find all Descendants (all employees in the "down-line" tree) for a given node.
2. Find all Ancestors (all managers in the "up-line" chain) for a given node.
3. Find all Siblings (all nodes at the same level) for a given node.
4. Find the SUM of sales for all Descendants 7 levels "down" for each of the million nodes in the entire hierarchy and insert into a new table.

Although I'm sure I could tweak a couple of things here and there insofar as indexing goes, I'm just not seeing what I would call either a performance problem or a resource usage problem.

So, my questions are... has anyone actually experienced a performance problem using the HierarchyID data-type and associated methods? If so, could you describe what you were doing and, perhaps, even post some code that demonstrates the problem?

Thanks for the help, folks.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1184191
Posted Sunday, October 2, 2011 11:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 1,886, Visits: 18,557
Jeff Moden (10/2/2011)
I.... Since I haven't actually used the HierarchyID datatype (and, therefore, haven't used any of it methods up 'till now), I built myself a nice "little" million row "clean" table in the form of an Adjacency List (including an extra column called "Sales") and converted it to use the HierarchyID data-type. I used the indexes that Microsoft Suggested for such a thing.


Hi Jeff

I have not used this data type before...but am keen to learn more.
would you care to share your "little" million row test rig please?

regards jls



__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1184195
Posted Sunday, October 2, 2011 12:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
Sure... the following code creates a million row "clean" hierarchy in the form of a classic Adjacency List. I have it as a stored procedure so that I can just call it with the number of rows I want the hierarchy to contain for different experiments...

CREATE PROCEDURE dbo.BuildTestHierarchy
/**********************************************************************************************************************
Create a randomized "clean" hierarchy. Each EmployeeID (except the first one, of course) is assigned a random
ManagerID number which is always less than the current EmployeeID. This code runs nasty fast and is great for
testing hierarchical processing code.

Usage: (both examples build a million row Adjacency List Hierarchy)
EXEC dbo.BuildTestHierarchy 1000000

Revision History:
Rev 00 - 28 Apr 2010 - Jeff Moden - Initial creation and test.
Rev 01 - 15 May 2010 - Jeff Moden - Abort if current DB isn't "tempdb" to protect users that want to "play".
**********************************************************************************************************************/
--===== Declare the I/O parameters
@pRowsToBuild INT
AS
--===== Make sure that we're in a safe place to run this...
IF DB_NAME() <> N'tempdb'
BEGIN
RAISERROR('Current DB is NOT tempdb. Run aborted.',11,1);
RETURN;
END
;
--===== Conditionaly drop the test table so we can do reruns more easily
IF OBJECT_ID('TempDB.dbo.Employee','U') IS NOT NULL
DROP TABLE TempDB.dbo.Employee
;
--===== Build the test table and populate it on the fly.
-- Everything except ManagerID is populated here.
SELECT TOP (@pRowsToBuild)
ISNULL(ROW_NUMBER() OVER (ORDER BY (SELECT 1)),0) AS EmployeeID,
CAST(0 AS INT) AS ManagerID,
CAST(NEWID() AS VARCHAR(36)) AS EmployeeName,
(ABS(CHECKSUM(NEWID()))%12+1)*1000 AS Sales
INTO TempDB.dbo.Employee
FROM Master.sys.All_Columns ac1
CROSS JOIN Master.sys.All_Columns ac2
;
--===== Update the test table with ManagerID's. The ManagerID is some random value which is always less than the
-- current EmployeeID to keep the hierarchy "clean" and free from "loop backs".
UPDATE TempDB.dbo.Employee
SET ManagerID = CASE
WHEN EmployeeID > 1
THEN ABS(CHECKSUM(NEWID())) % (EmployeeID-1) +1
ELSE NULL
END
;
--===== Add some indexes that most folks would like have on such a table
ALTER TABLE TempDB.dbo.Employee ADD CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmployeeID);
CREATE INDEX IX_Employee_ManagerID ON TempDB.dbo.Employee (ManagerID);




--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1184199
Posted Sunday, October 2, 2011 2:12 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:40 PM
Points: 1,595, Visits: 6,614
You posted a possible answer to your own question more than a year ago, so maybe you forgot.

http://www.sqlservercentral.com/Forums/FindPost922664.aspx

Unfortunately, the link to connect.microsoft.com results in an unexpected system error now. Maybe you remember what the item was about.
Post #1184218
Posted Sunday, October 2, 2011 2:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
Thanks, Peter. Ironically, that was one of the "dead ends" I ran across in my search for performance problems and I'm definitely not seeing those types of problems in the testing I've set up, so far.

No... I don't remember what that particular Connect Item was about, either. MS is correct about CLRs not being "seen" by the Optimizer and that the problem they mentioned could definitely be a problem with any CLR, but I'm just not seeing such problems with HierarchyID code since I started playing with it yesterday. Maybe they fixed it so it wouldn't be such a problem anymore.

Anyway, if anyone has an example of were the use of the HierarchyID data-type and related methods is a performance problem, I'd sure like to see it.

Thanks again.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1184222
Posted Monday, October 3, 2011 7:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
Gosh folks... based on the low number of responses on this thead, either not a whole lot of people are using the HierarchyID or not a whole lot of people are having problems with its performance.

Maybe I should change the question a bit... are any of you using the HierarchyID data type?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1184988
Posted Monday, October 3, 2011 11:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 1,886, Visits: 18,557
Jeff Moden (10/3/2011)

Maybe I should change the question a bit... are any of you using the HierarchyID data type?


No.

maybe useful in the future when dealing with 3rd pty dbs.



__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1185018
Posted Tuesday, October 4, 2011 8:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
Heh... I figured that out from your post where you said you never used it before. I was hoping some of the other denizens of SSC would jump in. Folks are normally pretty talkative on SSC and I'm amazed at the virtual silence this subject has drawn.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1185269
Posted Tuesday, October 4, 2011 11:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 13,126, Visits: 11,965
I think that may be further proof that few people has utilized this...nobody want to speak on a topic that they don't know about. I am hoping I can find some time soon to look through the test you have so far. I know I am going to have a project in the next few months where this could be really useful. In my case I would need more of a forest (multiple roots) but I may be able to use the hierarchyID as part of the solution.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1185410
Posted Tuesday, October 4, 2011 12:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 1,886, Visits: 18,557
Jeff Moden (10/4/2011)
Heh... I figured that out from your post where you said you never used it before. I was hoping some of the other denizens of SSC would jump in. Folks are normally pretty talkative on SSC and I'm amazed at the virtual silence this subject has drawn.


having read up a bit more on the subject...I can see possible benefits for such things as BOMs.
Would be interested to see if anyone out there is using it for BOMs.

At the moment I have no requirement for BOMS or complex Org charts ....so for now, its interesting but just not applicable (I think !!)


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1185426
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse