Re: Embedded SQL - performance question

Jeff,

Opening a cursor (and thus creating the result set) has its effects on
performance, especially on large files. And you want to retrieve only one
at the time.
And because you use host variables for the selection you have to generate a
new result set each time.

Have you used PRTSQLINF to see what access plan is stored with this service
programme and what estimated time it requres to run the SQL statement.

With regards,
Carel Teijgeler

*********** REPLY SEPARATOR ***********

On 26-5-2011 at 14:35 Jeff Crosby wrote:

All,

I have a procedure in a service program to retrieve a single record field

(an email address) from a file.  The relevant statement in the RPG is:

Exec SQL
Select CEMLADS
Into :EmlAds

From CUSEMLAD

Where CCUSNR = :PxCust and CEMLPPS = :PxEmlPurpose;

I then return :EmlAds to the calling program.

To do this for about 2000 records takes upwards of a minute. If instead I

define the file in the calling program and retrieve it via a

simple chain, these 2000 records are handled in 2-3 seconds. Why the big

discrepancy? If I run it in debug mode, it is using the access

path that already exists. Is my SQL statement flaky? Or is it the

overhead of calling the service program? Combination?

If it makes a difference, about 80% of the time no record will be found.