NYCPHP Meetup

[Hans Zaunere]

Mitch Pirtle wrote on Thursday, October 27, 2005 8:20 PM:
> On 10/27/05, Hans Zaunere <lists at zaunere.com> wrote:
> > 
> > Ahh, good old NSM...
> > 
> > New York PHP developed an implementation of NSM, pNSM, as part of
> > the clew project.
> 
> Then let's start that discussion over here, about the performance
> benefits that turn into performance hits when your one table has 300
> million rows...

Sure - the more rows, the more time it takes to do updates.  Keep in mind,
however, that while a lot of rows may need to be touched, it's lightweight.
The only operation is an update of an integer - based on an integer
constraint.

> Seriously, this concept makes lots of things seem easier, but next
> thing you know, you got a really looooooong table on your hands and
> only Oracle (IIRC) can partition across tablespaces at the table
> level.

As Snyder mentioned, we were just discussing this after last week's meeting.
Partitioning can help dramatically, but what if those types of features
aren't available?  Well, while the update of many rows may seem to be a big
hit, I'd much rather do that than recursive queries, especially in deep
trees.

> What to do when your nested table becomes more than you can manage? Is
> it time then to go back to a more normalized model, or is this an
> indication that - before you even implement - you have to take the
> eventual number of rows that your nested table might have to manage?

As Roland points out, for huge tree structures, it's better done outside of
SQL.  SQL isn't well suited for describing complex tree structures, but NSM
has been the most versatile, robust and fastest implementation I've seen.
I've had test trees with well over 100k rows and no problems.

The 80/20 rule applies here - for the majority of tree implementations in
SQL, NSM is the elegant solution.  If you're maintaining giant structures,
however, then a relational database probably isn't the right choice anyway.
But NSM solves the common problem of trees in your typical php/mysql/web
environment.


---
Hans Zaunere / President / New York PHP
   www.nyphp.org  /  www.nyphp.com