Tuesday, October 10, 2006

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

4 comments :

Larry Grey said...

Hi David,

Although you would never catch me saying this when I was at PeopleSoft, I like what you put together to update the tree performance options through SQL (we always discouraged people from updating Tools tables directly).

If you wanted to do something similar for layouts with the performance options embedded in them, the information is stored in defined names in Excel.

The name itself is NvsTree.{treename}. The contents of the name is a string of Y or N flags. (for example: ="YNNYN" ). The meaning of each position is as follows:

Position 1 - Dynamic Selectors (Yes, No)
Position 2 - Suppress Join (Yes, No Subselect Instead)
Position 3 - Range of values using >= and <= (Yes, No... which means BETWEEN if not single values)
Position 4 - Single Values (Yes, No)
Position 5 - Non-Specific Node Criteria (Yes, No)

I'd like to write up a similar post referencing this, and perhaps using the tree APIs instead of SQL for updating the tree tables

Raphael said...

Hello David, very useful post. If I understood well the use of the tree selector tables, does this mean all the following entries are the debris your mentioned?
"select * from PSTREESELECTnn
where SELECTOR_NUM not in (select SELECTOR_NUM from pstreeselctl"

David Kurtz said...

Raphael - essentially yes. If the selector isn't on PSTREESELCTL, then I think you can (and probably should) get rid of it. This debris is left over from nVision reports that crash and do not clean up after themselves. If you are deleting a large proportion of the control table then you may want to consider rebuilding it in order to reset the high water mark of the table.

Anonymous said...

hi, when I tried to run your query in my DEMO, I found that PSXLATITEM table is missing. How come we miss a PS table? Can you please throw some light on this?