Flattening a Parent Child Hierarchy

Question

Flattening a Parent Child Hierarchy

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

Thanks 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 http://www.sqlservercentral.com/articles/Hierarchy/94040/), 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.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

codeplexer SSC Enthusiast Points: 193 More actions · Answer 2

I think it does return the ragged hierarchy like your example. It tooks 22ms (definitely less than 1s) to return 72 rows.

Screeshot here

http://tinyurl.com/be6yqxp

or

https://docs.google.com/file/d/0B8BX62MoYSHGRjd3RWo2Q0pERFU/edit

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 3

codeplexer (1/13/2013)
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

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?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

codeplexer (1/14/2013)
I think it does return the ragged hierarchy like your example. It tooks 22ms (definitely less than 1s) to return 72 rows.
Screeshot here
http://tinyurl.com/be6yqxp
or
https://docs.google.com/file/d/0B8BX62MoYSHGRjd3RWo2Q0pERFU/edit

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).

_________________________________________________________________________
SSC Guide to Posting and Best Practices