How to add performance hint to CreateRowset peoplecode?

user3483096

Hi, we need to add a hint to a delivered CreateRowset Peoplecode to improve the page performance.

Is there a way to code this?

Thanks,

Aileen

Gans-Oracle

Hello @user3483096,

Please confirm the PeopleTools version and explain more details about the performance hint. Is it an Oracle SQL hint?

Thanks

user3483096

Hi!

Yes! PeopleTools version is 8.57.19 and yes, I mean a SQL hint.

There is a Createrowset and when it runs, it seems the SQL is running long. The Fill command has an exist subselect on another table which causes performance issue. The DBAs has recommended a SQL hint to improve performance, but since it is a CreateRowset, there seems to be no parameter in the function to add a SQL hint.

Thanks,

Aileen

Gans-Oracle

Aillen,

Please refer to this Peoplebooks link that helps to understand the usage %SqlHint metasql with Peoplecode

https://docs.oracle.com/cd/F52213_01/pt859pbr3/eng/pt/tpcl/Meta-SQLReference-073bd4.html

%SqlHint(SQL_cmd, index, hint_text, DB_platform [,

{ENABLE | DISABLE}])

Description

Use the %SqlHint function to insert a database platform-specific SQL hint into the specified SQL statement. The hint is inserted immediately after the SQL command specified by the SQL_cmd parameter.

This meta-SQL function is ignored in any of the following circumstances:

The current database connection does not match the DB_platform parameter.

The DB_platform parameter is not specified as ORACLE. (This is a limitation of the current release.)

The nth occurrence of the SQL command specified by SQL_cmd and index does not exist in the current SQL statement.

Parameters

Parameter

Description

SQL_cmd

Specifies the SQL command that will use the hint as one of the following literal constants:

SELECT

INSERT

UPDATE

DELETE

MERGE

index

Specifies which occurrence of the SQL command will use the hint as an Integer value from 1 to 99.

hint_text

Specifies the SQL hint as a String value enclosed in single quotes. The hint can include other meta-SQL, such as %Table.

DB_platform

Specifies the database platform for which the hint is valid as one of the following literal constants:

ORACLE

DB2

DB2UNIX

SQLSERVER

Note: Currently, ORACLE is the only supported platform. This meta-SQL function is ignored for all other platforms.

ENABLE | DISABLE

Specifies whether to enable or disable the hint as a literal constant.

Note: ENABLE is the default value for this optional parameter.

Example 1

Before: The following example includes an Oracle-specific SQL hint to be inserted after the first SELECT of the SQL statement:

%SqlHint(SELECT, 1, '/*+ FIRST_ROWS(10) */', ORACLE) 

 SELECT EMPLID 

 FROM PS_JOB

After: For an Oracle connection, this meta-SQL would expand to:

 SELECT '/*+ FIRST_ROWS(10) */' EMPLID 

 FROM PS_JOB

After: On all other connections, this meta-SQL would expand to:

 SELECT EMPLID 

 FROM PS_JOB

Example 2

Before: In the following example, %SqlHint functions will be expanded and applied after all other meta-SQL expansion has occurred. In this example, the APPEND hint will be applied to the first INSERT found in this SQL statement. The LEADING hint will be applied to the first SELECT found in this SQL statement.

%SqlHint(INSERT, 1, '/*+ APPEND*/', ORACLE, ENABLE ),%SqlHint(SELECT, 1, '/*+ LEADING(H) INDEX(L, PSFJRNL_LN) */', ORACLE, ENABLE) %InsertSelect(JRNL_LIU_TAO, JRNL_LN L, BUSINESS_UNIT_IU=H.BUSINESS_UNIT_IU, LEDGER_GROUP=H.LEDGER_GROUP, IU_SYS_TRAN_CD=H.IU_SYS_TRAN_CD, IU_TRAN_CD=H.IU_TRAN_CD, PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE)) 

 FROM %Table(JRNL_HIU_TAO) H, PS_%Bind(GL_JEDIT_WK_AET.RECNAME_JRNL_LN,NOQUOTES) L 

 WHERE H.PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE) 

  AND H.BUSINESS_UNIT=L.BUSINESS_UNIT 

  AND H.JOURNAL_ID=L.JOURNAL_ID 

  AND H.JOURNAL_DATE=L.JOURNAL_DATE 

  AND H.UNPOST_SEQ=L.UNPOST_SEQ;

After: The SQL statement after all meta-SQL expansion and hint insertion:

 INSERT /*+ APPEND */ INTO PS_JRNL_LIU_TAO5 (BUSINESS_UNIT , JOURNAL_ID , JOURNAL_DATE 

/* For the purposes of clarity, many columns in this column list have been omitted from this example. */

 , DEPTID , SCENARIO , BUSINESS_UNIT_IU) 

 SELECT /*+ LEADING(H) INDEX(L, PSFJRNL_LN) */ L.BUSINESS_UNIT 

 , L.JOURNAL_ID 

 , L.JOURNAL_DATE 

/* For the purposes of clarity, many columns in this column list have been omitted from this example. */ 

 ,L.DEPTID 

 ,L.SCENARIO 

 , H.BUSINESS_UNIT_IU 

 FROM PS_JRNL_HIU_TAO5 H 

 , PS_%Bind(GL_JEDIT_WK_AET.RECNAME_JRNL_LN,NOQUOTES) L 

 WHERE H.PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE) 

  AND H.BUSINESS_UNIT=L.BUSINESS_UNIT 

  AND H.JOURNAL_ID=L.JOURNAL_ID 

  AND H.JOURNAL_DATE=L.JOURNAL_DATE 

  AND H.UNPOST_SEQ=L.UNPOST_SEQ ;

Thanks

user3483096

I am asking more of How to Add SQL Hint if the existing standalone command CreateRowset+Fill

Ex:

Local Rowset &MYRS; 
Local String &EMPLID; 
 
&MYRS = CreateRowset(RECORD.PS_SOMEREC); 
&EMPLID = '8001'; 
 
&MYRS.Fill("where EMPLID = :1", &EMPLID); 

In the database, it resolves to "SELECT FIELD1, FIELD2, FIELD3.... FROM PS_SOMEREC where EMPLID = :1"

Not the How can I add a hint like this in the CreateRowset?

Select /hint+/ FIELD1, FIELD2, FIELD3.... FROM PS_SOMEREC where EMPLID = &EMPLID ;

Do I need to change to not use standalone rowset?

Thanks,

Aileen