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

- which did you run first?
I have done both ways

- did you run CLRPOOL and/or SETOBJACC to get the table out of memory?
Level playing field and all
I did not but I have run this multiple times on mulitple days and
always get the same results.


- it might be helpful to run a monitor over this to see what the
optimizer is doing
I did turn on a SQL Performance Monitor and could not see any
difference except the run times

- when you say "called the service program", do you mean you called the
procedure? I assume it was prototyped
Sorry, I should have said I called the procedure - I have done it
both as a
prototyped procedure and also as a stored procedure. Same results
with both.



Eileen Beckwith
Vermont Information Processing
eileen@xxxxxxxxxx



From: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)"
<rpg400-l@xxxxxxxxxxxx>
Date: 07/22/2014 03:59 PM
Subject: Re: Embedded SQL in a Service Program versus in a Program
Sent by: "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx>



Eileen

A couple thoughts

- which did you run first?
- did you run CLRPOOL and/or SETOBJACC to get the table out of memory?
Level playing field and all
- it might be helpful to run a monitor over this to see what the
optimizer is doing
- when you say "called the service program", do you mean you called the
procedure? I assume it was prototyped

There is also a statement cache if you are on more recent version of the
OS, running the same thing multiple times can end up with less
optimization time, although that should not be 9 seconds.

The first 2 items are related to that.

HTH
Vern

On 7/22/2014 2:45 PM, 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 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.

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 where ( ORDINQV.accountid = 'ACCT#'))
SELECT * FROM t WHERE row_number >= 1 FOR READ ONLY

Eileen Beckwith
Vermont Information Processing
eileen@xxxxxxxxxx


This email and any files transmitted with it are confidential and

intended

solely for the use of the individual or company to whom they are
addressed. Do not disclose, distribute, or copy this email to others
outside your company. If you have received this email in error, please
notify the sender immediately and delete this email from your system.