January 1999 Newsletter

Volume 9, No. 1

Performance Tuning PeopleSoft's nVision With Informix

by Kevin Fennimore

PeopleSoft's nVision is a reporting tool that is an extension of Microsoft Excel primarily used for General Ledger Financial reporting. Users create report templates which accesses ledger and summary ledger data and use selector trees for summarization purposes. This data is selected, summarized and put into the cells of an Excel spreadsheet. The selector trees are represented in the pstreeselectxx tables, where the xx represents the length of the chartfield being stored in the tree. For example, pstreeselect06 would contain trees associated with chartfields of length six, most commonly this would be account.

A common complaint of many PeopleSoft customers is the performance of nVision reports. For some customers, just a few nVision reports running can cause their system to slow down considerably. We have had a lot of experience with the queries that nVision generates and the performance problems they create. These performance problems are not specific to Informix but it was problems with the Informix optimizer which prompted PeopleSoft to make some changes. To that end, PeopleSoft has made some wonderful performance enhancements to nVision which can yield substantial improvements in run times and significant reductions in system load.

This article will discuss those changes, their impact on the SQL generated and how to take advantage of the changes in an Informix environment. The nVision performance options are available in PeopleTools versions 5.12.20+, 6.1+ and 7.x. Also, as part of the release notes for version 5.12.19 there is write-up of these changes which is very useful. These changes were first available in 5.12.19 but are better in 5.12.20.

The nVision reports do not typically perform a large number of selects against the database but the selects that are performed are complex, to say the least, and perform poorly. The frustration with the reports is that the SQL is generated and cannot be changed by the user. The performance options, however, allow the user to have some control over how the SQL is generated. An example of one of these selects which is summarizing account and department id information is shown in Figure 1.

Standard nVision Query

SELECT A.ACCOUNT, L1.TREE_NODE_NUM, SUM(A.POSTED_TOTAL_AMT)

FROM PS_LEDGER A, PSTREESELECT06 L, PSTREESELECT05 L1

WHERE A.LEDGER='ACTUALS'

AND A.FISCAL_YEAR = 1997

AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 6

AND A.BUSINESS_UNIT = 'XYZCO'

AND L.SELECTOR_NUM = 100

AND ( L.TREE_NODE_NUM BETWEEN 1000001 AND 1000100

OR L.TREE_NODE_NUM BETWEEN 1000300 AND 1000400

OR L.TREE_NODE_NUM BETWEEN 1000500 AND 1000600

...

)

AND A.ACCOUNT >= L.RANGE_FROM_06

AND A.ACCOUNT <= L.RANGE_TO_06

AND L1.SELECTOR_NUM = 101

AND ( L1.TREE_NODE_NUM BETWEEN 5000000 AND 5000100

OR L1.TREE_NODE_NUM BETWEEN 5000300 AND 5000400

OR L1.TREE_NODE_NUM BETWEEN 5000500 AND 5000600

...

)

AND A.DEPTID >= L1.RANGE_FROM_05

AND A.DEPTID <= L1.RANGE_TO_05

AND A.CURRENCY_CD = 'USD'

AND A.STAISTICS_CODE = ' '

GROUP BY A.ACCOUNT, L1.TREE_NODE_NUM

Note: The "..." indicates that these OR conditions can go on for several lines.

Figure 1

At one client's site this query took an hour to run. The problem with this query is that the Informix optimizer does not handle it well and generally chooses the wrong query path. The optimizer does not like the between's on the tree_node_num and it does not like the >= and <= on the account and deptid fields. This causes the path for the query to change very easily. We have found that the best path for this query is to read from the pstreeselect06, then the ledger and finally the pstreeselect05 table. However, with the filter conditions on the ps_ledger table - on ledger, fiscal_year, business_unit and accounting_period - the optimizer chooses to read from the ps_ledger table first. We've tried many things in order to get the optimizer to choose the optimal path with limited success. Even when the path was correct, the query did not perform efficiently due to the indexes needed to force the path. This leads us to the enhancements made by PeopleSoft.

The enhancements are designed to improve the queries being constructed so that the optimizer can make a better choice for the query path. Another advantage of the changes is that they actually help the engine perform the queries more efficiently. These performance changes must be set for each individual report layout and each tree used within that layout. There are no global settings that can be made at this time. There was talk at the 1998 PeopleSoft Users' Conference about storing the performance options in the database, versus the layout file, and allowing global settings for a tree. The performance options are accessed by opening a report layout and selecting the menu item nVision->Performance which brings up the dialog box shown in Figure 2.

Figure 2 - nVision Performance Dialog Box

The first step is to select the tree name that is being used in the report. These options are not only specific to the report but also to the individual trees being used in the report. It is possible to set the performance parameters for one tree in a report and not the other trees. Once the tree is selected we can now look at the other options on the screen.

Suppress Join

This check box will try to substitute all of the values for a tree where possible and avoid joining to the tree table. For example, if a tree contained 10 accounts checking this box would produce a query which would not join to the pstreeselect06 table but use an IN clause for the accounts as follows:

account in ( 1,2,3,4,5,6,7,8,9,10 )

versus joining to pstreeselect06 with the typical range:

account >= range_from_06

account <= range_to_06

This is very beneficial for trees with only one or a few values.

Tree Selectors

Choosing Static Selectors causes the queries to be built the original way (i.e., before the performance options). The query will use the selector numbers stored in the tables. The Dynamic Selectors option causes nVision to copy the selector number to a new selector number and use that new selector number for the current run of the report.

For example, if selector number 100 had the records shown in Figure 3, they might be copied to selector number 200. The key benefit here is that the conditions on tree_node_num are applied as the selector number is copied thus eliminating the optimizer unfriendly OR conditions from the query in Figure 1. Let's assume the OR conditions would eliminate the tree_node_num of 30, our new selector number would have the records shown in Figure 4.

Static Selector Records

selector_num tree_node_num range_from_xx range_to_xx

100 10 1 4

100 20 10 30

100 30 100 150

Figure 3

Copied Selector Records

selector_num tree_node_num range_from_xx range_to_xx

200 10 1 4

200 20 10 30

Figure 4

Assuming these options were chosen on both trees in our layout, account and deptid, the generated query would look like:

SELECT A.ACCOUNT, L1.TREE_NODE_NUM, SUM(A.POSTED_TOTAL_AMT)

FROM PS_LEDGER A, PSTREESELECT06 L, PSTREESELECT05 L1

WHERE A.LEDGER='ACTUALS'

AND A.FISCAL_YEAR = 1997

AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 6

AND A.BUSINESS_UNIT = 'XYZCO'

AND L.SELECTOR_NUM = 200

-- Note the missing OR conditions on tree_node_num

AND A.ACCOUNT >= L.RANGE_FROM_06

AND A.ACCOUNT <= L.RANGE_TO_06

AND L1.SELECTOR_NUM = 201

-- Note the missing OR conditions on tree_node_num

AND A.DEPTID >= L1.RANGE_FROM_05

AND A.DEPTID <= L1.RANGE_TO_05

AND A.CURRENCY_CD = 'USD'

AND A.STAISTICS_CODE = ' '

GROUP BY A.ACCOUNT, L1.TREE_NODE_NUM

This not only looks nicer but the optimizer does not have the OR conditions on tree_node_num and is more likely to choose a good path. However, we still have those annoying >= and <= clauses which can cause problems. This is where the next section of the dialog box is useful.

Selector Options

The Ranges of values(>=...<=) option causes the queries to be built the way they were originally. The Ranges of values(BETWEEN) causes the queries to be generated with a BETWEEN clause as follows:

AND A.DEPTID BETWEEN L1.RANGE_FROM_05 AND L1.RANGE_TO_05

These two options do not help us eliminate the ranges and therefore are of little use. The Single Values option, however is of use. This option is only available with the Dynamic Selectors option noted above and causes nVision to "expand" the range values when the selector number is copied. From the data above, the new selector number would have the records shown in , assuming that the valid accounts are 1, 2, 3, 4, 10, 20 and 30.

Copied Selector Records with "Single Values" Option

selector_num tree_node_num range_from_xx range_to_xx

200 10 1

200 10 2

200 10 3

200 10 4

200 20 10

200 20 20

200 20 30

Figure 5

Now instead of two records with ranges of 1 to 4 and 10 to 30 there are now 7 records with no ranges just single values. With this option set for the account tree, pstreeselect06, the query is changed as follows:

SELECT A.ACCOUNT, L1.TREE_NODE_NUM, SUM(A.POSTED_TOTAL_AMT)

FROM PS_LEDGER A, PSTREESELECT06 L, PSTREESELECT05 L1

WHERE A.LEDGER='ACTUALS'

AND A.FISCAL_YEAR = 1997

AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 6

AND A.BUSINESS_UNIT = 'XYZCO'

AND L.SELECTOR_NUM = 200

-- Note the equi-join on the account field vs. <= and >=

AND A.ACCOUNT = L.RANGE_FROM_06

AND L1.SELECTOR_NUM = 201

AND A.DEPTID >= L1.RANGE_FROM_05

AND A.DEPTID <= L1.RANGE_TO_05

AND A.CURRENCY_CD = 'USD'

AND A.STAISTICS_CODE = ' '

GROUP BY A.ACCOUNT, L1.TREE_NODE_NUM

This query is much simpler and the optimizer has a much easier time choosing the right path. In this case the optimal path is: pstreelect06, ps_ledger and then pstreeselect05. The OR conditions on tree_node_num in the pstreeselect06 table have been eliminated and the ranges on the account field have been replaced with an equi-join. With the right index on the ps_ledger table:

PS_LEDGER Index
ledger
account
fiscal_year
business_unit
accounting_period
currency_cd
statistics_code
deptid
posted_total_amt

the query performs the following path:

1) key-only index scan into pstreeselect06 with a lower index filter on:

selector_num = 200

2) key-only index scan into ps_ledger with a lower index filter on:

ledger = 'ACTUALS'

account = pstreeselect06.range_from

fiscal_year = 1997

business_unit = 'XYZCO'

accounting_period >= 1

and an upper index filter on accounting_period <= 6

3) index scan into pstreeselect05 with an upper index filter on:

range_from_05 <= ps_ledger.deptid

The run time for this query was about 25 minutes. Note that the last table read from, pstreeselect05, is performing a non-key-only index scan on a range (range_from_05 <= ps_ledger.deptid) but there is no lower index filter. This is not efficient because there could be many deptid's which will be scanned which are less than the current deptid but not in the range for which we are looking. To fix this, we can set the "Single Values" option for the deptid tree, pstreeselect05. Doing this produces the following query:

SELECT A.ACCOUNT, L1.TREE_NODE_NUM, SUM(A.POSTED_TOTAL_AMT)

FROM PS_LEDGER A, PSTREESELECT06 L, PSTREESELECT05 L1

WHERE A.LEDGER='ACTUALS'

AND A.FISCAL_YEAR = 1997

AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 6

AND A.BUSINESS_UNIT = 'XYZCO'

AND L.SELECTOR_NUM = 200

-- Note the equi-join on the account field vs. <= and >=

AND A.ACCOUNT = L.RANGE_FROM_06

AND L1.SELECTOR_NUM = 201

-- Note the equi-join on the account field vs. <= and >=

AND A.DEPTID = L1.RANGE_FROM_05

AND A.CURRENCY_CD = 'USD'

AND A.STAISTICS_CODE = ' '

GROUP BY A.ACCOUNT, L1.TREE_NODE_NUM

Using the following index on the pstreeselect05 table:

PSTREESELECT05 Index
selector_num
range_from_05
tree_node_num - added for key-only scan

the path for the query is as follows:

1) key-only index scan into pstreeselect06 with a lower index filter on:

selector_num = 200

2) key-only index scan into ps_ledger with a lower index filter on:

ledger = 'ACTUALS'

account = pstreeselect06.range_from

fiscal_year = 1997

business_unit = 'XYZCO'

accounting_period >= 1

and an upper index filter on accounting_period <= 6

3) key-only index scan into pstreeselect05 with a lower index filter on:

selector_num = 201

range_from_05 = ps_ledger.deptid

This is very efficient due to the lower index filter on the pstreeselect05 table being an equi-join versus an upper index filter with no lower index filter. This query ran in 10 seconds versus the original one hour.

These changes were made at one client's site and the longest run time of the tested reports was three and a half minutes. Prior to these changes the typical run time was two to four hours. As I mentioned earlier these changes are significant. It appears that the best performance can be obtained by using the Dynamic Selectors and Single Values options for all of the trees within a given report. This is what worked at this one client. Based on the reports and trees used by individual clients these options should be changed to achieve better performance. The objective is to run the report, examine the queries being produced, change some of the settings and re-examine the queries to see if the new settings produce more efficient queries.

Kevin Fennimore

1-888-UCI-FOR-U