Another Way to Look at Trees

  • Larry Schmidt-491187

    SSCrazy

    Points: 2555

    Comments posted to this topic are about the item Another Way to Look at Trees

  • fregatepllada

    SSCommitted

    Points: 1648

    Larry - are you aware that since SQL Server 2008 R2 there is a data type HierarchyID?

    If you using anything older than that version there is a Joe Celco book 100% dedicated to the

    topic of hierarchies, trees and graphs 😉

  • lwe@xtra.co.nz

    Mr or Mrs. 500

    Points: 536

    Can change CTE limit with OPTION statement. Example : SELECT * FROM CTE OPTION (MAXRECURSION 123)

    If no manager / supervisor then code won't execute the loop

    Add SET @NRows = @@RowCount before WHILE ( @NRows > 0 )

    One SELECT statement less but one iteration more

    Remove SET @Nrows = statement in the loop but add SET @NRows = @@RowCount before SET @Nlvl = @Nlvl + 1

    Should score a few nano seconds. 🙂

  • klaus.kuehne

    SSC Enthusiast

    Points: 164

    Hello,

    execution of:

    select EmployeeID, LastName, FirstName, MiddleName, Department, 0 as EmployeeLevel

    into #tmptbl

    from Employees where SupervisorID = (Select EmployeeID from #tmptbl where EmployeeLevel = 1)

    gives error message:

    The SELECT INTO statement cannot have same source and destination tables.

    ???

    Regards, Klaus

  • lwe@xtra.co.nz

    Mr or Mrs. 500

    Points: 536

    SELECT INTO will create a new table and it exists already since you are selecting from it.

    Change the statement to something you have done

    INSERT INTO #Tmp (col1,col2)

    SELECT col1,col2 FROM #Tmp WHERE col1=1

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    Isn't this a DIY version of recursive CTE?

  • g.britton

    SSChampion

    Points: 13686

    It is dyi version of a recvursive cte but avoids the rbar problem and potential exponential runtime

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • klaus.kuehne

    SSC Enthusiast

    Points: 164

    Sorry, but what means "DIY"?

  • webrunner

    One Orange Chip

    Points: 29921

    klaus.kuehne (9/15/2014)


    Sorry, but what means "DIY"?

    DIY means Do It Yourself.

    HTH (Hope This Helps 🙂 )

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Bill Talada

    SSChampion

    Points: 11956

    I'm reminded of the saying "Make things as simple as possible but not simpler". In other words, you may have assumed too simple of an answer.

    An accepted hierarchy for the animal kingdom is as follows: kingdom, phylum, class, order, family, genus, species

    There's one problem with it. Biologists have already created spreadsheets with "super class" and "sub class" columns to handle new generalizations in-between the levels of an accepted hierarchy. But, their software won't allow them to enter their new data.

    By going "backwards" in intelligence from a relative solution to an absolute solution, and backwards from complex to simple, you have gone from a general solution to a specific solution. If you must have performance, then it was a good choice but the price will be paid in maintenance.

    I don't have a problem with loopy code which generally outperforms CTEs in depths over four.

  • xapguh5

    Grasshopper

    Points: 12

    Larry,

    While this is useful, a much more robust and much faster version is discussed in http://www.sqlservercentral.com/articles/T-SQL/94570/. I personally worked on a project where we had over 20 levels with hundreds of thousands of nodes, and our processing was in the 1-2 second range using the Nested Set implementation. Additionally, it provides the structure for holding together so much more important information, suppose you wanted to track employee time accumulated per team?

  • Larry Schmidt-491187

    SSCrazy

    Points: 2555

    Thanks for your reply! I'd be very interested in looking at the book mentioned - as so often happens, the "tyranny of the urgent" resulted in doing some quick Internet scans, searches on SQLServerCentral.com, etc. Too bad, too, because our IT organization licenses the content on a service that makes available virtually every technical book published - if only we had more time to read some!

    🙂

    Larry

  • Larry Schmidt-491187

    SSCrazy

    Points: 2555

    Thanks for your reply! The one other thing you'd have to remember is to up the MaxRecursion when someone ended up with more than 123 direct reports (:-D) Great points, though!

    Larry

  • Larry Schmidt-491187

    SSCrazy

    Points: 2555

    Thanks for your reply, Bill! Believe it or not, it did cross my mind, however briefly, that "modern management" (is it really new?!) styles might have some new and varied organizational structures, cross-functional teams, etc.

    Somehow, though, at the end of the day pretty much all of us have someone we directly report to and who evaluates our performance . . . with that one exception up at level 0! And even there, a board of directors, trustees, whatever, may fill that role.

    Larry

  • Jeff Moden

    SSC Guru

    Points: 994683

    fregatepllada (9/14/2014)


    Larry - are you aware that since SQL Server 2008 R2 there is a data type HierarchyID?

    If you using anything older than that version there is a Joe Celco book 100% dedicated to the

    topic of hierarchies, trees and graphs 😉

    I'm curious... are you using the HierarchyID datatype for anything now?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

Viewing 15 posts - 1 through 15 (of 23 total)

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