DELETE SQL performance

Delete Performance and ROWID 

We discovered slow performance on a DELETE statement that was using an EXISTS joining a related table and temporary table.  The problem is the entire subselect is executed returning a lot of  data to be evaluated only to delete a few rows from the source table.

To speed this up I added the original source table to the join and the driving table that gave me the smallest set of data.  In this case it was to join SAD_EXT_CRS_COM to EXT_COURSE followed by joining to the temporary table.  Now the the optimizer also help the subselect run extremely fast.  At this point we just need to convert the EXISTS to an IN statement and use the oracle pseudocolumn ROWID which is the fastest way to access a single row. The delete query now executes in no time.

Slow version using WHERE EXISTS

 DELETE FROM %Table(SAD_EXT_CRS_COM) c 
WHERE EXISTS ( SELECT NULL FROM %Table(EXT_COURSE) p 
                     , %Table(M_ET_PST_TMP) t 
               WHERE t.process_instance = %Bind(process_instance) 
                 AND t.oprid = %OperatorId 
                 AND p.emplid = t.emplid 
                 AND p.ext_org_id = t.ext_org_id 
                 AND p.ls_data_source = 'OET' 
                 AND p.ext_data_nbr = t.ext_data_nbr 
                 AND c.emplid = p.emplid 
                 AND c.ext_org_id = p.ext_org_id 
                 AND c.EXT_COURSE_NBR = p.EXT_COURSE_NBR ) 

Faster version using ROWID IN

DELETE FROM %Table(SAD_EXT_CRS_COM) c 
WHERE ROWID IN ( SELECT C.ROWID FROM %Table(SAD_EXT_CRS_COM) C 
                   JOIN %Table(EXT_COURSE) P ON C.EMPLID = P.EMPLID 
                    AND C.EXT_ORG_ID = P.EXT_ORG_ID 
                    AND C.EXT_COURSE_NBR = P.EXT_COURSE_NBR 
                   JOIN %Table(M_ET_PST_TMP) T ON P.EMPLID = T.EMPLID 
                    AND P.EXT_ORG_ID = T.EXT_ORG_ID 
                    AND P.EXT_DATA_NBR = T.EXT_DATA_NBR 
                   WHERE P.LS_DATA_SOURCE = 'OET' 
                     AND T.PROCESS_INSTANCE = %Bind(process_instance) 
                     AND T.OPRID = %OperatorId ) 

Peoplesoft Menu Listing or Search

SQL for Menu Searching

Here is a very handy way to either export a full listing of the PeopleSoft Menu or search for a specific menu item.

----  Menu Look-up
SELECT SRCH.PORTAL_LABEL LABEL
   , NVL(SRCH.PORTAL_URI_SEG2, ' ') COMPNOENT
   , SRCH.PORTAL_OBJNAME
  --L1.PORTAL_LABEL,  L2.PORTAL_LABEL,  L3.PORTAL_LABEL,  
  --L4.PORTAL_LABEL,  L5.PORTAL_LABEL,  L6.PORTAL_LABEL
  ,'Main Menu > ' || L1.PORTAL_LABEL || decode(L1.PORTAL_REFTYPE, 'F', ' > ')
  || L2.PORTAL_LABEL  || DECODE(L2.PORTAL_REFTYPE, 'F', ' > ')
  || L3.PORTAL_LABEL  || DECODE(L3.PORTAL_REFTYPE, 'F', ' > ')
  || L4.PORTAL_LABEL  || DECODE(L4.PORTAL_REFTYPE, 'F', ' > ')
  || L5.PORTAL_LABEL  || DECODE(L5.PORTAL_REFTYPE, 'F', ' > ')
  || L6.PORTAL_LABEL NAV_PATH,
  decode(SRCH.PORTAL_REFTYPE, 'F', ' ', 'C', SRCH.DESCR254) DESCR
FROM PSPRSMDEFN L1
  LEFT JOIN PSPRSMDEFN L2
  ON L2.PORTAL_NAME         =L1.PORTAL_NAME
  AND L2.PORTAL_PRNTOBJNAME = L1.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L3
  ON L3.PORTAL_NAME         =L2.PORTAL_NAME
  AND L3.PORTAL_PRNTOBJNAME = L2.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L4
  ON L4.PORTAL_NAME         =L3.PORTAL_NAME
  AND L4.PORTAL_PRNTOBJNAME = L3.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L5
  ON L5.PORTAL_NAME         =L4.PORTAL_NAME
  AND L5.PORTAL_PRNTOBJNAME = L4.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L6
  ON L6.PORTAL_NAME         =L5.PORTAL_NAME
  AND L6.PORTAL_PRNTOBJNAME = L5.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN SRCH
  ON SRCH.PORTAL_NAME       = 'EMPLOYEE'
  AND SRCH.PORTAL_OBJNAME   
    = NVL(L6.PORTAL_OBJNAME,NVL(L5.PORTAL_OBJNAME,NVL(L4.PORTAL_OBJNAME, 
           NVL(L3.PORTAL_OBJNAME,(NVL(L2.PORTAL_OBJNAME,NVL(L1.PORTAL_OBJNAME,' ')))))))
WHERE L1.PORTAL_NAME ='EMPLOYEE' AND L1.PORTAL_PRNTOBJNAME = 'PORTAL_ROOT_OBJECT'
--*************************************************************************************--
--  Search for Items
--*************************************************************************************--
  --AND SRCH.PORTAL_URI_SEG2    = 'MCM_ADV_SUMMARY'     -- Component
  --AND SRCH.PORTAL_LABEL       LIKE 'PeopleTools%'     -- Label
  --AND L1.PORTAL_OBJNAME       = 'PT_PEOPLETOOLS'      -- Folder/Ref Object Name
ORDER BY L1.PORTAL_REFTYPE DESC,  L1.PORTAL_LABEL,
  L2.PORTAL_REFTYPE DESC,  L2.PORTAL_LABEL,  L3.PORTAL_REFTYPE DESC,  L3.PORTAL_LABEL,
  L4.PORTAL_REFTYPE DESC,  L4.PORTAL_LABEL,  L5.PORTAL_REFTYPE DESC,  L5.PORTAL_LABEL,
  L6.PORTAL_REFTYPE DESC,  L6.PORTAL_LABEL ;

Component Verification SQL

Component Verification

This query was built as part of a script to review projects.  The objective was a quick way to confirm all the components in your project are following the company standards. Peoplesoft will often use a Binary bit mapping to turn several flags into a single decimal value.  In this case the column SHOWTBAR is a numeric SUM of 6 binary values 111111 = 1+2+4+8+16+32 = 63.  If you experiment with these you will notice some odd behavior like the Disable Toolbar when checked doesn't add 1 to the decimal but Disable Pagebar when checked adds the value 2.

Columns

1. What is the default Search setting for the component
2. What flags are set for "Multi Page Navigation"
3. Disable Toolbar Flag
4. Disable Pagebar Flag
5. Disable Help URL
6. Disable Copy URL
7. Disable New Window
8. Disable Customize Page

Binary Mapping for SHOWTBAR

• +1 Disable Toolbar is Unchecked
• +2 Disable Pagebar is Checked
• +4 Help Link is Uncheked
• +8 Copy URL Link is Unchecked
• +16 New Window Link is Unchecked
• +32 Customize Page Link is Unchecked

select PNLGRPNAME as Component,
CASE DFLTSRCHTYPE WHEN 0 THEN 'BASIC SEARCH *ERROR*' 
                  WHEN 1 THEN 'ADVANCED SEARCH' 
                  ELSE to_char(DFLTSRCHTYPE) END as SEARCH_TYPE,
CASE PNLNAVFLAGS  WHEN 0 THEN 'MULTI-PAGE NAV OFF' 
                  WHEN 1 THEN 'FOLDERS (TOP)' 
                  WHEN 2 THEN 'HYPERLINKS (BOTTOM)' 
                  WHEN 3 THEN 'FOLDER + LINKS NAV ON' 
                  ELSE TO_CHAR(PNLNAVFLAGS) END AS NAVIGATION_TYPE
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,1),1,'N','Y') AS DISABLE_TOOLBAR
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,2),2,'Y','N') AS DISABLE_PAGEBAR
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,4),4,'N','Y') AS SHOW_HELP_URL
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,8),8,'N','Y') AS SHOW_COPY_URL
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,16),16,'N','Y') AS SHOW_NEW_WIN
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,32),32,'N','Y') AS SHOW_CUSTOMIZE_PAGE
FROM PSPNLGRPDEFN WHERE PNLGRPNAME IN (SELECT PI.OBJECTVALUE1 FROM PSPROJECTITEM PI WHERE PI.PROJECTNAME = 'MY_PROD_NAME' AND PI.OBJECTTYPE=7);

SQLExec for Number Column

The Snag

Today I was hitting a problem using the SQLExec function with a query against a single number column.  I needed to retrieve a specific value for an employee and if no rows are found my program would skip this employee.  The logic seemed simple enough something like the following:

SQLExec(SQL.MCM_SNLF_BN_RCD_NBR, &employee, &EmplRcd);
If None(&benefitEmplRcd) Then
   MessageBox(0, "", 20002, 4, " No Employee Record skip employee");
   &record = Null;
   Return &record;
End-If;

BAD! 

When you run this code the query is against a Number column and that number column even if no record is found will return a 0 not a null and your IF condition will never be true.  Also if zero is a valid value for Employee record then code after using that value could execute against all the wrong data.

Solution

My solution to this is pretty easy simply include another key value of your query in my case Employee ID which will never be empty and also a String value.  Use that column as your check for existence instead of the number column.

Local  &emplidExists;
SQLExec(SQL.MCM_SNLF_BN_RCD_NBR, &employee, &emplidExists, &EmplRcd);
If None(&emplidExists) Then
   MessageBox(0, "", 20002, 4, " No Employee Record skip employee");
   &record = Null;
   Return &record;
End-If;

PeopleCode information Queries

Queries for PeopleSoft Objects

Page information and objects

-- Information and Properties on a page
select * from pspnlfield 
where pnlname = 'EXTRA_ACTIVITY_TBL';

All records where a Field is used

-- Find all the Records a field is used
SELECT DISTINCT recname, fieldname FROM psrecfield 
WHERE  fieldname = 'ELEVENTH_GRADE';

All the pages that a field exists on

-- Find all the pages where a field is used
SELECT pnlname FROM pspnlfield 
WHERE recname='EXTRACUR_ACTVTY' AND fieldname = 'DESCR';

All the pages that a record is used on

-- Find pages a record is used on
SELECT DISTINCT pnlname FROM pspnlfield 
WHERE recname='EXTRACUR_ACTVTY';

Check for outstanding Run Controls

Simply change this to a delete if you have a process that failed and you want to restart from the beginning.

SELECT * FROM ps_aeruncontrol 
WHERE oprid = 'YOURUSERID';

Long readable SQL in peoplecode

Readable SQL statements

Problem 

First I'm going to admit when it comes to my code I'm a minimalist.  I like short clean code and I often use any shortcuts available in a language.  However, I will not sacrifice readability only to have the smallest line count.  Some languages you could get away with writing an entire function in 1 return statement but is a nightmare to read or modify.  For a while now I've been in pursuit of how to deal with long SQL statements in PeopleCode.  There are a few problems with this language that make writing long friendly SQL statements.  There is no way to continue a string on multiple lines without string concatenations.  There is not a shorthand append string that some languages have such as += either.

&sqlQry = " select * from PS_JOB A";
&sqlQry = &sqlQry | " where emplid = :1 and empl_rcd = :2 ";
&sqlQry = &sqlQry | " and effdt = (select max(effdt) from PS_JOB B";
&sqlQry = &sqlQry | " where A.emplid = B.em... ";

You can see where this is going. It's not to bad approach, however your indentation is off and your long query names waste space on that could be used for the query itself and actually reduce the lines you need. I finally settled with using &qry but I still wasn't satisfied with the doing the concatenation method on every line. What if I needed multiple queries and wanted a more descriptive name.

My solution

Here is my approach for now until they come up with += shortcut for string concatenation or a way to build a string on multiple lines.  I define a very simple several line short named variable scheme (&sq1, &sq2...) for each query line.  Then string the set together into the longer descriptive name and reuse them on my next query if needed.

Local string &sq1, &sq2, &sq3, &sq4, &sq5;
Local string &queryJobs, &queryEmpl;

/* query the jobs table */ 
&sq1 = " select * from PS_JOB A";
&sq2 = " where emplid = :1 and empl_rcd = :2 ";
&sq3 = " and effdt = (select max(effdt) from PS_JOB B ";
&sq4 = "            where A.emplid = B.em.....  ";
&queryJobs = &sq1 | &sq2 | &sq3 | &sq4;

/* query the Employee table */
&sq1 = " select * from PS_employees A";
&sq2 = " where emplid = :1 and empl_rcd = :2 ";
&queryEmpl = &sq1 | &sq2;

Just remember to put a space in at the beginning and/or ending of every line.

Using SQL Object

An alternative way is to use an SQL object. Just create a new SQL object from the menu File/New/SQL. Enter you valid SQL statement with the parameters you wish again using the same ":1" placeholders. Then call that SQL in your peoplecode with SQLEXEC and pass your parameters values in as shown below.

Local Record &rec = CreateRecord(Record.JOB);
SQLExec(SQL.MYSQL_JOB, &emplid, &rcd, &rec);

SQL for peoplesoft date values

Current Date

I needed a way to reset a table where the effective date was today. I had a query that worked with TO_DATE('24-02-19','yy-MM-dd') except every day I'd have to update the query to match the current date. Sometimes I'd forget and delete some values I may have wanted. In oracle if you use any current date function like sysdate, current_date etc the current time is actually included. To day where effective date is equal to the current_date would never match unless you ran the query at exactly midnight to the millisecond of the day you wanted removed.

Proof

select to_char(CURRENT_DATE, 'dd-mon-yyyy hh24:mi:ss') from dual;

To get around this I used the TRUNC function to zero everything after the the Date.

Example

select to_char(trunc(CURRENT_DATE,'DDD'), 'dd-mon-yyyy hh24:mi:ss')
from dual;
-- The delete statement would like like this.
delete from PS_MCM_SNLF_H_REC 
where EFFDT = trunc(CURRENT_DATE,'DDD');