Post reply

Another Way to Look at Trees

  • September 14, 2014 at 9:57 pm

    #293175

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

  • September 14, 2014 at 10:14 pm

    #1745352

    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 😉

  • September 14, 2014 at 10:51 pm

    #1745353

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

  • September 15, 2014 at 1:21 am

    #1745376

    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

  • September 15, 2014 at 2:41 am

    #1745391

    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

  • September 15, 2014 at 6:28 am

    #1745446

    Isn't this a DIY version of recursive CTE?

  • September 15, 2014 at 7:42 am

    #1745480

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

    Gerald Britton, Pluralsight courses

  • September 15, 2014 at 9:00 am

    #1745501

    Sorry, but what means "DIY"?

  • September 15, 2014 at 9:27 am

    #1745510

    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 asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • September 15, 2014 at 11:12 am

    #1745543

    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.

  • September 15, 2014 at 11:49 am

    #1745549

    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?

  • September 15, 2014 at 3:46 pm

    #1745617

    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

  • September 15, 2014 at 3:48 pm

    #1745618

    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

  • September 15, 2014 at 3:52 pm

    #1745620

    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

  • September 15, 2014 at 5:04 pm

    #1745633

    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.

    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)

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

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