Re: Understanding User Indexes from RPG

 Thanks for reference you sent, I'll need to give that stuff
consideration 
for yet another project.

My current use was probably a perfect use of both performance and
program 
flexibility.

The application involved some add-in software we wrote to provide access
to 
huge proprietary database that is refreshed monthly by the purveyor of
the 
data.  The data is generic across a particular industry.  The
application is 
use heavily by the majority of users all day long

Some users of the data create additional data which they want to append
to 
the generic data when it appears on the screen in the form a subfile
which 
may have from 1 to 50 or more pages.

The custom data resides in disk records but the keys are placed in a
user 
index. If there is a hit in the user index then a Chain is made to disk
file 
for any number of sequence records tied to the original key.

Since the possible generic data might come from millions of possible 
records, it seemed like such a waste of time to "chain" the custom data
for 
hundred of records for each display only to get a hit 1% of the time or 
less.

So I started by loading an internal table at load time into an array of
2000 
items. However once the table had 500+ records, a lookup started to get 
slower and while it was quicker than going to disk it was getting very
slow.

Another problem is the feature caught on and soon requests came in to 
increase the size of the array. That mean a program change to an already

slow process.

Maintenance to the custom data is only a few times a month so the User
index 
is just rebuilt once in awhile, and the issues you mention about its DB2

differences don't really matter in this use of the indexes.

The speed difference of a user index was a minimum 5 times faster than
the 
table lookup if the table hit was in the 300 to 700 entry range and the
user 
index speed stayed constant while the table lookup speed got really bad
at 
6000 entries.

I can't recall any program performance change I've made that had such a 
positive impact on observable user response time.

Thanks again for the reference source.

Steve

In addition to what's shown there, keep in mind that you won't be
getting a lot of the support that can appear as "overhead" in DB2.

Tom Liotta