Setting Tree Performance Options

You can set performance-enhancing options to improve the database performance of queries that use trees as selection criteria. This includes queries to which tree criteria are added by PeopleSoft nVision layouts and scopes. Both PeopleSoft Query and PeopleSoft nVision use the tree performance options. The performance options do not impact PeopleSoft Tree Manager itself, only the performance of programs that use the tree data.

Because of the many variations of data distributions, indexes, queries, and optimizers, you can select SQL techniques that will tune your queries for optimum performance. By specifying these options at the tree level, your trees can be used in queries whether they are run through PeopleSoft nVision. Those options, however, can be overridden by the performance options set at the PeopleSoft nVision level.

Use the Performance Options page (PSTREEDEFNPRPTY) to select options to enhance tree performance as well as performance for queries that involve trees and nVision.

Use these steps to access the Performance Options page:

  1. Select Tree Manager, Tree Manager.

  2. Create a new tree, or search for an existing tree.

  3. On the Tree Manager page, click the Tree Definition link.

  4. Click the Performance Options link on the Tree Definition and Properties page.

    The Performance Options page appears.

Image: Performance Options page

This example illustrates the fields and controls on the Performance Options page. Definitions for the fields and controls appear following the example.

Performance Options page

Field or Control

Definition
Access Methods

Select an access method from:

  • Use Literal Values: Eliminates a SQL join by retrieving the detail ranges associated with the selected node and coding them in the SELECT statement.

    Note: This option is not available for use with winter trees.

  • Sub-SELECT Tree Selector: Instead of adding the tree selector to the FROM list of the main query, the tree selector criteria and its relation to the data in the main query is within an EXISTS clause in the WHERE portion of the main query. This is called a correlated subquery.

  • Join to Tree Selector: Includes the tree selector table in the FROM clause and uses join criteria to select the appropriate rows from the fact table. This method is sometimes used by PeopleSoft nVision, even when another method is specified, if tree node information is needed to produce the report.

  • Use Application Defaults: Uses the tree performance options specified in the applications that use this tree. PeopleSoft nVision defaults to the tree performance options specified in a PeopleSoft nVision layout’s options. If performance options are not specified in PeopleSoft nVision, the PeopleSoft nVision’s default performance options are used. For PeopleSoft Query, this option defaults to the query’s sub-SELECT method.
Tree Selectors

Select a tree selector type from the following options:

  • Static Selector: A technique in which a selector represents the entire tree remains valid until the tree changes.

  • Dynamic Selectors: A technique in which PeopleSoft nVision creates a new tree selector for use in a section of a single report. The dynamic selector represents just the requested nodes.
Selector Options

Select a selector option from the following values:

  • Single Values: Used only with dynamic selectors. This technique causes PeopleSoft nVision or PeopleSoft Query to build a selector using the individual detail values (from the detail table specified in the tree structure) that fall within the detail ranges of the selected nodes.

  • Range of Values (>= <=): For a tree with ranges of values, this makes the selectors more compact (fewer rows) and less likely to become obsolete as detail values are added. For some database optimizers, the syntax fieldname >= RANGE_FROM_nn AND fieldname <= RANGE_TO_nn gets a better access plan than BETWEEN.

  • Range of Values (BETWEEN): Similar to the other Range of Values selector, but use the syntax fieldname BETWEEN RANGE_FROM_nn AND RANGE_TO_nn. This is a better choice for ranged selectors on most database platforms.
Auditing All Default Values

Note: The Auditing All Default Values group box is read-only when you work with winter trees.

If you've selected the All Detail Values in this Tree option on the Tree Definition and Properties page and clicked the Performance Options link, you can select from the following values:

  • Use All Tree Keys (Default): This is the existing default auditing behavior. Use this option if the performance time is acceptable.

  • Use Not Empty Tree Keys: If the performance time is unacceptable, attempt this option to expedite the process. If the performance time is still unsatisfactory, then attempt the Use This Audit Only in Batch option.

  • Use This Audit Only in Batch: If you select this option, the All Detail Values in This Tree option audit is ignored during online tree auditing or saving. Consequently, the All Detail Values in This Tree option audit is run only through the Application Engine tree audit process. Although Tree Manager allows you to proceed to save a tree as a valid tree, you should use the Save Draft option. After you've saved the tree, proceed to start the Application Engine auditing process.

    Note: Use this option only if the first two options fail to achieve the desired performance results.
Tree Change Message Options

Select a Tree Change Message option from the following:

  • Send Tree Change Message: Select this option to send a TREE_CHANGE message when Tree changes are made.

  • Don't send Tree Change Message: Select this option if there are no subscribers to TREE_CHANGE service operations.