MS SQL server and Trees

Question

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

Answer 1

Have a look at the HIerarchyID Data type - that is done exactly for that stuff.

Besides that - your recursion is about the worst way to get along with that. You should go into that procedureally, possibly, aggregating data into a temporary table as needed. Or - just forget about it. Seriously - Tree structures should not be put up on program start, but on demand, 23.000 items should just not be loaded without need.

THat STILL being said - 2:30 minutes is too long either. For something that is to be compuited in memory. Are you sure you have proper indices on your tables? Can you publish the query plan for the above query so we can check? Looks to me like you run into a SQL Design issue that forces lots of table scans.

Answer 2

Thanks, The main issue is that the data alreay exists and has done for a long time

The was no problems utill the boss asked for the main parent (ie root + 1) to be displayed be each item when displayed on the screen, When in tree mode not a problem as it loads nodes on demand, its when I need to list the selected noes, (ie 90+) with their main parent.

Currenty one of the 'Graduate developes' used temp tables and scanned back thro the the table parent by paent untill the right ones were found, this took like 30 seconds per node.

I trying to think of a better way of getting this info with out redisinge the tables and then having to deploy change scripts to all the clients.

even worce we no have to display the main parent when doing an ajax filetered lookup so its got to to be very fast < 1 second! as we filter as you type.

I is looking like I may have to redesign the tables :(

Also I think I am going to have the same problems with GeoPlantData which contains over 8.5m rows !!!!

Thanks for the info

Julian