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

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.