Sql-server – Count the total tree structure

sql server

I've got a table which contains object_id, object_name, derived_from_object_id

What function(s) do I need to look at to be able to provide a total count of derivations based on a provided object_id?

Example

1    object1    0  
2    object2    1  
3    object3    1  
4    object4    0  
5    object5    3  
6    object6    2  
7    object7    3  
8    object8    5

There is no defined maximum number of derivations from an object or the children. So if I wanted a total count from objectid 1 the result would be 7.

I've thought about writing a stored procedure which simply returned a count for each object id, it would then call itself for each id that there was a child for. Whilst I think this would work I think it's completely the wrong direction to go.

Best Answer

You could use a recursive common table expression to find all the derivations from a particular starting point.

Table and Data

DROP TABLE IF EXISTS dbo.ObjectTree;

CREATE TABLE dbo.ObjectTree
(
    [object_id] integer NOT NULL
        CONSTRAINT [PK dbo.ObjectTree object_id]
        PRIMARY KEY CLUSTERED,
    [object_name] varchar(50) NOT NULL
        CONSTRAINT [UQ dbo.ObjectTree object_name]
        UNIQUE NONCLUSTERED,
    derived_from_object_id integer NULL
        CONSTRAINT [FK object_id]
        FOREIGN KEY (derived_from_object_id)
        REFERENCES dbo.ObjectTree ([object_id]),

    INDEX [IX dbo.ObjectTree derived_from_object_id] (derived_from_object_id)
);

INSERT dbo.ObjectTree
    ([object_id], [object_name], derived_from_object_id)
VALUES
    (1, 'object1', NULL),  
    (2, 'object2', 1),  
    (3, 'object3', 1),  
    (4, 'object4', NULL),  
    (5, 'object5', 3),  
    (6, 'object6', 2),  
    (7, 'object7', 3),  
    (8, 'object8', 5);

Recursive Query

-- Where to start
DECLARE @ObjectID integer = 1;

WITH R AS
(
    -- Anchor
    SELECT
        OT.[object_id]
    FROM dbo.ObjectTree AS OT
    WHERE
        OT.[object_id] = @ObjectID

    UNION ALL

    -- Recursive
    SELECT
        OT.[object_id]
    FROM R
    JOIN dbo.ObjectTree AS OT
        ON OT.derived_from_object_id = R.[object_id]
) 
SELECT
    derivations = COUNT_BIG(*)
FROM R
OPTION MAXRECURSION(0);

This returns 7 for [object_id] = 1.

Try it at db<>fiddle.

Using `MAXRECURSION`

The MAXRECURSION query hint can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. If that is a concern for you, use a suitable maximum recursion level instead of the zero (no limit) above. The default (no hint) is 100.

self-join

This is the way people will tell you to do it when they're warning you to stay away from cursors, because "set-based is always faster." In some recent experiments I've found that the cursor out-paces this solution.

;WITH g AS 
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID) 
    FROM dbo.Hits
    GROUP BY [Day]
)
SELECT g.[Day], g.c, rt = SUM(g2.c)
  FROM g INNER JOIN g AS g2
  ON g.[Day] >= g2.[Day]
GROUP BY g.[Day], g.c
ORDER BY g.[Day];

recursive cte with dates

Reminder - this relies on contiguous dates (no gaps), up to 10000 levels of recursion, and that you know the start date of the range you're interested (to set the anchor). You could set the anchor dynamically using a subquery, of course, but I wanted to keep things simple.

;WITH g AS 
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID) 
    FROM dbo.Hits
    GROUP BY [Day]
), x AS
(
    SELECT [Day], c, rt = c
        FROM g
        WHERE [Day] = '20120501'
    UNION ALL
    SELECT g.[Day], g.c, x.rt + g.c
        FROM x INNER JOIN g
        ON g.[Day] = DATEADD(DAY, 1, x.[Day])
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

recursive cte with row_number

Row_number calculation is slightly expensive here. Again this supports max level of recursion of 10000, but you don't need to assign the anchor.

;WITH g AS 
(
  SELECT [Day], rn = ROW_NUMBER() OVER (ORDER BY DAY), 
    c = COUNT(DISTINCT CustomerID) 
    FROM dbo.Hits
    GROUP BY [Day]
), x AS
(
    SELECT [Day], rn, c, rt = c
        FROM g
        WHERE rn = 1
    UNION ALL
    SELECT g.[Day], g.rn, g.c, x.rt + g.c
        FROM x INNER JOIN g
        ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

recursive cte with temp table

Stealing from Mikael's answer, as suggested, to include this in the tests.

CREATE TABLE #Hits
(
  rn INT PRIMARY KEY,
  c INT,
  [Day] SMALLDATETIME
);

INSERT INTO #Hits (rn, c, Day)
SELECT ROW_NUMBER() OVER (ORDER BY DAY),
       COUNT(DISTINCT CustomerID),
       [Day]
FROM dbo.Hits
GROUP BY [Day];

WITH x AS
(
    SELECT [Day], rn, c, rt = c
        FROM #Hits as c
        WHERE rn = 1
    UNION ALL
    SELECT g.[Day], g.rn, g.c, x.rt + g.c
        FROM x INNER JOIN #Hits as g
        ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

DROP TABLE #Hits;

quirky update

Again I am only including this for completeness; I personally wouldn't rely on this solution since, as I mentioned on another answer, this method is not guaranteed to work at all, and may completely break in a future version of SQL Server. (I'm doing my best to coerce SQL Server into obeying the order I want, using a hint for the index choice.)

CREATE TABLE #x([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x([Day]);

INSERT #x([Day], c) 
    SELECT [Day], c = COUNT(DISTINCT CustomerID) 
    FROM dbo.Hits
    GROUP BY [Day]
    ORDER BY [Day];

DECLARE @rt1 INT;
SET @rt1 = 0;

UPDATE #x
SET @rt1 = rt = @rt1 + c
FROM #x WITH (INDEX = x);

SELECT [Day], c, rt FROM #x ORDER BY [Day];

DROP TABLE #x;

cursor

"Beware, there be cursors here! Cursors are evil! You should avoid cursors at all costs!" No, that's not me talking, it's just stuff I hear a lot. Contrary to popular opinion, there are some cases where cursors are appropriate.

CREATE TABLE #x2([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x2([Day]);

INSERT #x2([Day], c) 
    SELECT [Day], COUNT(DISTINCT CustomerID) 
    FROM dbo.Hits
    GROUP BY [Day]
    ORDER BY [Day];

DECLARE @rt2 INT, @d SMALLDATETIME, @c INT;
SET @rt2 = 0;

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
  FOR SELECT [Day], c FROM #x2 ORDER BY [Day];

OPEN c;

FETCH NEXT FROM c INTO @d, @c;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @rt2 = @rt2 + @c;
  UPDATE #x2 SET rt = @rt2 WHERE [Day] = @d;
  FETCH NEXT FROM c INTO @d, @c;
END

SELECT [Day], c, rt FROM #x2 ORDER BY [Day];

DROP TABLE #x2;

SQL Server 2012

If you are on the most recent version of SQL Server, enhancements to windowing functionality allows us to easily calculate running totals without the exponential cost of self-joining (the SUM is calculated in one pass), the complexity of the CTEs (including the requirement of contiguous rows for the better performing CTE), the unsupported quirky update and the forbidden cursor. Just be wary of the difference between using RANGE and ROWS, or not specifying at all - only ROWS avoids an on-disk spool, which will hamper performance significantly otherwise.

;WITH g AS 
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID) 
    FROM dbo.Hits
    GROUP BY [Day]
)
SELECT g.[Day], c, 
  rt = SUM(c) OVER (ORDER BY [Day] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[Day];

performance comparisons

I took each approach and wrapped it a batch using the following:

SELECT SYSUTCDATETIME();
GO
DBCC DROPCLEANBUFFERS;DBCC FREEPROCCACHE;
-- query here
GO 10
SELECT SYSUTCDATETIME();

Here are the results of the total duration, in milliseconds (remember this includes the DBCC commands each time as well):

method                          run 1     run 2
-----------------------------   --------  --------
self-join                        1296 ms   1357 ms -- "supported" non-SQL 2012 winner
recursive cte with dates         1655 ms   1516 ms
recursive cte with row_number   19747 ms  19630 ms
recursive cte with #temp table   1624 ms   1329 ms
quirky update                     880 ms   1030 ms -- non-SQL 2012 winner
cursor                           1962 ms   1850 ms
SQL Server 2012                   847 ms    917 ms -- winner if SQL 2012 available

And I did it again without the DBCC commands:

method                          run 1     run 2
-----------------------------   --------  --------
self-join                        1272 ms   1309 ms -- "supported" non-SQL 2012 winner
recursive cte with dates         1247 ms   1593 ms
recursive cte with row_number   18646 ms  18803 ms
recursive cte with #temp table   1340 ms   1564 ms
quirky update                    1024 ms   1116 ms -- non-SQL 2012 winner
cursor                           1969 ms   1835 ms
SQL Server 2012                   600 ms    569 ms -- winner if SQL 2012 available

Removing both the DBCC and loops, just measuring one raw iteration:

method                          run 1     run 2
-----------------------------   --------  --------
self-join                         313 ms    242 ms
recursive cte with dates          217 ms    217 ms
recursive cte with row_number    2114 ms   1976 ms
recursive cte with #temp table     83 ms    116 ms -- "supported" non-SQL 2012 winner
quirky update                      86 ms     85 ms -- non-SQL 2012 winner
cursor                           1060 ms    983 ms
SQL Server 2012                    68 ms     40 ms -- winner if SQL 2012 available

Finally, I multiplied the row count in the source table by 10 (changing top to 50000 and adding another table as a cross join). The results of this, one single iteration with no DBCC commands (simply in the interests of time):

method                           run 1      run 2
-----------------------------    --------   --------
self-join                         2401 ms    2520 ms
recursive cte with dates           442 ms     473 ms
recursive cte with row_number   144548 ms  147716 ms
recursive cte with #temp table     245 ms     236 ms -- "supported" non-SQL 2012 winner
quirky update                      150 ms     148 ms -- non-SQL 2012 winner
cursor                            1453 ms    1395 ms
SQL Server 2012                    131 ms     133 ms -- winner

I only measured duration - I'll leave it as an exercise to the reader to compare these approaches on their data, comparing other metrics that may be important (or may vary with their schema/data). Before drawing any conclusions from this answer, it'll be up to you to test it against your data and your schema... these results will almost certainly change as the row counts get higher.

demo

I've added a sqlfiddle. Results:

enter image description here

conclusion

In my tests, the choice would be:

SQL Server 2012 method, if I have SQL Server 2012 available.
If SQL Server 2012 is not available, and my dates are contiguous, I would go with the recursive cte with dates method.
If neither 1. nor 2. are applicable, I would go with the self-join over the quirky update, even though the performance was close, just because the behavior is documented and guaranteed. I'm less worried about future compatibility because hopefully if the quirky update breaks it will be after I've already converted all my code to 1. :-)

But again, you should test these against your schema and data. Since this was a contrived test with relatively low row counts, it may as well be a fart in the wind. I've done other tests with different schema and row counts, and the performance heuristics were quite different... which is why I asked so many follow-up questions to your original question.

UPDATE

I've blogged more about this here:

Best approaches for running totals – updated for SQL Server 2012

Sql-server – find total money of parent node of tree according to a particular pattern

Test Data:

memberid    MemberID    joiningposition packagetype
   RPM00000     NULL         Root   free
   RPM71572     RPM00000    Left    Royal
   RPM323768    RPM00000    Right   Royal
   RPM715790    RPM71572    Left    free
   RPM323769    RPM71572    Right   free
   RPM715987    RPM323768   Left    free
   RPM323985    RPM323768   Right   free
   RPM733333    RPM323985   Right   free
   RPM324444    RPM715987   Right   Royal

create a table to store final value

CREATE TABLE [dbo].[Wallatpayout]
(   
    [childid] [varchar](50) NULL,
    [joiningposition] [varchar](50) NULL,
    [DateofJoing] [varchar](50)    NULL,
    [packagetype] [varchar](50) NULL,
    [Total] [int] NULL,
    [FirstPayoutstatus] [varchar](30) NULL,
    [DateofPayout] [datetime]    NULL 
)

create procedure [dbo].[sunnypro] as 
DECLARE @pId varchar(40) = 'RPM00000'; 
Declare @Id int set @Id=(select id from registration where hildid=@pId)      
begin

-- Recursive 
CTE WITH R AS (
    -- Anchor
    SELECT 
        BU.DateofJoing,
        BU.childid,
        BU.joiningposition,
        BU.packagetype
        FROM registration AS BU
        WHERE
        BU.MemberID = @pId and
       BU.joiningposition IN ('Left', 'Right')
        or BU.packagetype in('Royal','Platinum','Majestic')
         and BU.Id>@id
   UNION All
    -- Recursive part
    SELECT      
         BU.DateofJoing,
         BU.childid,
         R.joiningposition,
        BU.packagetype   
    FROM R
    JOIN registration AS BU
        ON BU.MemberID = R.childid
    WHERE
        BU.joiningposition IN ('Left', 'Right') and
      BU.packagetype in('Royal','Platinum','Majestic')
     and BU.Id>@id ) 
INSERT INTO Wallatpayout
           (childid
           ,packagetype
           ,joiningposition
           ,DateofJoing
           ,Total)
-- Final groups of nodes found 
SELECT top 3
R.childid,    R.packagetype,
    R.joiningposition,
    R.DateofJoing,
    Total = COUNT_BIG(*) FROM R where R.packagetype in('Royal','Platinum','Majestic') 
GROUP BY
R.childid,
    R.joiningposition,
    R.DateofJoing,
    R.packagetype
    OPTION (MAXRECURSION 0); 
end

This code is helpful for multi-level marketing, to find all left node and right node by passing particular id or parentid.