Selecting from hierarchies like Managers and Employees

  • Comments posted to this topic are about the item Selecting from hierarchies like Managers and Employees

    Chuck Hoffman
    ------------
    I'm not sure why we're here, but I am sure that while
    we're here we're supposed to help each other
    ------------

  • Isn't this same function much easier to realise with an rCTE? Does an rCTE have some hidden overhead which makes manual looping better?

    For downstream selection, you could have something along the lines of:

    WITH employmentTree

    (EmpId, EmpName, SupId, [Level])

    AS (

    -- Anchor

    SELECT EmpId, EmpName, SupId, 0

    FROM Employees

    WHERE EmpId = @targetEmpId

    -- Recursive part

    UNION ALL

    SELECT emp.EmpId, emp.EmpName, emp.SupId, eTree.[Level] + 1

    FROM Employees emp INNER JOIN employmentTree eTree

    ON emp.SupId = eTree.EmpId)

    SELECT EmpId, EmpName, SupId, [Level]

    FROM employmentTree

  • yes, I have done this often with rCTE's which is much faster 🙂

  • Why not simply use hierarchyid if it's T-SQL ?

  • The HIERARCHYID datatype was only introduced to T-SQL in 2008. If you have to use an earlier version of SQL Server (2005 or earlier), hierarchyid is not supported.

    Dave

  • Isn't this same function much easier to realise with an rCTE?

    Thank you Miika.

    I think the choice of approach must depend upon the environment. For a shop whose TSQL writers are at the expert level I like your solution better than mine. In a shop that has few expert level writers the rCTE would probably be difficult for many of them to read, making the code actually less maintainable.

    I should admit that I had not thought about rCTE.

    Chuck Hoffman
    ------------
    I'm not sure why we're here, but I am sure that while
    we're here we're supposed to help each other
    ------------

  • I know but there is already SQL 2012 out. It is good exercise but 2000/2003 has it's age and most of clients already moved. But I understand your point.

  • I also have a solution in the form of a view that you can join to that will display upline and down line structure depending on how it is joined to

    Its quite technical but if you guys are keen I will submit it through an article

  • Unfortunately, I have to work with a 3rd party application that has only recently been certified by the supplier to work with SQL Server 2008. The base application probably contains none of the datatypes that have been introduced to TSQL since SQL Server 2000. Some of the companies using the application are still running it on SQL Server 2000. We are running SQL Server 2005, so we can at least use CTEs for reporting.

    If we want to take advantage of any of the newer datatypes we would have to introduce our own tables, the links with each table could possibly break with each new release of the application. I don't think introducing tables containing HIERARCHYID columns worth it at the moment as there are other ways to report on the data (we are currently using recursive CTEs). However I am considering adding tables containing spatial datatypes as I think the benefit could outweigh the effort and also the risk of a new release breaking the table linkage.

    I'd be interested in knowing which version(s) of SQL Server Chuck is using/supporting.

    Dave

  • If you are using a recursive CTE then you will need a to have a UNION ALL.

    DECLARE @EmpId int

    SET @EmpId = 5

    ;WITH CTE AS

    (

    SELECT *

    FROM employee e

    WHERE e.EmpId = @EmpId

    UNION ALL

    SELECT e.*

    FROM employee e

    INNER JOIN CTE

    ON CTE.SupId = e.EmpId

    )

    SELECT *

    FROM CTE

    OPTION (MAXRECURSION 0)

    You can also add MAXRECURSION 0 if you are confident that there is no infinite loop in the expression or data, if there is it could lead to an infinite loop, e.g. if you had an insert for Baker like this:

    INSERT INTO Employee (EmpID, EmpName, SupID)

    VALUES (3, 'Baker', 5)

    So maybe best to stick to the default MAXRECURSION of 100 as I doubt there is any organisation with more than 100 levels.

  • I use recursive CTEs for this as well and it's quite fast. Haven't tried the hierarchyid type though.

  • Dave Brooking (5/3/2012)


    I'd be interested in knowing which version(s) of SQL Server Chuck is using/supporting.

    2000, 2005, and 2008.

    Chuck Hoffman
    ------------
    I'm not sure why we're here, but I am sure that while
    we're here we're supposed to help each other
    ------------

  • I use rCTE for account control and access on our websites. Best way to go, haven't found one that's faster, including hierarchyid. I'm thinking hierarchyid loses on my perf tests due to the majority of its functions being CLR.

  • imarran (5/3/2012)


    yes, I have done this often with rCTE's which is much faster 🙂

    Proof!!! Let's see the proof for your claim!!!

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • hayden-508426 (5/3/2012)


    I use rCTE for account control and access on our websites. Best way to go, haven't found one that's faster, including hierarchyid. I'm thinking hierarchyid loses on my perf tests due to the majority of its functions being CLR.

    Your performance tests? Care to share those please? 😉

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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