PS/nVision is a Microsoft Excel based reporting tool that generates dynamic SQL based on the structure of the report layout. Note that it is not possible to rewrite the queries generated by nVision; but it is possible to affect the number of joins, create slightly more efficient joins, and limiting the selection criteria to specific portions of a tree. Before attempting to tune a nVision layout, it is beneficial to understand the structure of various tree tables and how PS/nVision uses them. The main tables used are
PSTREEDEFN: Tree Definition
Defines an effective-dated version of a tree.
PSTREELEAF: Tree Leaf
Defines the data value ranges that compose the leaves of a tree. For each “leaf node” (nodes without children) one or more ranges define the detail values that correspond to that node.
PSTREESELECTnn: Tree Select Work-Size nn
Defines Selectors used by PS/nVision to speed tree-based data selection. A selector table is defined for every possible detail field length (nn = 01-30).
PSTREESELNUM: Tree Select Control Number
PS/nVision uses this table to assign a unique SELECTOR_NUM value to each tree selector as it is built. This table has only one row.
What could be done to improve performance ?
Approach 1: Automatic Gathering of Table Stats
Sometimes the Stats of selector tables: PSTREESELECTxx are disturbed due to frequent changes in Trees, hence it is required to gather stats for tree selector tables else nVision could take very long to complete. Rather than manual Stats gathering, we can easily automate by creating Database Triggers using below SQL statement. Check the impacted selector tables and create triggers accordingly. Trigger will ensure whenever Tree is changed, stats are gathered for selector tables.
CREATE TRIGGER SYSADM.TRIGGER_NAME
AFTER DELETE OR INSERT OR UPDATE
Approach 2: Rebuilding Tree Selectors for nVision
The following are 2 methods for rebuilding tree selectors.
- 1. nVision will automatically rebuild selectors if the tree is re-saved after deleting a node and re-adding it or if the tree is copied to a new effective date.
- Delete the selector control entries, for example Account trees, forcing nVision to rebuild them. The following SQL statement should do this, assuming that the ACCOUNT trees have a tree structure called ACCOUNT (if not, change the structure ID):
delete from pstreeselctl where tree_name in (select tree_name from pstreedefn
Approach 3: Deleting orphaned rows from selector tables could improve performance.
Approach 4: Decrease the size of the Summary Ledger
- Reduce the number of ChartFields on the Summary Ledger.
- Reduce the number of values for each ChartField by using summary ChartFields
- Compress the Summary Ledger with summary nodes (use summary trees)
- Summarize data at a level that will satisfy a number of reports
- Using more than 2 or 3 trees causes problems for some database platforms.