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

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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.