Monday, September 26, 2005

Effective way to expand data tree in SQL Server

There are possibly many ways to expand nodes which are in parent - child relationship. This post focus on the most effective way to do it along with other possible solutions.

# Multiple round trip to server (worst case)
- Make an SP which returns all parent/child (whatever required) IDs
- From code call SP recursively untill you reach the end.

# Single round trip to server (thats what a better option - again not the best one)
- Make an SP which returns everything from the table.
- Just make your recurssion process in Business logic (.NET/Java) rather than T-SQL

# Without recursion (hopefully, the best)
Let's dig it out, Here is the story. I tried to solve the problem in most effective way using many ways including the above two. But no way could end up into success. I was looking for some logical way at SP end. Below is the table structure and the SP. SP comments would help you to understand it well. Just go through it, it's very easy. Also I would appreciate if you have the solution in some other way.

Thanks!
Dhananjay

Sample Table
CREATE TABLE [_Test_Hierarchy] (
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[ParentID] [numeric](10, 0) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

Stored Procedure
CREATE PROCEDURE Test_Expand -- Creates an stored procedure
(
@current NUMERIC -- The ID of which all childs are required
)
AS
DECLARE @LinesUpdated INT, @Level INT

SET NOCOUNT ON

SET @LinesUpdated = 1 -- It holds the value indicating number of rows inserted into temp table.
SET @Level = 1 -- used to identify the nodes for which we have already added childs.

CREATE TABLE #stack -- temporary table, you can add more columns as per your comfort.
(
ID NUMERIC,
Lvl INT
)

INSERT INTO #stack -- Insert the current node and set it's level to 1.
VALUES (@current, @Level)


WHILE @LinesUpdated > 0
BEGIN
SET @Level = @Level + 1 -- Every loop iteratation increast one level.

-- Here we need to focus on two criteria while shortlisting.
-- 1. Check all child nodes of the parents already stored into temp table - #stack
-- 2. A node already in the list, must not be repetated. That is why we kept Lvl = @Level - 1 condition. It always search for fresh nodes.
INSERT INTO #stack
SELECT t1.ID, @Level
FROM
_Test_Hierarchy t1,
(SELECT * FROM #stack WHERE Lvl = @Level - 1) s1
WHERE 1 = 1
AND s1.ID = t1.ParentID

SET @LinesUpdated = @@ROWCOUNT -- @@ROWCOUNT always holds last count of selection. Terminate the loop when no row added.
END

-- Its very simpe, you must be knowing...
SELECT t1.ID, t1.Name
FROM
#stack s1,
_Test_Hierarchy t1
WHERE 1 = 1
AND s1.ID = t1.ID

GO

No comments: