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