Information at link.

I'll write this down so I won't forget it. This common table expression enables you to omit CURSORS and apparently it goes quicker.

This query

PLAIN TEXT
SQL:
  1. INSERT #TempTreeStructures (TreeStructureId) VALUES (@RootTreeStructureId)
  2.  
  3. SET @RootRightValue = @RootRightValue - 1
  4.  
  5. SELECT  @RightValue = RightValue,
  6. @TreeStructureId = TreeStructureId
  7. FROM    TreeStructures
  8. WHERE   TreeId = @TreeId AND
  9. LeftValue = @RootLeftValue + 1
  10.  
  11. WHILE (@RightValue <@RootRightValue)
  12. BEGIN
  13. INSERT #TempTreeStructures (TreeStructureId) VALUES (@TreeStructureId)
  14. SELECT  @RightValue = RightValue,
  15. @TreeStructureId = TreeStructureId
  16. FROM    TreeStructures
  17. WHERE   TreeId = @TreeId AND
  18. LeftValue = @RightValue + 1
  19. END
  20.  
  21. INSERT #TempTreeStructures (TreeStructureId) VALUES (@TreeStructureId)
  22. SELECT  ts.TreeStructureId,
  23. ts.LeftValue,
  24. ts.RightValue,
  25. tn.Name,
  26. tn.Type,
  27. tn.DATA ,
  28. tn.Description,
  29. tn.DateChanged ,
  30. tn.UserId
  31. FROM    TreeStructures ts
  32. JOIN    TreeNodes tn ON ts.TreeStructureId = tn.TreeStructureId
  33. WHERE   ts.TreeStructureId = (SELECT TOP 1 TreeStructureId FROM #TempTreeStructures)
  34. AND tn.TreeNodeId = (SELECT TOP 1 tn2.TreeNodeId FROM TreeNodes tn2 WHERE tn2.TreeStructureId = ts.TreeStructureId ORDER BY tn2.DateChanged DESC)
  35. UNION ALL
  36. SELECT  ts.TreeStructureId,
  37. ts.LeftValue,
  38. ts.RightValue,
  39. tn.Name,
  40. tn.Type,
  41. tn.DATA ,
  42. NULL AS [Description],
  43. NULL AS DateChanged,
  44. tn.UserId
  45. FROM    TreeStructures ts
  46. JOIN    TreeNodes tn ON ts.TreeStructureId = tn.TreeStructureId
  47. WHERE   ts.TreeStructureId IN (SELECT TreeStructureId FROM #TempTreeStructures) AND
  48. ts.TreeStructureId <> (SELECT TOP 1 TreeStructureId FROM #TempTreeStructures) AND
  49. tn.TreeNodeId = (SELECT TOP 1 tn2.TreeNodeId FROM TreeNodes tn2 WHERE tn2.TreeStructureId = ts.TreeStructureId ORDER BY tn2.DateChanged DESC)

could be replaced by this one

PLAIN TEXT
SQL:
  1. ;WITH TreeData (TreeStructureId, LeftValue, RightValue, TreeId)
  2. AS (
  3. SELECT TOP 1
  4. TreeStructures.TreeStructureId
  5. , TreeStructures.LeftValue
  6. , TreeStructures.RightValue
  7. , TreeStructures.TreeId
  8. FROM TreeStructures
  9. WHERE TreeStructures.TreeId = @TreeId
  10. AND TreeStructures.LeftValue = @RootLeft + 1
  11. UNION ALL
  12. SELECT
  13. TreeStructures.TreeStructureId
  14. , TreeStructures.LeftValue
  15. , TreeStructures.RightValue
  16. , TreeStructures.TreeId
  17. FROM TreeStructures
  18. INNER JOIN TreeData ON TreeStructures.LeftValue = TreeData.RightValue + 1
  19. WHERE TreeStructures.TreeId = TreeData.TreeId
  20. )
  21. SELECT  TreeStructures.TreeStructureId,
  22. TreeStructures.LeftValue,
  23. TreeStructures.RightValue,
  24. TreeNodes.Name,
  25. TreeNodes.Type,
  26. TreeNodes.DATA ,
  27. TreeNodes.Description,
  28. TreeNodes.DateChanged ,
  29. TreeNodes.UserId
  30. FROM    TreeStructures
  31. JOIN    TreeNodes ON TreeNodes.TreeStructureId = TreeStructures.TreeStructureId
  32. WHERE   TreeNodes.TreeNodeId = (
  33. SELECT TOP 1 tn2.TreeNodeId
  34. FROM TreeNodes tn2
  35. WHERE TreeStructureId = TreeStructures.TreeStructureId
  36. ORDER BY DateChanged DESC
  37. )
  38. AND TreeStructures.TreeStructureId = @RootTreeStructureId
  39. UNION ALL
  40. SELECT  TreeData.TreeStructureId,
  41. TreeData.LeftValue,
  42. TreeData.RightValue,
  43. TreeNodes.Name,
  44. TreeNodes.Type,
  45. TreeNodes.DATA,
  46. TreeNodes.Description,
  47. TreeNodes.DateChanged ,
  48. TreeNodes.UserId
  49. FROM TreeData
  50. JOIN TreeNodes ON TreeNodes.TreeStructureId = TreeData.TreeStructureId
  51. WHERE TreeNodes.TreeNodeId = (
  52. SELECT TOP 1 tn2.TreeNodeId
  53. FROM TreeNodes tn2
  54. WHERE TreeStructureId = TreeData.TreeStructureId
  55. ORDER BY DateChanged DESC
  56. )
  57. ORDER BY LeftValue
  58. ;

I'll have to read some more on it to fully understand its capabilities.

thx, Lieven Cardoen