Global nVision performance options

Another back door into PeopleSoft: I've been working on nVision recently. In PeopleTools 8.x, the nVision performance options are stored on the tree definition table, PSTREEDEFN (in previous versions, they were stored in the layout). The performance options should be set as follows

• Use Literal Values: The SQL generated by nVision uses a list of litteral values instead of a joining to one or more tree selector tables. This simplifies the query by reducing the number of tables queried, although the query will be much longer because there will be many criteria on litteral values select from those same tree selector tables. The fewer tables in the from clauses, the fewer way the optimizer will have to execute the statement incorrectly!
• Use Static Selectors: nVision queries data from the tree selector tables rather than build an extract of part of the trees. Sometimes nVision doesn't clear out this temporary data, usually when it errors for some reason. Hence, debris builds up in the tree selector tables over time an this degrades the performance of the queries.
• Use Between on the Selection Criteria: This doesn't improve performance, but merely shortens the length of the SQL statement.

spool nvperfopts
SELECT  T.SETID, T.TREE_NAME, T.EFFDT
,       T.TREE_ACC_SELECTOR --static selctors
,       X1.XLATSHORTNAME
,       T.TREE_ACC_SEL_OPT --between
,       X2.XLATSHORTNAME
,       T.TREE_ACC_METHOD --literals
,       X3.XLATSHORTNAME
FROM    PSTREEDEFN T
,       PSXLATITEM X1
,       PSXLATITEM X2
,       PSXLATITEM X3
WHERE   (T.TREE_ACC_SELECTOR != 'S'
OR       T.TREE_ACC_SEL_OPT != 'B'
OR       T.TREE_ACC_METHOD != 'L')
AND     X1.FIELDNAME = 'TREE_ACC_SELECTOR'
AND     X1.FIELDVALUE = TREE_ACC_SELECTOR
AND     X2.FIELDNAME = 'TREE_ACC_SEL_OPT'
AND     X2.FIELDVALUE = TREE_ACC_SEL_OPT
AND     X3.FIELDNAME = 'TREE_ACC_METHOD'
AND     X3.FIELDVALUE = TREE_ACC_METHOD;

/*increment the version numbers*/
UPDATE PSLOCK
SET    VERSION = VERSION + 1
WHERE  OBJECTTYPENAME IN('SYS','TDM');

UPDATE PSVERSION
SET    VERSION = VERSION + 1
WHERE  OBJECTTYPENAME IN('SYS','TDM');

/*update nvision flags and version number on trees*/
UPDATE PSTREEDEFN
SET    TREE_ACC_SELECTOR = 'S' --static selctors
,      TREE_ACC_SEL_OPT = 'B' --between
,      TREE_ACC_METHOD = 'L' --literals
,      VERSION = (SELECT VERSION
                 FROM PSLOCK WHERE OBJECTTYPENAME = 'TDM')
WHERE  TREE_ACC_SELECTOR != 'S'
OR     TREE_ACC_SEL_OPT != 'B'
OR     TREE_ACC_METHOD != 'L';

SELECT T.SETID, T.TREE_NAME, T.EFFDT
,      T.TREE_ACC_SELECTOR --static selctors
,      X1.XLATSHORTNAME
,      T.TREE_ACC_SEL_OPT --between
,      X2.XLATSHORTNAME
,      T.TREE_ACC_METHOD --literals
,      X3.XLATSHORTNAME
FROM   PSTREEDEFN T
,      PSXLATITEM X1
,      PSXLATITEM X2
,      PSXLATITEM X3
WHERE  (T.TREE_ACC_SELECTOR = 'S'
OR      T.TREE_ACC_SEL_OPT = 'B'
OR      T.TREE_ACC_METHOD = 'L')
AND    X1.FIELDNAME = 'TREE_ACC_SELECTOR'
AND    X1.FIELDVALUE = TREE_ACC_SELECTOR
AND    X2.FIELDNAME = 'TREE_ACC_SEL_OPT'
AND    X2.FIELDVALUE = TREE_ACC_SEL_OPT
AND    X3.FIELDNAME = 'TREE_ACC_METHOD'
AND    X3.FIELDVALUE = TREE_ACC_METHOD;

SETID TREE_NAME          EFFDT               T XLATSHORTN T XLATSHORTN T XLATSHORTN
----- ------------------ ------------------- - ---------- - ---------- - ----------
      QUERY_TREE_WF      00:00:00 01/01/1990 S Static     B Between    L Literal
      QUERY_TREE_PT      00:00:00 01/01/1900 S Static     B Between    L Literal
      QUERY_TREE_OLAP    00:00:00 01/01/1900 S Static     B Between    L Literal
SPN01 SPANISH_ACCOUNT    00:00:00 01/01/1900 S Static     B Between    L Literal
SHR03 ALL_PURCHASE_ITEMS 00:00:00 01/01/1900 S Static     B Between    L Literal
SHR02 ALL_PURCHASE_ITEMS 00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE VENDOR_TYPE1       00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE USER_LIMITS        00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE TR_INSTRUMENTS     00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE TR_CASH_POSITION   00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE STOCK_AVAILABILITY 00:00:00 01/01/1900 S Static     B Between    L Literal
SHARE SALES_PERSON       00:00:00 01/01/1900 S Static     B Between    L Literal

spool off