MS SQL server and Trees
- by Julian
Im looking for some way of extrating data form a tree table as defined below.
Table Tree Defined as :-
TreeID uniqueidentifier
TreeParent uniqueidentifier
TreeCode varchar(50)
TreeDesc varchar(100)
Data some (23k rows), Parent Refs back into ID in table
The following SQL renders the whole tree (takes arround 2 mins 30)
I need to do the following.
1) Render each Tree Node with its LVL 1 parent
2) Render all nodes that have a Description that matches a TreeDesc like 'SomeText%'
3) Render all parent nodes that are for a single tree id.
Items 2 and 3 take 2mins30 so this needs to be a lot faster!
Item 1, just cant work out how to do it with out killing SQL or taking forever
any sugestions would be helpfull
Thanks
Julian
WITH TreeCTE(TreeCode, TreeDesc, depth, TreeParent, TreeID)
AS
(
-- anchor member
SELECT cast('' as varchar(50)) as TreeCode ,
cast('Trees' as varchar(100)) as TreeDesc,
cast('0' as Integer) as depth,
cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) as TreeParent,
cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) as TreeID
UNION ALL
-- recursive member
SELECT s.TreeCode,
s.TreeDesc,
cte.depth+1,
isnull(s.TreeParent, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)),
isnull(s.TreeID, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier))
FROM pdTrees AS S
JOIN TreeCTE AS cte
ON isnull(s.TreeParent, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) = isnull( cte.TreeID , cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier))
)
-- outer query
SELECT
s.TreeID, s.TreeCode, s.TreeDesc, s.depth, s.TreeParent
FROM TreeCTE s
