|
|
Posted by Nick Chan on 09/17/07 02:25
the retrieval is of course still very fast
but updates was getting slower and slower, noticably starting from 75K
nodes. like it used to take less than 3 sec to add a node. now it
kinda take 11sec.
it was a dual xeon with 6gb ram, win2k3 ent, sql ent. and just me
handling it (me not dba, just asp.net programmer)
given a better server and dba, it may not be 75k nodes.
we moved to a better server because it was growing rapidly.
so i 'created', out of desperation, another structure because my boss
was mad at me because of the 11sec.
it is unorthodox and im kinda embarassed to discuss it. im just self-
taught
briefly it is like this
A
/ \
B C
/ \ /\
D E F G
/
H
so the table looks like this
id node parent level
1 A null 1
2 B A 1
3 D A 1
4 D B 2
5 E A 1
6 E B 2
7 H A 1
8 H B 2
9 H D 3
10 C A 1
11 F A 1
12 F C 2
13 G A 1
14 G C 2
so if i add another node under H, say 'X',
i would insert records for X, like this
15 X A 1
16 X B 2
17 X D 3
18 X H 4
it's fast, because i just retrieve all parents of H, make a copy and
insert 1 more record (X-H-4).
u can imagine how big the table is going to be. from 75K rows (celko
tree) to about 4-5million rows (new table).
in our 'financial report', we have take a selected node, and select
its parent. so with this table , i just do a simple select * from
where node='X',
I 'imagine' the growth would be logarithmic like this :
http://www.ifi.uio.no/it/latex-links/STORE/opt/rsi/idl/help/online_help/images/objaxes2.gif
rather than exponential, because in our table, the max level column
grow slower and slower.
id column is clustered, node column is indexed.
another table will store each node's number of child nodes.
but it works so well and we have eliminated the celko-tree just
recently for very large apps
i welcome any constructive criticism, because i'm quite inexperienced,
and am a college dropout.
On Sep 15, 6:00 am, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> ps : ur tree has been running in my server for 3 years , 150k
>
> nodes <<
>
> How is performance? Everyone asks for "Real World" examples of the
> Nested Sets model from someone other than me! I am not a "trusted
> source" :)
Navigation:
[Reply to this message]
|