I'm less impressed after running some tests:

1) Index selectivity is great, but that doesn't necessarly
equal 'fast'.
2) Inserting a new node involved updating all parent records - which
is quite blocking. (those transactions had better be very quick
indeed!)
3) On my test tree of 900 nodes (up to 30 deep) this routine
performed over 3 times slower than a good old-fashioned 'parent'
foreign key system, when generating a subtree with 'indent' levels).
4) It's very fast at producing 'all children of' at item (without
showing their nested depth), but that isn't very useful to me. I
normally need the relationships between nodes to be given too.

After testing, I'll stick with the old technique. With Firebird's
fast stored procedure language, it performs very well.

Maybe I'll use this technique in something like MS SQL Server, which
isn't as good at recursive code...

John



--- In firebird-support@yahoogroups.com, "johnsparrowuk"
<jsparrow@e...> wrote:

> Wow! That is so cool! It's rare to find something thats both

simple

> and clever these days... :)
>
> And great for saving all those hard-to-optimise recursive sproc
> calls.
>
> Thanks!
>
> John
>
> --- In firebird-support@yahoogroups.com, "Martijn Tonies"
> <m.tonies@u...> wrote:
> >
> >
> > > Can we achieve the same in a single sql query?
> >
> > http://www.intelligententerprise.com/001020/celko.jhtml?
> _requestid=203273