| Author |
Topic |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2007-05-03 : 12:02:29
|
| Trying to implement a Nested Set Model in sql server using this as a reference: http://dev.mysql.com/tech-resources/articles/hierarchical-data.htmlThe section labeled "Find the Immediate Subordinates of a Node" contains this query:SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depthFROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.name, (COUNT(parent.name) - 1) AS depth FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'PORTABLE ELECTRONICS' GROUP BY node.name ORDER BY node.lft )AS sub_treeWHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.nameGROUP BY node.nameHAVING depth <= 1ORDER BY node.lft;I am getting the following error:Msg 1033, Level 15, State 1, Line 13The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-03 : 12:06:43
|
| Either remove the ORDER BY in the subquery or add a TOP X.Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-03 : 12:52:16
|
| If you are using SQL 2005, there is no reason to use nested sets. Just use recursive CTE's and model your data with a simple parentID column. Simple, quick, efficient, easy, none of the overhead or complexity of the nested set model.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2007-05-03 : 14:25:21
|
| Jeff,Here is a list of all the features with Nested Sets:- Retrieving a Full Tree - find all the leaf nodes- retrieving a single path- finding the depth of the nodes- finding the depth of a sub-tree- finding the immediate subordinates of a node- adding a new node- deleting nodesSo you are saying with CTE (UNION ALL -> recursion) we can do everything a nested set can do? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-03 : 14:33:49
|
| Yep, but even easier and more efficiently. Remember that adding/deleting has nothing to do with CTE -- those are just simple, single INSERT/UDPATE/DELETE statements if you data is modelled properly and you have cascading deletes and updates in effect. The set-based model requires quite a bit of work to keep things in synch as your data changes. For a normal parent/child model, you just DELETE or INSERT or UPDATE, and you're done.The only time you need CTE's is for SELECTS, and as the examples I linked you to show, it is pretty easy to get whatever you need.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2007-05-03 : 14:37:25
|
| Jeff,yeah the nested method means you have to updated all nodes when you insert, with a simple parent/child database you update fewer rows. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-03 : 14:41:39
|
| yeah, the maintenance is difficult. Without the ability to do hierarchical sql statements, the nested set model might be necessary, and it is an interesting technique overall, but I think it is really not needed if you have access to SQL 2005's features...- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2007-05-03 : 15:04:25
|
| I wonder if Joe Celko has commented on sqlserver 2005's new feature and how it compares to nested sets... |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2007-05-03 : 15:08:40
|
| hehe, I want to test the difference (performance mainly) between the two on a table of 1 million rows. The issue is insert meaningful data in the nested set model is pretty tricky! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2007-05-04 : 09:16:48
|
| Jeff,So assuming you haven't done any testing comparing the two methods (nested sets versus adjec.), which one would you say is faster? (assuming sql server 2005 w/cte).Would it depend on the number of nodes and if the tree is wider or long? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-04 : 09:20:00
|
| I haven't tested; we KNOW that parent/child is faster for DML operation (INSERT/UPDATE/DELETE). As for SELECT's, I suspect that parent/child is also faster, but I have not tested. you're right, it may depend on the width of the tree, but overall, I just don't see a good reason to use nested sets.Do you have a large amount of existing nested-set data? It should be pretty easy to quickly convert it to parent/child to do testing.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2007-05-04 : 09:29:29
|
| Actually I am just going back and forth on which model to use (starting a new design), I am totally for adjacency model using CTE while others are leaning towards nested-set. But nested set is much more complicated...I mean glancing at the raw rows in the db are pretty meaningless while with adj. you can make sense of it as its much more intiutive and easier to recover if nodes get messed up somehow.I think its a nobrainer since the applicaion will have quite allot of inserts/updates/deletes. |
|
|
|
|
Nested Model query issues