Re: Embedded SQL in a Service Program versus in a Program

On 22-Jul-2014 14:45 -0500, Eileen@xxxxxxxxxx wrote:

I was working on improving performance and got some interesting
results. I have some embedded SQL which includes a PREPARE, DECLARE,
OPEN and FETCH. I have the exact same code in a program as in a
service program. I am logging the start and end time just for the
FETCH. I start the time just after the OPEN and end the time just
after the FETCH.

The Create Program (CRTPGM) from a *MODULE and the Create Service Program (CRTSRVPGM) with that one\same module specified for both, is more /same/ than merely the use of [effectively] identical source.

The FETCH in the Service program is taking about 800000 ms and the
FETCH in the program is taking about 1000 ms.
The time to call the service program 10 times was 9.2 seconds versus
.53 seconds for the program. Has anyone else seen this where the
actual SQL Fetch takes longer if it is in a service program?

The program and the service program are both Activation Group *CALLER
and compiled with the same SQL options.

Be sure to compare the Print SQL Information (PRTSQLINF) output for both the *PGM and the *SRVPGM, as verification of that claim.

I called the service program and program 10 times each. The ACCT#
gets replaced with each call. The service program and the program
both fetch the same number of rows.

WITH
t AS
( SELECT ROW_NUMBER()
OVER( ORDER BY ACCOUNTID ASC
, ORDERDATE DESC
, INVDATE DESC
, INVNUMBER ASC
) AS row_number
, ORDINQV.*
FROM ORDINQV

/* correlation name clause AS something best here */
/* change correlation identifier for the asterisk */

where ( ORDINQV.accountid = 'ACCT#')

/* correlation id is unnecessary here */

)
SELECT *
FROM t
WHERE row_number >= 1
FOR READ ONLY

OLAP is implicitly read-only, but the COMMIT\isolation may be active in either or both; the clause WITH NC could be coded explicitly to avoid any lack of consistency in creating the executable objects, or of course choose another isolation level as required. Additionally a clause effecting OPTIMIZE FOR N ROWS could be added to ensure the most basic optimization option is identical, even if a mismatch is possible between the executable objects if\when not careful.

Is there a parameter marker where the 'ACCT#' shows in the above quoted [but reformatted with comments added to the] query, or is the dynamic SQL SELECT being rebuilt with actual tokens; i.e. replacing the statement text within the program, versus the expected OPEN USING :HV syntax so the optimizer knows to attempt optimization for the parameter from the first access plan creation rather than only learning over time the possible need? Explicitly having been coded as parameterized query versus depending on the SQL to do so instead, implicitly, could be an issue for performance.