Re: Embedded SQL - performance question

On 26-May-2011 14:12 , Joe Pluta wrote:

On 5/26/2011 3:16 PM, CRPence wrote:

I expect that SQL should be much faster even on a poorly
performing system; almost as fast as the RLA, esp. if the same
index is utilized for both the SQL and RLA.

I've never found a single-record fetch to be anywhere near as fast
as RLA, and I did exhaustive tests; SQL doesn't catch up until the
block size is upped to about 100 records. I could rerun all those
tests, but until someone shows me some evidence SQL has caught up, I
have no reason to repudiate the old data.

2000 single-row retrieval attempts with 39 actual values found took ~1.13sec; function resolution had already been activated in my job, and ~.2sec longer in a new job. The SELECT INTO was defined in a LANGUAGE SQL stored procedure [ACTGRP(*CALLER)] where the SQL TABLE was created with a PRIMARY KEY CONSTRAINT index on the cCusNbr and cEmlTyp columns, returning the EmlAdr varchar(45) result or the NULL value indicator when the key value was not located.

<code>

> rclactgrp emladr
> SETOBJACC OBJ(EMLADRLIST) OBJTYPE(*FILE) POOL(*PURGE)
> call getemladrs /* SQLRPGLE created ACTGRP(EMLADR) */
DSPLY 2011-05-27-04.59.51.199744
DSPLY 2011-05-27-04.59.52.323112

//data GETEMLADRS endchar('//ENDGETEML')

D E S 45A inz('*N') VARYING
D T S 2A inz('PF')
D N S 7P00 inz(600)
D EI S 3i00
D T0 S 26A
D T1 S 26A

c/free
Exec SQL
values current timestamp into :t0 ;

// file has 39 matches between 601 and 2600 with EmlTyp='PF'
DoW N<=2600;
N=N+1;
Exec SQL
call getemladr (:E :EI , :N, :T) ;
if ei<>0;
E='*N';
else;
// DSPLY E ;
endif;
enddo;

Exec SQL
values current timestamp into :t1 ;

DSPLY T0;
DSPLY T1;

*inlr = *on;

//ENDGETEML

</code>

Regards, Chuck