Re: Embedded SQL - performance question

PRTSQLINF shows this:

SELECT CEMLADS INTO : H FROM CUSEMLAD WHERE CCUSNR = : H AND CEMLPPS = : H
SQL4021 Access plan last saved on 05/17/11 at 10:52:26.
SQL4020 Estimated query run time is 0 seconds.
SQL4017 Host variables implemented as reusable ODP.
SQL4008 Index CUSEMLA1 used for table 1.
SQL4011 Index scan-key row positioning used on table 1.
SQL4006 All indexes considered for table 0.



On Thu, May 26, 2011 at 3:16 PM, Carel Teijgeler <coteijgeler@xxxxxxxxx> wrote:

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.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.