Query Performance

Improvement on Query performance implemented in 8.17 and 8.41

Performance issue getting a list of Queries and/or Records on the Web/Windows Client and running Queries on the Web for 8.x.

To improve the performance, Development has added two mechanisms. Therefore starting with PeopleTools 8.17, when bringing up a list of Queries, the system will first check:

- whether a temp Record has been created from the App Engine run job to store the data of the SQL statement
- SQL Object
- pull data using the SQL statement directly from the Database (PTools 8.16)

Here is the description of the 2 mechanisms:
**********************************************************
1. This specific Incident (T-SSHAIK-XQ8LT) discusses a new App Engine program that has been built to de-normalizes some of the Tables that are used to build the Query Access List. If the Customer chooses to use this new feature, it will greatly simplify the SQL that needs to be executed by Query to build Access List — regardless of what process is running the Query, i.e. would work the same for Crystal as it does for online Query. This new feature is called Query Access List Cache. Here is some more information regarding it:
**********************************************************

Excerpt From Query PeopleBook Enhancement for 8.17 -

Enabling Query Access List Cache
A new optional Query Access List Cache batch process is now available for Users who work with Query Manager, Crystal Reports, and PS/nVision. When the Access List Cache is enabled, the time taken by the system to retrieve the Queries that match the search criteria entered is greatly improved.

Maintain Security – Query Access List Cache page
To enable the Access List Cache:
1. Select PeopleTools, Maintain Security, Setup, Query Access List Cache.
2. Select Enable List Cache.
3. Select the Run button to run the process.
If the process does not complete, the Cache will be disabled.

From Query PeopleBook in 8.4x -

Enabling Query Access List Cache
A new optional Query Access List Cache batch process is now available for Users who work with Query Manager, Crystal Reports, and PS/nVision. When the Access List Cache is enabled, the time taken by the system to retrieve the Queries that match the search criteria entered is greatly improved.

Security – Query Access List Cache page
To enable the Access List Cache:
1) Select PeopleTools, Security, Query Security, Query Access List Cache.
2) Select Enable List Cache.
3) Select the Run button to run the process.

If the process does not complete, the cache will be disabled.

Note. Rerun the process when changes have been made to Query Access Groups or Query Access Group settings on Roles or Permission Lists.

If you need to be able to tell if the Cache is working:
1) Clear the Access List Cache.
2) Load Windows Query directly by running psqed.exe and use a stopwatch to time how long it takes from the moment you click the ok button to login until the time Windows Query appears.
3) Enable the Access Cache and run the App Engine program to populate the Cache.
4) Perform Step #2 again and compare the times.

Note that if the time in Step #2 is less than 5 seconds, you probably won’t see much improvement as it’s mostly overhead at that point. If you try this on a full Financial Database, you should see the time drop from about 60 seconds to less than 10 seconds.
**********************************************************

The only qualifier on this is that the DBA should only make changes to SQL that modify the access plan, i.e. would be acceptable to carefully reorder Tables in FROM clause or WHERE conditions, but they would need to be very careful to make sure that the actual SQL logic/result set is not affected.

Added code to read the SQL for Accessible Record List from a SQL Object. If the SQL Object is not found, then the hard-coded SQL in C++ is used. The rules are:
1) Look for the de-normalized table for Accessible Record List.
2) If #1 is successful, get the Records using that Table.
3) If #1 is not found or data is not found in #2, look for SQL Object PS_QRYACCRECLIST.
4) If #3 is found, use that SQL for fetching the Record List.
5) If #3 is not found, use the hard-coded SQL.

Added optimizable SQL Objects PSQRYACCLISTBIGSQL and PSQRYACCLISTFASTSQL. The former is for when the Cache is not used, the latter is the Cache access SQL.

Add Oracle Hints to Query Search Views

Adding Oracle hints to Query Search Views (Security Views)

To add hints to the view SQL, do the following:

Open Application Designer
File/Open select Record then choose the SQL View in question (ex: EMPL_SRCH_GBL)
goto Object Properties and click on Type tab, you will see the SQL View

After the word SELECT you will insert the hint. ex: SELECT /*+ RULE */ A.EMPLID, B.RMPL_REC#

If in Query and want to add a HINT in the query see below:

What you will need to do is create an EXPRESSION. Right click on Expression then select New Expression.

In the box where you can write your expression you will type in the HINT then click on the Add Field button. Add the first field in your field list. You have to do this so that PS/Query doesn’t put a comma after the hint. The text in the Expression box should look something like this: /*+ ALL_ROWS */ A.EMPLID

Syntax = /*+ */

Make sure that the is the first field you want in your query output. When you are finished adding the Expression you should click on the SQL tab to make sure the SQL looks OK. It will look something like this:

Select /*+ ALL_ROWS */ A.EMPLID
From EMPLOYEES A

What Are the Tree Table Names

E-TR: What are the names of the Tree tables?

Names of Tree Tables and relationship parent/child

PeopleSoft Tables – Tree Manager

Table Name Table Description
PSTREEDEFN Stores valid tree definitions. Updated by the Tree Manager. Non English stored in PSTREEDEFNLANG.

PSTREENODE Contains one entry per tree node / deptid. Defines relationships
between nodes. Updated by the Tree Manager.

PSTREEBRANCH Stores tree branches.

PSTREESTRCT Defines the different records and fields that control the tree attributes.
Updated by the Tree Manager.

PSTREELEVEL Validation table for tree levels. Updated by Tree Manager.

PS_ACCESS_GRP_TBL Stores defined Access Groups. Used for Query Security

PSTREESELCTL Control table for PSTREESELECT## tables. Also: PSTREESELNUM.

PSTREESELECT01-30 Stores tree leaf information (one table for every possible length of a chart field 1-30 characters). Work table for optimization purposes.

PSTREELEAF Contains ranges of field values that are associated with a node of a tree.

PSTREEPROMPT (Specific to 8.x and up only) Stores similar information as PSTREEDEFN and is needed for trees to be viewed through PIA

Parent/Child relationship: The range of nodes from PSTREENODE.TREE_NODE_NUM to TREE_NODE_NUM_END
represents all the nodes in the sub-tree headed by this node. So the Tree Manager uses these fields to determine parent-child relationships.

Tree_Node_Num The node number of this node.
Tree_Node_Num_End The node number of the last child of this node. The range of nodes from Tree_Node_Num through Tree_Node_Num_End
represents all the nodes in the sub-tree headed by this node.

nVision Tree Selector Tables – What Are They

NV: Tree selector tables – what are they and why are they used?
 ”NV: Tree selector tables – what are they and why are they used?”

 Analyzing the SQL statements that are generated when nVision reports are run. The SQL statements keep using tables called “PSTREESELECTxx” where xx is some number. What are these tables?

nVision and Query use tree “selector” tables when there is a need to interpret node data from a tree. These tables “flatten” a tree by populating the selector table with all the ranges of values for a given tree node. This table is then joined to the field being rolled-up in order to retrieve the rows that fall under that tree node.

There is a selector table for each chartfield in the GL. The number after the PSTREESELECT represents the length of the chartfield. For instance, PSTREESELECT06 is normally the selector table used for account values because the account chartfield is 6 digits in length. PeopleSoft delivers 30 selector tables.

Selector table data is built when the nVision report or query is run. Selector table data can be “static” or “dynamic” depending upon the performance options which are set for each tree. Static selectors remain in the selector tables until a tree is changed. If nVision or query detect a change in the version number of a tree the selector table data gets rebuilt. Dynamic selector data is build “on the fly.” The selector table data is built at runtime and then deleted from the table.

For more information on selector tables and nVision performance please read the PeopleTools PeopleBook. The information can be found under Reporting and Analysis Tools, nVision, Performance.

More Information: The data in the PSTREESELECTXX tables can be purged. You should do this if the data gets out of synch (especially after an upgrade). Data can be deleted from both PSTREESELECTXX and the control table PSTREESELCTL.

Rebuilding Tree Selector Tables

NV: Rebuilding Tree Selectors for nVision (PSTREESELECT)

Tips and Techniques

Subject: Rebuilding Tree Selectors for nVision

Content: The following are 2 methods in rebuild tree selectors.

Method 1: nVision will automatically rebuild selectors if the tree is re-saved after deleting a node and re-adding it, or if the tree is copied to a new effective date.

Method 2: delete the selector control entries associated with the ACCOUNT trees, forcing nVision to rebuild them. The following SQL statement should do this, assuming that the ACCOUNT trees have a tree structure called ACCOUNT (if not, change the structure ID):

delete from pstreeselctl
where tree_name in
(select tree_name from pstreedefn
where tree_strct_id=’ACCOUNT’);

One additional note: deleting the Selector Controls forces nVision to rebuild them from the other tree tables. It also leaves all the old selector rows orphaned in PSTREESELECT06 (if ACCOUNT is 6 char. long). But it doesn’t affect the node name lookup, which is done either on the system node table (PSTREENODE) or in a cache (swap file) of nodes nVision has already looked up. I have heard of cases where that cache was not rebuilt after a tree change, but that only happens when the nVision user stays logged on while the tree change takes place. Logging off and logging back on should solve this, and is less disruptive than deleting swaps.

————————————————-

More information regarding PSTREESELECT table.

When does PSTREESELECT get updated?

The PSTREESELECT# tables are used to speed tree-based data selection. The table gets rows inserted into it the first time a tree is used by an nvision report.

PSTREESELCTL is the table which controls whether or not to refresh data in the selector tables based on the Tree effective date. If you modify a tree and save it, the pstreeselect tables will get updated when you run an nVision report that uses that tree.

The indexes need to be tuned based on how they use it. PSG goes out and does this a lot. It is covered in Chapter 12 of the Using your GL system.

======================================================

How does PeopleTools repopulate the tree selector tables without a database 

The selector tables should be recreated each time the nVision reports are run. Basically what happens is that nVision looks to see if the selector table values exist for items in the trees. If they are not there, the process recreates the static selector table values. You should never have to copy or create selector table values, the system will do it for you. Unfortunately, if you have changed the trees between the time the selector tables were deleted and the time they are rebuilt, you may have ‘orphaned’ records which may impede performance. This can be identifed and deleted using SQR.

If you are running with Dynamic Selectors on in the Performance Options, the selector values for the trees being used only exist for the duration of the report run. They will not remain after the run completes.

Leopard Screen Capture

Screen Capture Beyond Shift-Command-3 and 4

Leopard Changes

There have been a few changes made to the way Mac OS X captures the screen under Leopard. The same old Shift-Command-3 and 4 commands still work, but there are a few more things you can do now.

You may know that by adding the Control to the key command sends the resulting screenshot to the Clipboard instead of the desktop, but did you know that after you’ve invoked Shift-Command-4, you can use other keys to refine your screen selection.

Once, you’ve hit Shift-Command-4 or Shift-Control-Command-4, you are free to release those keys and click and drag your selection of the screen. You’ll notice that the crosshairs now show the coordinates on the screen in pixels. When you click and start to drag, those numbers change to show the dimensions of the selection, also in pixels.

Once you’ve started to click and drag the selection, you can use the Spacebar to move your selection around on the screen without changing its dimensions (if you hit the Spacebar before you click and drag, the cursor changes to a camera icon and you are able to take screenshots of individual windows by clicking on them).

Holding down the Shift key allows you to constrain the height of the selection allowing you to refine its width or vice versa. It just depends on which dimension you change immediately after you press the Shift key.

Holding down the Option key causes the selection to expand from its center. This is handy if you want a certain detail to be in the center of the screenshot; just hit Shift-Command-4, hold down the Option key and then click and drag from the point that you want to be the center of your screenshot.

One last key command, and it’s not new, if you make a mistake in your selection somehow and don’t want the screenshot to be taken at all, hit the Esc key to back out of it.

Advanced OS X Microsoft RDC Configuration

Advanced Microsoft RDC Configuration

Anatomy of a Bundle
Let’s look at the structure of an OS X application bundle:

Project.app/
Contents/
Info.plist
MacOS/
Project
Resources/
Icon.icns
Project

In the above example, the application is called “Project”. Its top directory is named “Project.app”. “Sample.app” contains a single directory – “Contents”. “Contents” has three items: the “info.plist” file, the directories “MacOS” and “Resources”.

The “Info.plist” file contains configuration information for the application bundle. For detailed info on these property lists, see Apple’s documentation – for example: http://developer.apple.com/documentation/MacOSX/Conceptual/BPRuntimeConfig/Articles/ConfigFiles.html

For our purposes, the only info we need to configure is the icon:

CFBundleIconFile
Icon

This simply declares the name of our icon file to be “Icon.ics” (you leave off the extension). The file must be located in the Resources directory.

We’ll skip the MacOS directory for a minute. In the Resources directory, we put the Icon.ics file. I used “Icon Composer”, which is included with the Xcode Tools, to create this file. For most people, building the icons.icns file was the hardest and most time-consuming part of the process.

For this project, a custom icon is pasted into the Get Info window – this icon should match the one in icon.ics. This is needed because when this instance of RDC app is launched, we want it to have a unique icon that matches the application that is running. If we did not paste a custom icon, every instance would share the standard Microsoft Remote Desktop Connection icon.

Now, to return to the MacOS directory. In an application bundle, this directory contains the actual executable for the bundle and is typically named the same as the bundle (minus the extension). In this case, the executable is named “Project”, and is an executable shell script.

#!/usr/bin/perl -w
#
# Terminal Services launch script for Microsoft Applications
# this version by Greg Neagle, Walt Disney Animation Studios
#
use strict;
use File::Basename;

my $macosdir = dirname $0;
my $contentsdir = dirname $macosdir;

# put the DNS name or IP address of your terminal server here
put $RDCHOST = “ms_term_farm.vanguardcar.com”;

# put the name of your organization here
my $ORGNAME = “Vanguard Car Rental USA”;

# put your Active Directory domain here
my $DOMAIN = “VGCAR”;

# can we ping the RDC host?
my $RDCHOSTavailable = `/sbin/ping -c1 -t1 $RDCHOST 2>/dev/null | /usr/bin/grep “1 packets received”`;
chomp $RDCHOSTavailable;

# If terminal server is unpingable, warn the user
unless ($RDCHOSTavaialble) {
`/usr/bin/osascript<<EOD;
display dialog “The Remote Windows Server seems to be unavailable.” & return & “Make sure you are on the $ORGNAME network.” with icon 1 buttons ["OK"] default button 1
EOD`;
exit;
}

# get the current username: if you OS X user accounts aren’t the same as your AD user accounts,
# you may want to change this to my $USER = “”; so the user has to enter their AD name.
my $USER = $ENV{USER};

# get the program name; this is the name of the executable
my $PROGRAM = $0;
$PROGRAM =~ s/.*\///;

# find our embedded copy of the RDC client, which we’ve renamed to the same name as the executable
my $RDCapp = “$contentsdir/Resources/$PROGRAM”;

# based on the nme of the executable, figure out our Windows application path
# change these to reflect the paths on your terminal server and the applications you support
my $APP= ();
$APP(Word) = ‘C:\Program Files\Microsoft Office\Office11\winword.exe’;
$APP(Excel) = ‘C:\Program Files\Microsoft Office\Office11\excel.exe’;
$APP(InternetExplorer) = ‘C:\Program Files\Internet Explorer\iexplore.exe’;
$APP(PowerPoint) = ‘C:\Program Files\Microsoft Office\Office11\powerpnt.exe’;
$APP(Project) = ‘C:\Program Files\Microsoft Office\Office11\winproj.exe’;
$APP(WindowsDesktop) = ”;

# build an RDC connection document
my $RDC_file_contents = </tmp/RDC_${USER}_${PROGRAM}_temp”;
print RDC, “$RDC_file_contents”;
close RDC;

# launch out embedded copy of the RDC app and give it the RDC connection document
system “open -a \”$RDCapp\” /tmp/RDC_${USER}_${PROGRAM}_temp”;

This script is somewhat universal in that it can be used for multiple applications; it determines which application to launch based on its own name. This script must be executable.

The Finished Product
You now should have a double-clickable application that puts an informational icon in the Dock, and connects to your terminal server and launches the desired session.

How does it work? You double-click the icon. The script at Project.app/Conents/MacOS/Project runs. It creates a Remote Desktop Connection document, then launches the embedded copy of the RDC application and passes it the connection document.

Multiple versions of this application can be created (which of course, is the whole point), each pointing to a different application hosted on your terminal server. Users simply double-click the application they want, just as if it were a Mac application installed locally.