Re: Nested Sets and a typical Extranet Example

Just as a side issue as the use of materialized path often requires per-row record UDF execution, it is going to slow things down a hell of a lot on large trees. I found this when I was playing around with my 10,000 node tree. So even with materialized path, unless the function is very simple, i cannot see much benefit.

"Robin Tucker" <r.tucker_at_thermoteknix.com> wrote in message news:bka2ui$qnj$1$8300dec7_at_news.demon.co.uk...
> I've had the same problem. There is no complete implementation for
> reference, so I have to figure it out for myself, which is time consuming.
>
> I've ditched nested sets for now and have chosen to use an adjacency list
> with a stored paths field for sorting the tree structure (like this
> "\00000045\00000004\") etc. Yes, it takes up more space and yes things
will
> be somewhat slower, but I don't need to use recursive selects to generate
> subtrees. I figure this is the neatest solution. Of course, if I had a
> materialized path algorithm that actually worked (note to Tropashko, your
> implementation suffers from arithmetic overflows with MS-SQL and large
> trees) then I could replace the path strings with something less
profligate.
>
>
> "rivers" <member38170_at_dbforums.com> wrote in message
> news:3381253.1063812208_at_dbforums.com...
> >
> > Originally posted by --Celko--
> >
> > > >> I'm not sure if I asked this in the original post but how
> > > receptive
> >
> > > is this model to constant updates, moves, additions, deletions of
> >
> > > nodes and children? <<
> >
> > >
> >
> > > There are better models for that situation, but it is not as bad as
> >
> > > people think.
> >
> > >
> >
> > > The tree structure is in a table with only two integers and a foreign
> >
> > > key to the nodes table, so you get a lot of data pags into cache. The
> >
> > > changes are made from one point in the numbering to the end of the
> >
> > > table. So if the table is stored in a sorted file structure (a
> >
> > > clustered index in Sybase/SQL Server terms), this is done via a scan
> >
> > > fromthat point forward. This allows the other rows before the point
> >
> > > to be used by other queries and for the engine to do page locking and
> >
> > > releasing pretty fast.
> >
> > >
> >
> > > The major advantages of nested sets are the algebraic properties and
> >
> > > how you can use them to get reports and summary information.
> > >
> >
> >
> >
> >
> >
> > I've been beating my head over and over on this and whether I should
> > simply just stay with the adjacency model I'm using today or should I
> > reconfigure all my code to work with nested sets.
> >
> >
> >
> > The biggest hurdle for me is finding a complete example (that works for
> > MS SQL) that covers from scratch inserting/deleting/moving items from
> > the trees. The examples are all over the place and it's becoming
> > daunting for someone new to understand how it all works together. As I
> > see it this is the biggest reason most people stick with adjacency model
> > because of the support and practical working examples available today.
> >
> >
> >
> > I understand you have a book out "SQL for smarties" and that you ask
> > people to purchase it. I will be purchasing the book not just for the
> > nested sets information but for a resource.
> >
> >
> >
> > Is it possible for you to post a "clean" working example of
> > inserting/updating/deleting/moving a tree item? Or is this only
> > something which can be found in your book.
> >
> >
> >
> > I appreciate every article you've writing on the subject and those
> > publications which carry your re-prints.
> >
> >
> >
> > Thanks very much Mr. Celko!
> >
> >
> >
> > Rob M.
> >
> > Intermediate SQL/ASP programmer
> >
> >
> > --
> > Posted via http://dbforums.com
>
>
Received on Wed Sep 17 2003 - 19:33:19 CEST