Post reply

Another Recursive Hierarchy bit of help needed...

  • December 22, 2015 at 6:04 pm

    #1847626

    Jason A. Long (12/22/2015)

    I'm not certain that it's actually representative of a real, possible situation.

    I was tending to say it's not a real situation, therefore I took it out of cte_Block initially.

    But then thought it might be "a deadlock in development".

    I never saw it, because I always deal with aftermath of a deadlock. When everything is stale, and every spid involved is blocked by another one.

    But if you run the script as a part of some live monitoring - then yes, there could be such a possibility.

    So the script must be capable to deal with this one too.

    _____________
    Code for TallyGenerator

  • December 23, 2015 at 8:45 am

    #1847780

    Jason A. Long (12/22/2015)

    Yea... I'm right there with you. My latest test harness was based solely on my perception of what Jeff was talking about. I'm not certain that it's actually representative of a real, possible situation.

    Hopefully, Jeff will chime back in.

    My post notifications have, yet again, stopped working for SSC so apologies for the delay. I've asked the folks at RedGate to "fix me" a third time and to stop having their software "black ball" me just because of having a really old email address on Yahoo (and, no... I refuse to move to GMail).

    I'll try to get to this over this nice 4 day weekend coming up.

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

  • December 23, 2015 at 9:27 am

    #1847791

    Jeff Moden (12/23/2015)

    (and, no... I refuse to move to GMail).

    .

    There's always Hotmail & AOL. 😛

  • December 31, 2015 at 7:27 am

    #1848932

    thank you all for the wealth of information ... first day back in the office today so I'm just playing with the solutions ... one thing I have noticed so far is with just 215 rows in the table its taking over 30 minutes so far to run .. I shall review a bit more in depth next week

    Happy New Year and thanks for the help 🙂

    Simon

  • January 1, 2016 at 4:59 pm

    #1849040

    simon_s (12/31/2015)

    one thing I have noticed so far is with just 215 rows in the table its taking over 30 minutes so far to run...

    You have a loop somewhere in the data.

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

  • January 6, 2016 at 4:26 am

    #1849790

    Hi Jeff

    could you elaborate please ? what sort of thing am I looking for ?

    thanks simon

  • January 6, 2016 at 7:46 am

    #1849846

    simon_s (1/6/2016)

    Hi Jeff

    could you elaborate please ? what sort of thing am I looking for ?

    thanks simon

    It's a loop in the hierarchical chain. For example...

    Q Y W A M Q D P

    This is read right to left where P reports to D, D reports to Q, etc. The problem is that Q shows up in the hierarchy twice and reports to themselves. Using a traditional Recursive CTE, this would cause a never ending loop across the nodes between Q and the other Q.

    The key to finding such a loop is to trap for it in the recursive CTE that traverses the hierarchy.

    Of course, this could be easily fixed in an Org Chart (for example) by substituting a position within the organization instead of using something that identifies the employee (usually an EmployeeID). I've got a lot of sticks in the fire right now but I'll try to provide a coded example in a bit.

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

  • January 6, 2016 at 9:24 am

    #1849909

    Jeff Moden (1/6/2016)

    simon_s (1/6/2016)

    Hi Jeff

    could you elaborate please ? what sort of thing am I looking for ?

    thanks simon

    It's a loop in the hierarchical chain. For example...

    Q Y W A M Q D P

    This is read right to left where P reports to D, D reports to Q, etc. The problem is that Q shows up in the hierarchy twice and reports to themselves. Using a traditional Recursive CTE, this would cause a never ending loop across the nodes between Q and the other Q.

    The key to finding such a loop is to trap for it in the recursive CTE that traverses the hierarchy.

    Of course, this could be easily fixed in an Org Chart (for example) by substituting a position within the organization instead of using something that identifies the employee (usually an EmployeeID). I've got a lot of sticks in the fire right now but I'll try to provide a coded example in a bit.

    Ok... here we go. I wrote the code to create the test data for the hierarchy mention above. A full explanation is in the comments of the code below on how to find the loops in the data.

    This first part builds the test table as an "Adjacency List" (typical parent/child hierarchy) with a hierarchical loop in it, just like the example in my previous post...

    --===== If the test table already exists, drop it to make

    -- reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#Hierarchy','U') IS NOT NULL

    DROP TABLE #Hierarchy

    ;

    --===== Populate an "Adjacency List" table for Demo.

    -- This is the way most folks create a hierarchy and

    -- and it can lead to real problems with "loops".

    -- One simple method of prevention would be to put a

    -- unique constraint on the CHILD column but that would

    -- limit what you might need to do. We'll demo the loop

    -- that is formed with the data below.

    SELECT d.Child

    ,d.Parent

    INTO #Hierarchy

    FROM (

    SELECT 'Q',NULL UNION ALL

    SELECT 'Y','Q' UNION ALL

    SELECT 'W','Y' UNION ALL

    SELECT 'A','W' UNION ALL

    SELECT 'M','A' UNION ALL

    SELECT 'Q','M' UNION ALL

    SELECT 'D','Q' UNION ALL

    SELECT 'P','D'

    ) d (Child,Parent)

    ;

    This next bit of code shows what happens in the loop. I've limited the problem with MAXRECURSION so that it doesn't actually run forever...

    --===== Now, if we run a "traditional" traversal of the

    -- "Adjacency List" (parent/child), we end up with

    -- a never ending loop limited only by the

    -- MaxRecursion option. It would loop forever

    -- without that limit. Look at SortPath of the last

    -- row of output and see...

    WITH

    cteHierarchy AS

    (

    SELECT Child = Child

    ,Parent = Parent

    ,hLevel = 1

    ,SortPath = CAST('\' + Child + '\' AS VARCHAR(8000))

    FROM #Hierarchy

    WHERE Parent IS NULL

    UNION ALL

    SELECT tbl.Child

    ,tbl.Parent

    ,hLevel = cte.hLevel + 1

    ,SortPath = CAST(cte.SortPath + tbl.Child + '\' AS VARCHAR(8000))

    FROM cteHierarchy cte

    JOIN #Hierarchy tbl

    ON tbl.Parent = cte.Child

    WHERE SortPath NOT LIKE '%***%'

    )

    SELEcT *

    FROM cteHierarchy

    OPTION(MAXRECURSION 20) --Max number of expected levels, really.

    ;

    This code finds those loops for you without failing. The loops will need to be fixed by a human deciding what to do about it if it was unintentional. If you need "Q" (for example) to appear in a loop, there is a way to do that but we'll peel one potato at a time. Post back if you need to know that fix.

    --===== It will take a human to fix it but what's an

    -- easy way to find most hierarchical loops in

    -- just one go? Put a limit in the WHERE clause

    -- that looks for dupes in the SortPath.

    -- Like this...

    WITH

    cteHierarchy AS

    (

    SELECT Child = Child

    ,Parent = Parent

    ,hLevel = 1

    ,SortPath = CAST('\' + Child + '\' AS VARCHAR(8000))

    FROM #Hierarchy

    WHERE Parent IS NULL

    UNION ALL

    SELECT tbl.Child

    ,tbl.Parent

    ,hLevel = cte.hLevel + 1

    ,SortPath = CASE --This marks the problem to make them easy to find

    WHEN cte.Sortpath LIKE '%\'+tbl.Child+'\%'

    THEN CAST(cte.SortPath + '*** CHILD LOOP/DUPE ON '+QUOTENAME(tbl.Child,'"')+' ***\' AS VARCHAR(8000))

    ELSE CAST(cte.SortPath + tbl.Child + '\' AS VARCHAR(8000))

    END

    FROM cteHierarchy cte

    JOIN #Hierarchy tbl

    ON tbl.Parent = cte.Child

    WHERE SortPath NOT LIKE '%***%' --This limits the problem so the find can actually complete.

    )

    SELEcT *

    FROM cteHierarchy

    OPTION(MAXRECURSION 20) --Max number of expected levels, really.

    ;

    --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 8 posts - 16 through 22 (of 22 total)

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