Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Retireving hierarchical data

Author  Topic 

Starting Member

7 Posts

Posted - 2008-08-26 : 05:14:23
I'm dealing with hierarchical data which is not a kinda org charts but it involves children being linked to the many roots. Imagine building blocks being part of different products and i want to know the children of each product at time passing the product ids as the concatenated string. i want a csv of parent for each child and all the values are in one table only in context and mapping format and levels are unknown. I'm using a recursive query for each parent and getting the below result later using distinct and coalesce. I'm expecting the data to be so huge that each parent may have up to 500-1000 children in hierarchy and children are shared between parents.

Child Parent
100 201,202
101 201,202,203
102 201,203

any Idea on how can i speed up the process on the server side. Currently we are able to achieve a speed of 14 sec(6.5 sec query running on server) for 3 products with 500 distinct children in total.


Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-26 : 05:19:32
Post your query and some sample data. maybe someone will be able to optimise it.

Go to Top of Page

Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-26 : 08:40:14
Not sure what you expect since you didn't post either the table structure(s) or the query you are currently using.

However, without any of that, then the best I can do is suggest that with SS2000 the idea is to start with 1 (or many) parents and get ALL the direct children on first iteration. Then get ALL the children of those children in the second iteration, etc. That way you only have as many iterations as there are total number of nesting levels. So if all 1000 children are in 5 levels of nesting you are only querying 5 times. The only other suggestion is to make sure your query handles the situation where your data has a circular reference.

Be One with the Optimizer
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 00:14:56
Refer this for an illustration
Go to Top of Page

Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-27 : 15:05:29
Originally posted by visakh16

Refer this for an illustration

That KB artical uses the technique that does one iteration per value rather than what I suggested as one iteration per level of nesting.

The code in that link makes 19 recursive queries. This code (based on the same sample data uses 5:

set nocount on

CREATE TABLE hierarchy
(parent VARCHAR(20) NOT NULL,
child VARCHAR(20),
CONSTRAINT UIX_parentchild
ON hierarchy(parent)

INSERT hierarchy VALUES('World','Europe')
INSERT hierarchy VALUES('World','North America')
INSERT hierarchy VALUES('Europe','France')
INSERT hierarchy VALUES('France','Paris')
INSERT hierarchy VALUES('North America','United States')
INSERT hierarchy VALUES('North America','Canada')
INSERT hierarchy VALUES('United States','New York')
INSERT hierarchy VALUES('United States','Washington')
INSERT hierarchy VALUES('New York','New York City')
INSERT hierarchy VALUES('Washington','Redmond')

declare @t table (parent varchar(20), child varchar(20), lev int, fullpath varchar(1000))
declare @lev int
set @lev = 0

--Get Root node(s)
insert @t (parent, child, lev, fullpath)
select distinct null, p.parent, @lev, p.parent
from hierarchy p
left join hierarchy c on c.child = p.parent
where c.child is null

while @@rowcount > 0
set @lev = @lev + 1

--Get all children of current level
insert @t (parent, child, lev, fullpath)
select h.parent, h.child, @lev, t.fullpath + '.' + h.child
from @t t
join hierarchy h on h.parent = t.child and t.lev = @lev-1

--make sure a circular reference doesn't put is in an infinate loop
left join @t x on x.parent = h.parent and x.child = h.child
where x.parent is null

print 'helper table'
select * from @t order by fullpath

print 'one way to display the hierarchy'
select replicate(char(9), lev) + child from @t order by fullpath

drop table hierarchy

helper table
parent child lev fullpath
-------------------- -------------------- ----------- ------------------------------------------------------
NULL World 0 World
World Europe 1 World.Europe
Europe France 2 World.Europe.France
France Paris 3 World.Europe.France.Paris
World North America 1 World.North America
North America Canada 2 World.North America.Canada
North America United States 2 World.North America.United States
United States New York 3 World.North America.United States.New York
New York New York City 4 World.North America.United States.New York.New York City
United States Washington 3 World.North America.United States.Washington
Washington Redmond 4 World.North America.United States.Washington.Redmond

one way to display the hierarchy
North America
United States
New York
New York City

Be One with the Optimizer
Go to Top of Page

Starting Member

7 Posts

Posted - 2008-08-28 : 06:47:35
Hi All,

Thanks for all the replies,Sorry for not posting the code, As of now It has been designed using the method suggested by TG. But that method is still very slow. Actually the problem for me that a child can have more than one root parent so i need to query all the children for each parent which is taking time, and as the application is multi-user is it adviced to use global tables.I'm trying to use a global temp table so that we need not query for children of a child whose children has been determined for another root.

Go to Top of Page

Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-28 : 08:19:21
as TransactCharlie said - if you want help:
"Post your query and some sample data. maybe someone will be able to optimise it."

If you are sharing a "global temp table" between concurrent users maybe blocking is causing your slowness.
Another option is since you are using previously constructed nodes then perhaps the data is static enough to store the entire structure and rebuild only when base data changes.

Be One with the Optimizer
Go to Top of Page

Starting Member

5 Posts

Posted - 2012-04-07 : 22:02:58

TG's example here is exactly what I need for my project, but I am working in SQLite, where clustered isn't part of the scheme.

Is there a way I can implement this in SQLite to get the same kind of multilevel hierarchical functionality?
Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 2012-04-07 : 22:13:54
The clustered index is only used to maintain a particular physical order in SQL Server. It may not be necessary for the remaining SQL logic to work correctly.

SQLTeam is a Microsoft SQL Server website. You can Google for SQLite forums that may be able to help, if any of the posted code doesn't translate.

Go to Top of Page

- Advertisement -