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.