Acts_as_nested performance query


#1

Wondering about using the acts_as_nested for a model but concerned about
the performance overhead of having to rewrite the table each time and
entry is added or removed(and locking it while it’s happening or risking
data integrity probs). Wasn’t really familiar with the structure so did
a little reading up on it, and one solution to this seems to spreading
the table out, so that [using the table in the API as an example],
instead of:

ID | PARENT | LEFT | RIGHT | DATA
1 | 0 | 1 | 14 | root
2 | 1 | 2 | 7 | Child 1
3 | 2 | 3 | 4 | Child 1.1
4 | 2 | 5 | 6 | Child 1.2
5 | 1 | 8 | 13 | Child 2
6 | 5 | 9 | 10 | Child 2.1
7 | 5 | 11 | 12 | Child 2.2

you would do this:

ID | PARENT | LEFT | RIGHT | DATA
1 | 0 | 10 | 140 | root
2 | 1 | 20 | 70 | Child 1
3 | 2 | 30 | 40 | Child 1.1
4 | 2 | 50 | 60 | Child 1.2
5 | 1 | 80 | 130 | Child 2
6 | 5 | 90 | 100 | Child 2.1
7 | 5 | 110 | 120 | Child 2.2

Then if you added a new entry 8, which was a child of 2, for example,
you could give it a lft of 121 and a rgt of 122, meaning no full-table
re-write. Of course this fails down eventually and does require at least
a partial table rewrite (and you’d prob want to do a multiple of more
than 10 on the entries to begin with), but it would certainly cut down
the probs. Anybody got any comments? There’s probably a good reason why
the AR implementation takes the full-rewrite approach, and I’m pretty
much a noob (not a programming background) so feel free to shoot me down
in flames.

The new table would look like:

ID | PARENT | LEFT | RIGHT | DATA
1 | 0 | 10 | 140 | root
2 | 1 | 20 | 70 | Child 1
3 | 2 | 30 | 40 | Child 1.1
4 | 2 | 50 | 60 | Child 1.2
5 | 1 | 80 | 130 | Child 2
6 | 5 | 90 | 100 | Child 2.1
7 | 5 | 110 | 120 | Child 2.2
8 | 5 | 121 | 122 | Child 2.3

Cheers
Chris T