Optimizing Trees

PeopleTools have enhanced the Tree Manager API reducing the time taken to load pivot grid models with more than thousand leaves. Pivot grid models with multiple trees and deep trees load faster.

Pivot Grid invokes tree manager API CTamTreeAccess::PVGWhereStringTokens by passing tree meta data. Tree Access Manager returns the appropriate SQL phrases (string tokens containing PSTREESELECT table number & additional Query Criteria) for the FROM and WHERE clauses. Pivot Grid then appends the Query Criteria to the existing Pivot Grid Query.

Pivot Grid Query further performs a join with PSTREESELECT & PSTREENODE tables to retrieve tree data.

Tree Select tables are frequently used by Pivot Grid for accessing Tree data. Substantial data change operations such as insert, update or delete, changes the data distribution in the table or the indexed view and makes the statistics irrelevant. This happens when the correct data distribution does not reflect on the correct column or index, which results in the slow execution of Pivot Grid Query.

To make the pivot grid models load faster with multiple trees or deep trees, PeopleTools has:

  • Updated tree selector table statistics.

  • Added indexes.

  • Used dynamic selectors.

Updating statistics ensures that queries compile with up-to-date statistics.

The Application Engine program TREESTATUPD updates table. You can configure the program to run at certain intervals.

Multi-Column Indexes

Relational databases allow indexes over multiple columns, so that if you have Where criteria for two or more columns in the index, the database manager can use one index to satisfy criteria on multiple columns at a time. Having the pertinent criteria columns in an index, however, does not guarantee that index will be used or that it will be used effectively on all the columns that have criteria.

There are three types of system tables in Tree Selectors and Controls:

  • PSTREESELCTL: Tree Selection Control table

    This system table controls and manages static selectors. Each row in this table corresponds to a row in PSTREEDEFN and to a group of rows (with the same SELECTOR_NUM) in PSTREESELECTnn. This table is only used for static selectors.

  • PSTREESELECTnn: Tree Selector tables

    The diagram shows the relationship between regular tree definition system tables with its counterpart of cache tables

  • PSTREESELNUM: Tree Selector Number

    The diagram shows the relationship between regular tree definition system tables with its counterpart of cache tables

Image: Tree Definition System Tables

The diagram shows the relationship between regular tree definition system tables with its counterpart of cache tables.

The relationship between regular tree definition system tables with its counterpart of cache tables.

Tree manager provides the options to improve the database performance:

  • Static Selectors

    • Selector represents entire tree data

    • Selector tables are updated only when tree is modified

  • Dynamic Selectors

    • Selector represents only requested nodes

    • Selector tables are updated with each query run.