nVision Performance Tuning: 1. nVision Performance Options

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

At no point when defining a nVision report does the developer directly specify a SQL statement, all the SQL is generated by nVision from the report definition.  That also means that it is not possible to directly intervene and change the SQL statement, for example, to add an optimizer hint.

However, the way that the SQL is generated can be controlled via the nVision performance options. Setting this appropriately can make a significant difference to nVision performance, but the optimal settings will differ from system to system, from tree to tree and sometimes even report to report.

Specification of Performance Options

nVision performance options are specified for named trees.  Since PeopleTools 8, they are defined in the tree definition.

They are stored in the database in PeopleTools table PSTREEDEFN.

Field Name	Field Value	Description
TREE_ACC_METHOD	D	Use Application Defaults
	J	Join to Tree Selector
	L	Use Literal Values
	S	Sub-SELECT Tree Selector
TREE_ACC_SELECTOR	D	Dynamic Selectors
	S	Static Selectors
TREE_ACC_SEL_OPT	B	Range of Values (BETWEEN)
	R	Range of Values (>=…<=)
	S	Single Values

Performance options can also be specified in individual layouts, and the specific definition in the layout overrides the generic definition in the tree.

You can also see the trees defined in a layout file in the Name Manager in Excel.  The performance options are converted into a string of 5 characters.  Below you can see a tree called OPERATING_UNIT. The easiest way to determine whether performance options are set in a layout is to look in the Name Manager, and the only reliable way to delete a performance option from a layout is to delete them from the Name Manager.

The string of flags in the NvsTree formula can be decoded with this table.

X	Value
Position	Y	N	S
1	Dynamic Selector	Static Selector
2	Join to tree selector	Suppress join; use literal values if feasible	Sub-SELECT tree selector
3	Range of values (BETWEEN)	Range of values (>= … <=)
4	Single Value (overrides position 3)
5	Non-specific node criteria (above 2 billion)

Recommendations

It is rare to need to set different performance options for the same tree in different layouts. Therefore, it is easiest to set performance options at tree level and not at all in layouts unless absolutely necessary.
Tree performance options can even be updated in bulk by SQL script, though it is essential to maintain the versions numbers correctly.  Care should be taken when migrating trees from other environments to ensure that the performance options are still correctly set, as the performance options are part of the tree being migrated
It is now possible to give clear advice to developers and especially business users who create nVision reports:

• Don't use them.
• If you have specified them in layouts in the past, then remove them unless you really do need to use a different option in a particular report.

Some customers have written Visual Basic macros to work through all their layouts and remove all tree performance options settings.

In the next post I will start to look at how the performance options control how the SQL generated by nVision.