Re: Performance issues with connect by clause with Trees

[Tony DeLia <tdelia@EROLS.COM>]

Jerry,
   Tree performance is pretty slow to begin with... Using the CONNECT BY
clause to access tree nodes is even slower... Do you just want to list
the Tree Nodes indented by level? In my opinion SQR is a better
alternative to the CONNECT BY clause... You have more control over the
processing... I've used both and SQR outperforms CONNECT BY... Here's
the Mainline logic of a Tree Structure Listing I wrote... It lists one,
some or ALL trees... PeopleSoft has an extremely messy version but
that's the style of all their SQR programs... I'll be posting the entire
program when I get a chance...

http://www.erols.com/tdelia/sqr.html


!**********************************************************************
!*                                                                    *
!*       MODULE:  TDTREE.SQR                                          *
!*       AUTHOR:  TONY DELIA.                                         *
!*         DATE:  07/02/97.                                           *
!*       SYSTEM:  TD SQR UTILITY SERIES.                              *
!*         DESC:  PSTREENODE LISTING.                                 *
!*                                                                    *
!**********************************************************************
!*                                                                    *
!*       TABLES:  pstreenode          - Select                        *
!*                pstreedefn          - Select                        *
!*                pstreestrct         - Select                        *
!*                psrecdefn           - Select                        *
!*                psrecfield          - Select                        *
!*                pstreeleaf          - Select                        *
!*                                                                    *
!*               < All other tables are accessed DYNAMICALLY >.       *
!*                                                                    *
!**********************************************************************
....

begin-select

a.setid
a.tree_name
a.effdt
a.tree_node
a.tree_branch
a.tree_node_num
a.tree_node_num_end
a.parent_node_num
a.tree_node_type

  if $treename <> &a.tree_name
  or $setid    <> &a.setid
     do New-Tree
  end-if

  do Move-Tree
  do Find-Tree

  do Print-Tree
  do Print-Leaf

 from pstreenode       a
where a.tree_name like $tree
  and a.effdt          =
      (select max(a2.effdt)
         from pstreenode      a2
        where a2.setid      = a.setid
          and a2.tree_name  = a.tree_name
          and a2.effdt     <= $AsOfDate)
order by a.setid            asc,
         a.tree_name        asc,
         a.tree_node_num    asc,
         a.parent_node_num  asc

end-select

....

Sorry - That's all I'm posting for now... it's another busy day!!! ;o)

                                        -Tony DeLia

Jerry Kight wrote:
>
> I have a very rudimentary tree set up using tree-manager in Peoplesoft
> (Oracle 7)
>
> When I use the "connect by" clause with a "start with" as the first
> tree_node_num in the tree, it takes a couple of hours to run.
>
> Any ideas on how performance can be tuned?
>
> SELECT  SETID,
> TREE_NAME,
>  EFFDT,
> TREE_BRANCH,
> TREE_NODE_NUM,
> TREE_NODE,
> TREE_LEVEL_NUM,
> TREE_NODE_TYPE,
> PARENT_NODE_NUM,
> TREE_NODE_NUM_END
> FROM PSTREENODE
>     WHERE   SETID = 'ACSHR'
> START WITH  TREE_NODE_NUM = 1
> CONNECT BY PRIOR  TREE_NODE_NUM =  PARENT_NODE_NUM;

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com