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

I have not been in on the discussion about READE, so this may have been said 
before, or in this thread. 

1. Access using an index usually has to do 2 reads to get a record. One is to 
the index, the other to the physical over which the index is built. SQL can 
take advantage of an index-only access method if all the data being requested 
is in the index, but I don't think native can do this. Could be wrong.

2. Keyed access is more often random than sequential. Unless things are located 
in key order. In the latter case, because records are brought in and exist in 
memory, it is closer to sequential access. Not all the way, however, IMO.

3. It might be worth trying to use expert cache. This is done, for those who 
might not have heard of this, by changing a setting on a pool in WRKSYSSTS, in 
the "Paging option" column. What happens is, the system records in a sliding 
window the kinds of database access - sequential, mostly sequential, mostly 
random, random - and changes the size of the page brought in by each I/O. 
Random accesses SHOULD use smaller pages, as the overhead to bring in the extra 
stuff that is not in the record being read. Sequential benefits from larger 
blocks because of the reduction in physical disk access.

Seems to me the solution to SETLL and READ would benefit even more from using 
the *CALC setting for expert cache - these blocks can go as high as 128KB, 
whereas the default for standard PFs is 4KB and for SQL tables is 16KB (I 
think).

Vern

-------------- Original message -------------- 
From: "Brian Piotrowski" <bpiotrowski@xxxxxxxxxxxxxxx> 

> Thanks, Greg. I suspected that the READE was the problem. I have posed 
> this question in RPG-400 along with a snippet of code that appears to be 
> the friction in our system. 
> 
> Brian. 
> 
> -----Original Message----- 
> From: Fleming, Greg (ED) [mailto:GFLEMING@xxxxxxxxxxxxxxxxxxxx] 
> Sent: Wednesday, December 14, 2005 10:50 AM 
> To: Midrange Systems Technical Discussion 
> Subject: RE: Usage of Indexes and its Effects on an RPG Program 
> 
> If you are using READE, then I think your PF must be keyed, which is 
> just as good as having a logical file. Check the source on your PF. 
> 
> As far as indexes go, if you are creating a new logical file, you have 
> to actually name that logical file on the F spec in your RPG program and 
> refer to it in your C specs. 
> 
> If you are talking about an SQL index, that's another matter with which 
> I'm not very familiar, but I think that Embedded SQL would probably pick 
> it up right away, but your regular RPG program won't use it at all. It 
> only uses what you tell it to use. 
> 
> On a wider note, it sounds like you are trying to improve performance. 
> I've been preaching here a couple times in the last few weeks about the 
> hazards of using READE over a large data set, since it doesn't read from 
> a block of records in memory, but actually hits the file for every read. 
> If performance is an issue, much better to setLL on your key, then use 
> READ, and compare your records to your key fields with a few more lines 
> of code. It's uglier, but it goes faster. 
> 
> 
> 
> >-----Original Message----- 
> >From: midrange-l-bounces+gfleming=evergladesdirect.com@xxxxxxxxxxxx 
> >[mailto:midrange-l-bounces+gfleming=evergladesdirect.com@xxxxxxxxxxxx] 
> On 
> >Behalf Of Brian Piotrowski 
> >Sent: Wednesday, December 14, 2005 10:38 AM 
> >To: Midrange Systems Technical Discussion 
> >Subject: Usage of Indexes and its Effects on an RPG Program 
> > 
> >Hi All, 
> > 
> > 
> > 
> >I have a physical file that has 20 fields and contains over 2,000,000 
> >records. In the current RPG program, it does a SETLL/READE on the data 
> >based on a key (which is the first five fields in the record). 
> However, 
> >the READE command does not have an associated logical file - it is 
> using 
> >the straight PF (this program was written before my time, and I suspect 
> >does not run as efficiently as it should). 
> > 
> > 
> > 
> >I went into the system (via Navigator) and added an index to this PF of 
> >the fields that are in the key. Now my question: when a new index is 
> >created on a physical file, will an RPG program begin using it 
> >automatically, or does the program need to be made aware of the index? 
> > 
> > 
> > 
> >Thanks! 
> > 
> > 
> >Brian. 
> > 
> > 
> > 
> >-=-=-=-=-=-=-=-=-=-=-=-=-=- 
> > 
> >Brian Piotrowski 
> > 
> >Specialist - I.T. 
> > 
> >Simcoe Parts Service, Inc. 
> > 
> >PH: 705-435-7814 
> > 
> >FX: 705-435-6746 
> > 
> >-=-=-=-=-=-=-=-=-=-=-=-=-=- 
> > 
> > 
> > 
> >-- 
> >This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list 
> >To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> >To subscribe, unsubscribe, or change list options, 
> >visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> >or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> >Before posting, please take a moment to review the archives 
> >at http://archive.midrange.com/midrange-l. 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
> 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>