InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 04:14:03 GMT20http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspx[quote][b]codeplexer (1/14/2013)[/b][hr]I think it does return the ragged hierarchy like your example. It tooks 22ms (definitely less than 1s) to return 72 rows. Screeshot herehttp://tinyurl.com/be6yqxporhttps://docs.google.com/file/d/0B8BX62MoYSHGRjd3RWo2Q0pERFU/edit[/quote]That may be true but you are using Adventure works DW as the test case, which is a pre-defined dataset with all the nasty work done for you so that you can consume the data in a nice simple way.I'm using a completely different source object that comes from a 3rd party finance system (Oracle Financials) which has different rules and 3nf schema design.Also the timings I gave are for building a 3700 row chart of accounts, which means that when you extrapolate out your query by around 50 times your query runs is likely to run in around 1100ms, or lets be generous and apply a 25 times assuming economies of scale its still around 500ms.In addition to this I dont have the description on the same table as the Hierachy, and have to Join an additional 2 tables for each node in order to get that piece of information and also identify 'dead' paths, so now your query will have at least least twice the number of joins as present (one for each level in the hierarchy) to look remove dead paths, then you also have to look up the descriptions which is at least one more join if you encapsulate the original query in a CTE, or an additional join for each level. I dont state that this is the optimal solution, but it hits and surpasses the NFR requirement (5 minutes) for this piece of code by at least one order of magnitude.Given more time and resources (and most importantly desire from those that write my paycheque every month) I would love to rewrite it and get an optimal solution, but at the moment free time during the day is at a premium and the will is lacking from my superiors (if it isnt broken why fix it).Tue, 15 Jan 2013 01:31:23 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspx[quote][b]codeplexer (1/13/2013)[/b][hr]Oops, I should have say "I think this is a better way...." You can find your proof easily if you know copy and paste. But SQL newbie would have know straight forward SQL join or left join is faster than recursive cte and a lot of extra code.... Just saying[/quote]Tell ya what. YOU do the copy and paste to support your own claim! Build a nice little 100,000 node hierarchy and prove what even a newbie knows... that a developer must not guess like you have. ;-)Where's the code that compares the runs and where's the code that builds the test data so that others can test?Mon, 14 Jan 2013 16:33:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxI think it does return the ragged hierarchy like your example. It tooks 22ms (definitely less than 1s) to return 72 rows. Screeshot herehttp://tinyurl.com/be6yqxporhttps://docs.google.com/file/d/0B8BX62MoYSHGRjd3RWo2Q0pERFU/editMon, 14 Jan 2013 16:14:48 GMTcodeplexerhttp://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxThanks for this, I did consider this as an option, but found that it wasnt that performant overall due to the structure of the source tables that I was reading from and the nature of the data.Also (from memory) this doesnt build a ragged hierarchy which is a strict requirement for this project. However I'm working on a alternative using one of Jeffs recent articles on Hierarchies [url]http://www.sqlservercentral.com/articles/Hierarchy/94040/[/url]), but its slow going as i can only do that when I dont have anything else more urgent to do, once I've got the code I'll post it back here.Mon, 14 Jan 2013 00:48:31 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxOops, I should have say "I think this is a better way...." You can find your proof easily if you know copy and paste. But SQL newbie would have know straight forward SQL join or left join is faster than recursive cte and a lot of extra code.... Just sayingSun, 13 Jan 2013 22:23:53 GMTcodeplexerhttp://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspx[quote][b]codeplexer (1/12/2013)[/b][hr]This is actually a much better way to do it and it's the fastest.[/quote]Claims of performance without some form of coded proof are just hearsay. Do you have some proof of that claim?Sun, 13 Jan 2013 07:47:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxHi someone ask me the same question: How to flatten a hierarchy self-referencing table, Google it and your thread and my first thought is Recursive CTE too, but then I'm just curios how someone do it before R.CTE. This is actually a much better way to do it and it's the fastest.Try it on AdventureworksDW DimAccount table, the cool thing is: One line of codeSELECTlev01.AccountKey id_01, lev01.AccountDescription name_01,lev02.AccountKey id_02, lev02.AccountDescription name_02,lev03.AccountKey id_03, lev03.AccountDescription name_03,lev04.AccountKey id_04, lev04.AccountDescription name_04,lev05.AccountKey id_05, lev05.AccountDescription name_05,lev06.AccountKey id_06, lev06.AccountDescription name_06,lev07.AccountKey id_07, lev07.AccountDescription name_07,lev08.AccountKey id_08, lev08.AccountDescription name_08,lev09.AccountKey id_09, lev09.AccountDescription name_09,lev10.AccountKey id_10, lev10.AccountDescription name_10FROM DimAccount lev01LEFT OUTER JOIN DimAccount lev02 ON lev01.AccountKey = lev02.ParentAccountKeyLEFT OUTER JOIN DimAccount lev03 ON lev02.AccountKey = lev03.ParentAccountKeyLEFT OUTER JOIN DimAccount lev04 ON lev03.AccountKey = lev04.ParentAccountKeyLEFT OUTER JOIN DimAccount lev05 ON lev04.AccountKey = lev05.ParentAccountKeyLEFT OUTER JOIN DimAccount lev06 ON lev05.AccountKey = lev06.ParentAccountKeyLEFT OUTER JOIN DimAccount lev07 ON lev06.AccountKey = lev07.ParentAccountKeyLEFT OUTER JOIN DimAccount lev08 ON lev07.AccountKey = lev08.ParentAccountKeyLEFT OUTER JOIN DimAccount lev09 ON lev08.AccountKey = lev09.ParentAccountKeyLEFT OUTER JOIN DimAccount lev10 ON lev09.AccountKey = lev10.ParentAccountKeyWHERE lev01.ParentAccountKey IS NULL --AND lev01.AccountKey =1 uncomment to get just the balance sheet subtree Found it at and courtesy of:http://jpbi.blogspot.ca/2007/05/sql-trick-for-flattening-parent-child.htmlSat, 12 Jan 2013 14:17:54 GMTcodeplexerhttp://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxNo worries Jeff theres no rush, the code has been running for around a year within the expected parameters. I also know what its like especially if things get hectic at work and during the summer holiday's.If you have any advice or pointers on improving the efficency then I'm all ears,Thu, 09 Aug 2012 01:19:00 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxHooo boy! I sure dropped the basket on this one. My apologies. Are you all set or is this still a problem?Wed, 08 Aug 2012 19:47:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxHi Jeff,I agree the limit of 12 levels is a bit of problem, however it can be extended if needed with a change to the Temptable and final pivot, but most of the hierarchies were dealing with are 4-7 levels, with one at 9 levels. The stats I provided are for a 7 level hierarchy and it returns 530 rows in the final output. I'm not sure why theres a range in the Oracle table and I've only come across its use a couple of times. I suspect its just a legacy table format thats persisted though Oracle Financials since it was incepted, I've not seen the latest version of the table in Oracle Financials 12, but i dont think its changed.I hope the code is self explanitory, as I tried to comment the important parts and what they were doing for anyone that took over the project. I look forward to reading any suggestions you might have in regards to improvements, as I'm sure there are a couple of tweaks that can be made. Many thanks for your interest as im always keen to learn.Wed, 18 Jul 2012 15:09:17 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxThanks, Jason... I'll have a look at this tonight after work.BTW... thanks for taking the time for the explainations and the code even after so long. Quick look over says that you're limted to 12 levels for now. I'm pretty sure we can automatically beat the tar out of that.As a side bar, why is there a "range" of child elements? I don't get that yet.Wed, 18 Jul 2012 09:47:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxJeff as per my last message, this is the DDL for the SP and source Table FND_FLEX_VALUE_NORM_HIERARCHY as well as the Execution plans and IO/Timing StatsJust a little explanation of some of the filesBuildHierarchy.Txt file is the main Stored Proc, you might notice a couple of strange things, the first being a superluous IF 1=0, this is needed to interface with SSIS and return a column list from the output as SSIS doesnt play nicely with Temp tables. The FND_FLEX_VALUE_NORM_HIERARCHY.Txt is the source table in Oracle Financials, and the key fields here are FLEX_VALUE_SET_ID (Type of Hierarchy), PARENT_FLEX_VALUE (Parental Node), CHILD_FLEX_VALUE_LOW (Child Range Start), CHILD_FLEX_VALUE_HIGH (Child Range End). I've also attached sample data, to show the structure in Sample Data.Txt.Its not a full tree as some are upwards 8-9 levels.Wed, 18 Jul 2012 01:17:22 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxHi Jeff,Sorry for the delay, works been quite hectic as we try and clear the decks as much as possible before the Olympic chaos hits london.The reason was that we were building a Data Warehouse off of Oracle Financials, which would hold 3+ years of data, with an estimateld 20 million rows in the balances and around the same in the transactions for every 12 months.As there was an OLAP solution (SSAS) over the top, and natural parent child hierarchies were discounted as there were 8 seperate segments for the data to be analysed by, as there is a performance issue with Parent-child hierarchies in SSAS as they are not included in any Aggregation designs that you create. The client also mentioned that they wanted to be able to add data to non-leaf level members in each hierarchy, hence the requrement to have each level act as a Leaf Level, an example of such data is budget numbers that cant be broken down below a certain grain without causing massive data expansion issues.I'll try and post the DDL tomorrow for the final SP that I created which generically creates any Hierachy based on a point of Origin and the relevant HierarchyId that is passed in. I know there are some improvements that I can make but it works and returns the data in a timely manner so its not a priority at the moment. I hope this explains things a bit better, feel free to ask me any more questions if something isnt clear. Regards Jason.Tue, 17 Jul 2012 15:34:35 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspx[quote][b]Jason-299789 (3/1/2011)[/b][hr]Hi,We are starting work on a BI project that is given data in a traditional Parent-Child Hierarchy format.The Problem is that we need to Flatten the Hierarchy out the method that we have is to create a delimited string via a standard recursive CTE, then using a UDF string spliter, with a sample record set of just 9 rows, and 4 levels, the query curently takes around 1.5 seconds. The actual record set we have to flatten is around 3-5K rows, and to complicate matters we need to run this as a low-latency, every 15 minutes, so its critical that we parse these structures as quickly as possible.I have managed to pivot the dataset and get the leaf nodes correct, by using this script[code="sql"]WITH DirectReports(Name,Title, Manager, EmployeeID, ManagerId, EmployeeLevel,Sort)AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName), e.Title, Convert(varchar, NULL) Manager, e.EmployeeID, convert(int,Null) ManagerId, 0, CONVERT(varchar(255), '\'+e.FirstName + ' ' + e.LastName) FROM dbo.MyEmployees AS e WHERE e.ManagerID IS NULL UNION ALL SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName), e.Title, Convert(varchar,d.Name) name, e.EmployeeID, convert(int,d.EmployeeID) as ManagerId, EmployeeLevel + 1, CONVERT (varchar(255), RTRIM(Sort) + '\' + FirstName + ' ' + LastName) FROM dbo.MyEmployees AS e JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID), Piv AS (Select *FROM (Select d.EmployeeId,d.ManagerId,d.Name, d.EmployeeLevel,d.EmployeeLevel c,Sort from DirectReports d ) T PIVOT (MAX(Name) For EmployeeLevel IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9])) p)Select EmployeeId,[0],[1],[2],[3],[4]from Pivorder by Sort[/code]The speed is about what we need at 1ms, however it returns this data set Id 0 1 2 3 41 Ken Sánchez NULL NULL NULL NULL273 NULL Brian Welcker NULL NULL NULL16 NULL NULL David Bradley NULL NULL23 NULL NULL NULL Mary Gibson NULL274 NULL NULL Stephen Jiang NULL NULL276 NULL NULL NULL Linda Mitchell NULL275 NULL NULL NULL Michael Blythe NULL285 NULL NULL Syed Abbas NULL NULL286 NULL NULL NULL Lynn Tsoflias NULLas you can see the nodes are Parent Levels are blank. This is result set I'm that trying to get out.Id Level0 Level1 Level2 Level3 Level41 Ken Sánchez NULL NULL NULL NULL16 Ken Sánchez Brian Welcker David Bradley NULL NULL23 Ken Sánchez Brian Welcker David Bradley Mary Gibson NULL273 Ken Sánchez Brian Welcker NULL NULL NULL274 Ken Sánchez Brian Welcker Stephen Jiang NULL NULL275 Ken Sánchez Brian Welcker Stephen Jiang Michael Blythe NULL276 Ken Sánchez Brian Welcker Stephen Jiang Linda Mitchell NULL285 Ken Sánchez Brian Welcker Syed Abbas NULL NULL286 Ken Sánchez Brian Welcker Syed Abbas Lynn Tsoflias NULLAny pointers or advice gratefully accepted, I suspect theres a Self join back into the CTE or Source table But I cant for the life of me figure it out.[/quote]Hi Jason,Yeah, I realize that this thread is well over a year old but I hope you're still around because I found it very interesting. What I'd really like to know is "why". That is, why did you need to present the hierarchical data in a flattened format like this?And, no... I'm not getting ready to start a "fight" over whether it's right or wrong. I'm just really curious as to what the actual business requirement to do this is. If you get the chance, I sure would like to hear about it.Wed, 11 Jul 2012 22:48:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxThanks Joe, I come from an App dev background, and even though I've been writing SQL code for 8 years old habits are hard to get rid off. :)Mon, 07 Mar 2011 02:08:48 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxYour problem is that you picked the wrong tradition :-)The adjacency list model is what people who grow up with recursive procedural code and assembly language pointer chains use. Switch this over to a Nested Sets model and finding all the subordinates is fast, self-join for[i] any[/i] depth. You will get the level in the hierarchy with a little math and can use that in the front end to format the display in the usual [-|+] file folders styel.Sun, 06 Mar 2011 10:11:33 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxI managed to figure out a more dynamic way of doing this using a couple of recursive CTE's one to traverse downt he hierarchy the second to traverse up the heirarchy.Heres the SQL code.[code="sql"]SET STATISTICS TIME ON;WITH DirectReports(Name,Manager, EmployeeID, ManagerId, EmployeeLevel)AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName), Convert(varchar, NULL) Manager, e.EmployeeID, convert(int,Null) ManagerId, 0 FROM dbo.MyEmployees AS e WHERE e.ManagerID IS NULL UNION ALL SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName), Convert(varchar,d.Name) name, e.EmployeeID, convert(int,d.EmployeeID) as ManagerId, EmployeeLevel + 1 FROM dbo.MyEmployees AS e JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID), X AS( Select Row_Number() OVER (ORDER BY EmployeeId) id,EmployeeID, Name, ManagerId, EmployeeLevel From DirectReports UNION ALL SELECT id, x.EmployeeId, CONVERT(varchar(255), e.FirstName + ' ' + e.LastName), e.ManagerId, EmployeeLevel-1 From X JOIN dbo.MyEmployees as e on e.EmployeeId=x.ManagerId)Select id, EmployeeId,Max([0]),Max([1]),Max([2]),Max([3]),Max([4])from (Select *FROM (Select id,d.EmployeeId,d.ManagerId,d.Name, d.EmployeeLevel,d.EmployeeLevel c --,Sort from x d ) T PIVOT (MAX(Name) For EmployeeLevel IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9])) p) pivgroup by id,EmployeeID[/code]It takes 3ms to run on a 9 row table, on a local DB.In theory it should work for any Parent Child Hierarchy as long as you know the number of levels.Fri, 04 Mar 2011 02:01:43 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxThanks Dave, much appreciated.Tue, 01 Mar 2011 03:29:39 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxThe obvious issue with this sort of query is that you have to have a finite limit to the depth of recursion that can be displayed.You may be better of , making this a display issue and doing the work in the presentation layer.In any case , here's how you can handle this in SQL[code="sql"]Create table PrtChild(Id integer not null,PrtId integer null,Name varchar(20))goinsert into PrtChild values(1,null,'Dave')insert into PrtChild values(2,1,'Tom')insert into PrtChild values(3,1,'Dick')insert into PrtChild values(4,2,'Harry')gowith cteTreeas( Select Id,PrtId, Name as Path1, cast(Null as varchar(255)) as Path2, cast(Null as varchar(255)) as Path3, cast(Null as varchar(255)) as Path4, cast(Null as varchar(255)) as Path5, 0 as Level from PrtChild where PrtId is null union all Select Child.Id, Child.PrtID, Path1, case when Level+1 = 1 then Name else Path2 end, case when Level+1 = 2 then Name else Path3 end, case when Level+1 = 3 then Name else Path4 end, case when Level+1 = 4 then Name else Path5 end, Level+1 from CteTree join PrtChild child on child.PrtId = CteTree.Id)select * from cteTree[/code]Tue, 01 Mar 2011 03:09:31 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspxHi,We are starting work on a BI project that is given data in a traditional Parent-Child Hierarchy format.The Problem is that we need to Flatten the Hierarchy out the method that we have is to create a delimited string via a standard recursive CTE, then using a UDF string spliter, with a sample record set of just 9 rows, and 4 levels, the query curently takes around 1.5 seconds. The actual record set we have to flatten is around 3-5K rows, and to complicate matters we need to run this as a low-latency, every 15 minutes, so its critical that we parse these structures as quickly as possible.I have managed to pivot the dataset and get the leaf nodes correct, by using this script[code="sql"]WITH DirectReports(Name,Title, Manager, EmployeeID, ManagerId, EmployeeLevel,Sort)AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName), e.Title, Convert(varchar, NULL) Manager, e.EmployeeID, convert(int,Null) ManagerId, 0, CONVERT(varchar(255), '\'+e.FirstName + ' ' + e.LastName) FROM dbo.MyEmployees AS e WHERE e.ManagerID IS NULL UNION ALL SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName), e.Title, Convert(varchar,d.Name) name, e.EmployeeID, convert(int,d.EmployeeID) as ManagerId, EmployeeLevel + 1, CONVERT (varchar(255), RTRIM(Sort) + '\' + FirstName + ' ' + LastName) FROM dbo.MyEmployees AS e JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID), Piv AS (Select *FROM (Select d.EmployeeId,d.ManagerId,d.Name, d.EmployeeLevel,d.EmployeeLevel c,Sort from DirectReports d ) T PIVOT (MAX(Name) For EmployeeLevel IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9])) p)Select EmployeeId,[0],[1],[2],[3],[4]from Pivorder by Sort[/code]The speed is about what we need at 1ms, however it returns this data set Id 0 1 2 3 41 Ken Sánchez NULL NULL NULL NULL273 NULL Brian Welcker NULL NULL NULL16 NULL NULL David Bradley NULL NULL23 NULL NULL NULL Mary Gibson NULL274 NULL NULL Stephen Jiang NULL NULL276 NULL NULL NULL Linda Mitchell NULL275 NULL NULL NULL Michael Blythe NULL285 NULL NULL Syed Abbas NULL NULL286 NULL NULL NULL Lynn Tsoflias NULLas you can see the nodes are Parent Levels are blank. This is result set I'm that trying to get out.Id Level0 Level1 Level2 Level3 Level41 Ken Sánchez NULL NULL NULL NULL16 Ken Sánchez Brian Welcker David Bradley NULL NULL23 Ken Sánchez Brian Welcker David Bradley Mary Gibson NULL273 Ken Sánchez Brian Welcker NULL NULL NULL274 Ken Sánchez Brian Welcker Stephen Jiang NULL NULL275 Ken Sánchez Brian Welcker Stephen Jiang Michael Blythe NULL276 Ken Sánchez Brian Welcker Stephen Jiang Linda Mitchell NULL285 Ken Sánchez Brian Welcker Syed Abbas NULL NULL286 Ken Sánchez Brian Welcker Syed Abbas Lynn Tsoflias NULLAny pointers or advice gratefully accepted, I suspect theres a Self join back into the CTE or Source table But I cant for the life of me figure it out.Tue, 01 Mar 2011 02:00:25 GMTJason-299789