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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Retireving hierarchical data
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

7 Posts

Posted - 08/26/2008 :  05:14:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 08/26/2008 :  05:19:32  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Post your query and some sample data. maybe someone will be able to optimise it.

Go to Top of Page

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 08/26/2008 :  08:40:14  Show Profile  Reply with Quote
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 - 08/27/2008 :  00:14:56  Show Profile  Reply with Quote
Refer this for an illustration
Go to Top of Page

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 08/27/2008 :  15:05:29  Show Profile  Reply with Quote
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
         UNIQUE NONCLUSTERED (parent,child)
 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

Edited by - TG on 08/27/2008 15:08:24
Go to Top of Page

Starting Member

7 Posts

Posted - 08/28/2008 :  06:47:35  Show Profile  Reply with Quote
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

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 08/28/2008 :  08:19:21  Show Profile  Reply with Quote
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 - 04/07/2012 :  22:02:58  Show Profile  Reply with Quote

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 - 04/07/2012 :  22:13:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000