Statement
Compilation
When an SQL
statement is issued, SQL Server (technically SQL Manager) checks if the
statement (an execution plan for the query) is already available in the SQL
Server cache. If not, the query must be compiled fully. During the compilation
phase SQL Server must check the statement syntactically and semantically,
prepare a sequence tree, optimize based on statistics and other criteria, and
generate an execution plan. This is referred to as a compile, which is
expensive in terms of CPU usage.
Compilation
happens when SQL Server parses a query and cannot find an exact match for the
query in the procedure cache.
This occurs
due to the inefficient sharing of SQL statements, and can be improved by using
bind variables (parameters) instead of literals in queries. The number of hard
parses can be identified with an Application Engine trace (128).
Use of
Bind Variables
The number
of compiles can be reduced to one per multiple executions of the same SQL
statement by constructing the statement with bind variables instead of
literals. Most of the PeopleSoft programs written in Application Engine, SQR,
and COBOL have taken care to address this issue.
Application
Engine ReUse Option
Application
Engine programs use bind variables in their SQL statements, but these variables
are specific to PeopleSoft. When a statement
is passed to the database, Application Engine sends the statement with literal
values. To indicate to the Application Engine
program to send the bind variables, enable the ReUse option in the
Application Engine step containing the statement
that needs
to use the bind variable.
Restrictions
on Enabling the ReUse Option
It is
acceptable to enable ReUse if %Bind is used to supply a value to a
column in a WHERE predicate, SET clause, or INSERT VALUES list.
For
example:
UPDATE
PS_PF_DL_GRP_EXEC
SET
PF_ODS_STATUS = 'C',
PROCESS_INSTANCE
= %Bind(PROCESS_INSTANCE)
WHERE
PF_DL_GRP_ID = %Bind(PF_DL_GRP_ID)
AND
PF_DL_ROW_NUM = %Bind(PF_DL_ROW_NUM)
Do not
enable ReUse if %Bind is used to supply a column name or portion of a
table name.
For
example:
SELECT
DISTINCT KPI_ID
, CALC_ID
, ' '
,0
,0
,KP_CALC_SW
,KP_OFFCYCLE_CALC
FROM
PS_%Bind(KP_CALC_AET.KP_KPI_LST1,NOQUOTES)
%Bind(EPM_CORE_AET.FACT_TABLE_APPEND
,NOQUOTES)
WHERE
LOOP_CNT = %Bind(KP_CALC_AET.LOOP_CNT)
AND
LOOP_PROGRESSION='B'
Do not
enable ReUse if %Bind appears in the SELECT list.
For
example:
SELECT
DISTINCT
%Bind(EPM_CORE_AET.PROCESS_INSTANCE)
,
%Bind(EPM_CORE_AET.ENGINE_ID)
,
%CurrentDateTimeIn
, 10623
, 31
,
'GL_ACCOUNT'
, ' '
, ' '
, ' '
, ' '
,
A.MAP_GL_ACCOUNT
, ' '
, ' '
, ' '
, ' '
, 'LEDMAP_SEQ'
FROM …………
Do not
enable ReUse if %Bind is being used to resolve to a value other than a
standard Bind value and the contents of the Bind will change each time the
statement executes.
For
example:
%Bind(GC_EQTZ_AET.GC_SQL_STRING,NOQUOTES)
In this
case, the SQL is different each time (at least from the database perspective)
and therefore cannot be “reused.”
If NOQUOTES
modifier is being used inside %Bind, there is an implied STATIC. For dynamic
SQL substitution, the %Bind has a CHAR field and NOQUOTES to insert SQL rather
than a literal value. If you enable ReUse, the value of the CHAR field is
substituted inline, instead of using a Bind marker (as in :1, :2, and so on).
The next time the same Application Engine action executes, the SQL that it
executes will be the same as before, even if the value of a static bind has
changed.
For
example:
INSERT INTO
PS_PF_ENGMSGD_S
%Bind(EPM_CORE_AET.TABLE_APPEND,NOQUOTES)
(PROCESS_INSTANCE
, ENGINE_ID
,
MESSAGE_DTTM
,
MESSAGE_SET_NBR
,
MESSAGE_NBR
,
FIELDNAME1
, FIELDNAME2
,
FIELDNAME3
,
FIELDNAME4
,
FIELDNAME5
, FIELDVAL1
, FIELDVAL2
, FIELDVAL3
, FIELDVAL4
, FIELDVAL5
,
SOURCE_TABLE)
……………
Use the
%ClearCursor function to recompile a reused statement and reset any STATIC
%Bind variables.
Application
Engine Bulk Insert Option
By
buffering rows to be inserted, specifying a ReUse statement value of Bulk
Insert can provide considerable performance boost. PeopleSoft Application
Engine offers this non-standard SQL enhancement for Microsoft SQL Server. This
feature improves performance only when an SQL INSERT statement is called
multiple times in the absence of intervening COMMIT statements.
PeopleSoft
Application Engine ignores the Bulk Insert option in the following
situations:
• The SQL
is not an INSERT statement.
• The SQL
is other than an INSERT/VALUES statement that inserts one row at a time. For
example, the following statements are ignored: INSERT/SELECT, UPDATE, and
DELETE.
• The SQL
does not have a VALUES clause.
• The SQL
does not have a field list before the VALUES clause.
In these
situations, PeopleSoft Application Engine still executes the SQL; it just does
not take advantage of the performance boost associated with Bulk Insert
