Re: Stored procedure bottlenecks...

[Robert Rogerson <rrogerson@xxxxxxxxxxx>]

Thanks Vern, Justin and Alan,

I did try VE but it doesn't return anything.  (Everything is blank 
although it does say Status: Complete - no results )

I call the sp as follows:

call RESERVE_DISTRIBUTION_BATCH('82411208035520160727');

The sp is defined/created as

CREATE PROCEDURE QS36F/RESERVE_DISTRIBUTION_BATCH (
    IN BATCHID CHAR(20) )
    DYNAMIC RESULT SETS 1
    LANGUAGE RPGLE
    SPECIFIC QS36F/RESERVE_DISTRIBUTION_BATCH
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    EXTERNAL NAME 'CANLIB/OE7025'
    PARAMETER STYLE SQL ;

The external program OE7025 has no sql in it other than to populate the 
resultset to be returned.  Would this be why VE shows nothing.

But I don't think the issue is sql related.  While waiting for a 
response to my original email I ran the program from the command line 
and it took virtually the same time to complete.  So I think the problem 
is purely RPG related.  It's oldish code so maybe I can see why its slow 
in debug.

Thanks for the help.

Rob



On 7/28/2016 1:07 PM, Vernon Hamberg wrote:
> Hi Rob
>
> Run it through Visual Explain to see if it needs some indexes - that's 
> the first thing I'd do.
>
> VE is part of Run SQL statement in IBM i Access Navigator
>
> You might want to come back with some results and ask more questions.
>
> HTH
> Vern
>
> On 7/28/2016 11:47 AM, Robert Rogerson wrote:
> > Hi All,
> >
> > I have an external stored procedure calling an SQLRPGLE program. To 
> > process 250 rows it is taking about 12 seconds.
> >
> > Can anyone suggest how I can see if there are any bottlenecks that 
> > are slowing down the processing.
> >
> > Thanks,
> >
> > Rob