Re: Usage of Indexes and its Effects on an RPG Program

On Wed, 2005-12-14 at 14:46 -0800, Tony Carolla wrote:
> I have written many data warehouse routines, that churn through record
> counts in the hundreds of millions.  And because speed has been the
> principal goal (other than accuracy), I have conducted many tests using READ
> vs. READE.  I have found that the real difference between the two is that
> READE is doing a LOOKUP each time it runs, while READ is obviously only
> grabbing the next record.  
A lookup?  You mean SETLL?  


> If you use READE and specify BLOCK(*NO), versus
> using READ and specifying BLOCK(*YES), and overriding the block size to an
> I/O block of 128k, the differences are very real.
Of course, if you can do sequential I/O it will tear the wheels off of
indexed I/O.  But that's not the point here.  This test is to compare
indexed I/O using READ + test to indexed I/O using READE, and letting
OS/400 test.  

I merely want to see what sort of performance boost I would see if I
followed the advice I've seen here to replace READE with READ + test.  I
have found that the difference in not dramatic, but it is measurable.
The difference starts to get meaningful if block(*yes) is specified.

> 
> One of the key things to watch out for though, is if you use READE, and
> specify BLOCK(*YES), as Scott suggested for the test.  In this case, you are
> doing a DB LOOKUP, reading a block-size buffer of records, and tossing the
> entire block out at the start of each READE operation -- now that is the
> worst case, and will result in staggering performance differences.
Actually, you get a severity 10 compiler error RNF7092, indicating
block(*yes) will be ignored.  See Scott K's message on this earlier.

> 
> So I don't believe I have ever 'bashed' READE, it is good for certain
> operations, and for smaller volumes.
Uh, I don't mean you personally bashed it, just that the general
consensus over the past week or two *seems* to be that READE = slow,
READE + condition = fast, and I decided to test this assertion.  I don't
see much difference in the files I was using to test.  12.5%, though,
makes me consider using it more, but not necessarily going back to all
programs and making this change.

>   You just have to consider what the
> goal of the program is.  
Always.

> I have gone so far as to study the file being read,
> and figure out what the average/max/min records per account is, and set the
> block size accordingly, to read all of account's records in one READ.
If SETLL + READ can block successfully,  SETLL + READE should also be
able to do the same.  That it cannot is an implementation deficiency, in
my humble opinion.  

> 
> Bottom line -- If you are asking about performance, you are going to get
> advice from people like me that are willing to go the extra mile for those
> few seconds/minutes.
Do you write it all in RPG, or perhaps some MI ?


Regards,
Rich