nested tree, how to ?

hi, i've problems representing nested tree in sql server strucutre ...
my scenario is :
several process (p1,p2,p3,etc..) makes several operations
(op1,op2,op3,...)
i need to store the combination process,operation,time and this ismy
table
structure (processid,opid,dateop)
now i need to show a tree hystory of the operations in a definited date
range...
here's a sample:
op1
50% makes op2
20% makes op1
60% makes op3
100% makes op4
20% makes op5
50% makes op3
100% makes op6
100% makes op1
the real problem is the amount of data ., about 20 000 000 records ...
how can represent this tree in an efficient way ?!'!!?
thanks in advance for help
andrewI'm guessing you don't want to display all 20M lines at once, so what are yo
u
really trying to 'represent' ?
So, make sure you have an index on processid, and another on dateop.
Populate a temporary table with the processes you're interested in. Then add
to that table the details of the sub-processes, and repeat until there's
nothing more to add. You'll go through the table only once for each level yo
u
need to go down.
To make sure you only ask the 20M row table for the data you need, mark
things as done once you've pulled the data in. So, 3 values. 0 = new, 1 =
processing, 2 = done. Populate with 0. Then increment everything. Then pull
in new values (defaulting to 0), for the records that are marked with 1.
For making sure that it's ordered correctly, try using a string which
indicates the node you're looking at, with something appended to it. After
all, "aba" will come between "ab" and "ac". You might want to use lots of
characters per node though, etc... but string sorting may well work for your
ordering here better than numbers.
Hope this helps... I'm sure other people will have better ideas though.
"andrew" wrote:

> hi, i've problems representing nested tree in sql server strucutre ...
> my scenario is :
> several process (p1,p2,p3,etc..) makes several operations
> (op1,op2,op3,...)
> i need to store the combination process,operation,time and this ismy
> table
> structure (processid,opid,dateop)
> now i need to show a tree hystory of the operations in a definited date
> range...
> here's a sample:
> op1
> 50% makes op2
> 20% makes op1
> 60% makes op3
> 100% makes op4
> 20% makes op5
> 50% makes op3
> 100% makes op6
> 100% makes op1
> the real problem is the amount of data ., about 20 000 000 records ...
> how can represent this tree in an efficient way ?!'!!?
> thanks in advance for help
> andrew
>|||Please post sample data the the tree below is based on.
So far, you don't have anything that sounds like a tree datawise, since you
have not explained any relationships. Trees have parents and children, what
you posted doesn't seem to have either, outside of the formatting of the
results.
Supervisors and employees is a good example of a tree. We all understand
how that relationship works. Explain how yours works and we will be able to
give you much better advice.
"andrew" <cekgroup@.yahoo.com> wrote in message
news:1147337447.998183.307900@.i39g2000cwa.googlegroups.com...
> hi, i've problems representing nested tree in sql server strucutre ...
> my scenario is :
> several process (p1,p2,p3,etc..) makes several operations
> (op1,op2,op3,...)
> i need to store the combination process,operation,time and this ismy
> table
> structure (processid,opid,dateop)
> now i need to show a tree hystory of the operations in a definited date
> range...
> here's a sample:
> op1
> 50% makes op2
> 20% makes op1
> 60% makes op3
> 100% makes op4
> 20% makes op5
> 50% makes op3
> 100% makes op6
> 100% makes op1
> the real problem is the amount of data ., about 20 000 000 records ...
> how can represent this tree in an efficient way ?!'!!?
> thanks in advance for help
> andrew
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. What you posted makes no sense.
You have described a matrix (ops by processes), not a tree. In a tree,
one process might have one or more operations as subordinates.
You show a cycle where OP1 is one of its own subordinates. Trees do
not cycles.
Have you gotten a copy of TREES & HIERARCHIES IN SQL?|||> Have you gotten a copy of TREES & HIERARCHIES IN SQL?
Thanks I think i need it ?!?!? :-)
here is a sample of my table
TABLE : OPERATIONSLOG
--
LogID , <-- PK
CompanyID ,
ProcID ,
OperationID ,
PreviousOperationID ,
OperationDate ,
OperationName ,
PreviousOperationName
SAMPLE DATA
--
1,1, 1, null , 2006-01-01 10:00 <-- ENTRY OPERATION
1,1, 2, 1 , 2006-01-01 10:17
1,1, 5, 2 , 2006-01-01 10:32
1,1, 1, 5 , 2006-01-01 10:36
1,1, 2, 5 , 2006-01-01 10:36 --> EXIT OPERATION
1,2, 3, null , 2006-01-01 10:00 <-- ENTRY OPERATION
1,2, 1, 3 , 2006-01-01 10:06
1,2, 4, 1 , 2006-01-01 10:15 --> EXIT OPERATION
2,3, 6, null , 2006-01-02 10:00 <-- ENTRY OPERATION
2,3, 7, 6 , 2006-01-02 10:26
2,3, 11, 7 , 2006-01-02 10:46
2,3, 1, 11 , 2006-01-02 11:06
2,3, 2, 1 , 2006-01-02 12:06
2,3, 4, 2 , 2006-01-02 13:15 --> EXIT OPERATION
1,4, 1, null , 2006-01-03 10:00 <-- ENTRY OPERATION
1,4, 4, 1 , 2006-01-04 13:15 --> EXIT OPERATION
this is a flat table, with all this data , and other not relevand
fields fot this result
i need a tree result with the history of my operations.
some like is
Give me flow for operation "1" in Company "1" from 2006-01-01 to
2006-01-31
Result :
Operation "1 "
Operations "2" (qty 1 process 50%)
Operations "5" (qty 1 process 100%)
Operations "1" (qty 1 process 100%)
Operations "2" (qty 1 process 100%)
Operations "4" (qty 1 process 50%)
Give me flow for operation "3" in Company "1" from 2006-01-01 to
2006-01-31
Result:
Operations "3" (qty 1 process 100%)
Operations "1" (qty 1 process 100%)
Operations "4" (qty 1 process 100%)
the problem is with 20.000.000 records... (for all companies)
a process always execute operations in the same companyid
about 20 companies ==> 1.000.000 per company :)
a possible idea is with sql 2005 to create a new table with one record
per process ...
my idea is to store in this table :
processid , and an XML Field tha represent the flow of operations.
So when someone request me a tree history i think to use xml native
performance in sql 2005
to create the result xml ( with an xquery or xpath ...)
then i can schedule a job that each night (for eaxmple) can add the new
process stored in the
huge flat table ...
dreams ?
thanks a lot for patience, support and help
thanks in advance !!!!
andrew|||>You show a cycle where OP1 is one of its own subordinates. Trees do
>not cycles.
yes but OP1 in the root level is not the same of the OP1 excecuted
after another
operation, is relevant the operation in a specific level of the tree
...
i hope to explain me, sorry for this !?!? (and for my english too ) ;-)|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147365462.119015.291560@.u72g2000cwu.googlegroups.com...
> ...Have you gotten a copy of TREES & HIERARCHIES IN SQL?
>
It should be "...a copy of MY BOOK, TREES & HIERARCHIES IN SQL.
When you recommend your own book, you should have the moral obligation to
send a copy to the poster.
Free of charge (OK, the poster can pay transport) and autographed, of
course.|||If they buy a copy, then I promise NOT to autograph it. You cannot
return a book with an auto graph nto the distributor. I used to own
some bookstore.
.