On 18 Aug 2004 at 12:26, kaczy27 wrote:

>since it is the speed not the space that is important I guess I'll
>invest into another column in a million record table, or perhaps I'll
>create another table just to store the records that need to be
>recalculated and store the trigger there. It will break somewhat the
>design, but still what for one would need a design if the instance
>doesn't work.

Whatever you do, keeping summary columns anywhere is not multiuser
safe. There are always cases when you'll finish with wrong sum.
I'd suggest you calculate your sums on-the-fly when they are
needed. Of course, you are going to argue that it is slow, because it
requires tree-walk. But I have a recipe for this. If your tree is
limited in depth and width (i.e. branch can't have more than XXXX sub-
branches) you can use incremental keys.
Key of a record in this case is a string, containing key of parent
record plus sub-key of leaf.

Example:

KEY Value
A01 Head office
A01000 Branch office
A01000FVZ Sub-branch office
A01000FVY Another sub-branch office

In this case extracting all childs is simple: .... WHERE KEY
STARTING WITH 'A01'. And number of other useful tricks are available.
For example, select all items of second level and deeper: WHERE KEY
LIKE '______%'. Only second level conform the mask '______'.

But if your tree is unlimited, there is other trick, called 'table
of relationship'. An additional table where all childs and grand-
childs of a node are listed.
Extracting of sub-trees in this case also simple and fast,
especially for really big trees, when indices become quite
selectable.

SY, Dimitry Sibiryakov.