Optimizing PS/nVision Performance

This section discusses:

PS/nVision is a complex tool, capable of producing a great variety of reports from a variety of database tables. The SQL statements it generates are not necessarily complex, but they are very sensitive to the performance of the underlying database, especially in the following areas:

  • Large tables (ledgers often have millions of rows) make efficient use of indexes essential.

  • The use of trees and reporting (security) views cause multiple tables to be joined.

    The efficiency with which the database processes these JOIN statements dictates most of the performance of PS/nVision.

Unlike traditional background reporting tools, PS/nVision supports interactive, focused reporting with a probing or querying approach to accessing the database. PS/nVision queries tend to be more numerous than traditional report writers are, but also more focused on the specific data that you want to see.

PS/nVision relates tree node criteria to data tables by joining the data table to a tree selector table. This selector table contains a row for every detail range defined for the tree in the Tree Manager, and is keyed by PROCESS_INSTANCE (a system-generated constant number for all the rows representing a particular tree) and tree node number. Because some database platforms only join tables efficiently if the field sizes match, the system uses up to 30 selector tables, one for each supported ChartField length. Each selector table has RANGE_FROM_nn and RANGE_TO_nn columns matching the corresponding ChartField size.

The following code is a typical SELECT for selection via nodes on a single tree:

SELECT L.TREE_NODE_NUM, SUM(POSTED_TOTAL_AMT)
FROM PS_LEDGER A, PSTREESELECT06 L
WHERE A.LEDGER='ACTUALS'
 AND A.FISCAL_YEAR=1991
 AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 9AND A.ACCOUNT>=L.RANGE_FROM_06
 AND A.ACCOUNT<=L.RANGE_TO_06
 AND L.PROCESS_INSTANCE=198
 AND (L.TREE_NODE_NUM BETWEEN 16 AND 30
 OR L.TREE_NODE_NUM BETWEEN 35 AND 40)
GROUP BY TREE_NODE_NUM

The parts of this statement in boldface accomplish the tree criteria selection. The GROUP BY clause returns an answer row for each node that has a detail range attached to it; these node numbers are used to post amounts from the answer set into the appropriate rows of the report.

PS/nVision endeavors to retrieve the data for each report instance with as few SELECTs as possible. It examines all row criteria to determine which can be combined, and does the same for column criteria. It then builds a Select statement to retrieve each intersection of a combined group of rows with a combined group of columns. You should understand the following built-in rules when designing indexes:

  • Different ledgers cannot be combined.

  • Different TimeSpans cannot be combined.

  • nPloded rows or columns cannot be combined with non-nPloded rows or columns.

  • To be combined, two or more rows or columns must have criteria for the same set of ChartFields, and each ChartField's criteria must be of the same type (selected tree nodes cannot be combined with selected detail values).

  • If criteria for a ChartField are specified by tree node, they can only be combined if they use the same tree.

  • If the combined rows or columns have identical criteria for a particular ChartField, the criteria are included in the "where" clause but no "group by" on that field is required.

    If different rows/columns in the group have different criteria, PS/nVision adds this field (or the corresponding tree node number) to the "group by" clause to retrieve a value for use in posting the answer set to the report.

  • A single Select statement can retrieve amounts for multiple combined rows and columns.

  • Different scope instances are retrieved with separate Select statements.

To examine the SQL produced by PS/nVision, capture the statements in one of two ways:

  • Use the Options Trace option on the Excel menu.

    This causes PS/nVision to display each Select statement used for retrieving labels or amounts in a dialog. Select the text with the mouse, copy it to the clipboard, and paste the text into another application such as Notepad or a text editor. Then save the text to a file or work with it within the application.

    Note: If you want to capture the SQL but do not want to wait for it to execute, select the Excel Options Simulated Run option. PS/nVision generates all the SQL, but will not execute SELECTs for amounts.

  • Select the PeopleTools SQL trace through the Utilities menu.

    This causes all SQL statements executed by PeopleTools to be written to a file called ~DBG0001.TMP in the Windows TEMP directory (often C:\TEMP). This trace shows timings, but does not include SQL that was not executed due to the Simulated Run option.