hierarchical data problem

• cek

  Newbie

  Points: 8

  More actions

  May 11, 2006 at 3:16 am

  #168287

  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 ?!??!!?

  can sql 2005 and XML data structure help me ?

  and analisys service ?

   

  thanks in advance for help

  andrew

• peterhe

  SSChampion

  Points: 11469

  More actions

  May 11, 2006 at 7:17 am

  #637229

  What you need is properly define the PK (or index) of the table. The key should contain dateop,processid, and opid and in this order.

  Since you are querying for date range, the query will be quick based on the PK.