RE: Reduce large amount of logicals in SUBFL pgm, take in another direction

Another option is to investigate why there are 80 logicals. Is there a large volume simply due to the number of fields that could be sorted per the user? Or is there Select/Omits being done as well? Some Select/Omits are fine, but if you find that for every key you need 4 logicals because of different select/omits, you could always try to fit those into the key list?

If you have 80 logicals because there are 80 different key lists, I would think you would then be required to have 80 different indeces. Someone correct me if I'm wrong.

-Kurt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Friday, July 29, 2011 8:36 AM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm, take in another direction

I'd also look at SQL..

Assuming you have multiple users running the program at once, the problem with the work file idea is that you'd be repeating the same work for multiple users. Whereas with SQL, any work the system does for one user (ex. temp index @ v5r4+) could be shared by another user with a similar query.

However, note that SQL access doesn't support "position to" very well...it can be done, but it's much, much more complex... SQL is better at filtering. It sounds like you might already be filtering instead of the more common "position to" so this may not be a factor.

Also, you may find that instead of 80 LF's you need 37 or more indexes....still that'd be an improvement.

Lastly, you may find that the current structure of the data doesn't lend itself to better performance. Specifically, if the data has been denormalized to support LF creation....the lareg amounts of repeated data hurts SQL performance. You might need to restructure into a "star schema" of the sort usually used by data warehouses to support ad-hoc queries.

HTH,
Charles

On Fri, Jul 29, 2011 at 8:11 AM, Sharon Strippoli <sharonstrippoli@xxxxxxxxxxxxxxxxx> wrote:

Good morning,

new job and given the assignment to revamp a screen pgm that the whole
company uses to make it more efficient, less CPU.

There are 12 different fields a user can fill in to search for the
info they want.  Plus there are fields they can check off for other
criteria.  Total about 25 different fields on the screen.

Currently, there are about 80 logicals that are used for all the
different combinations of fields to search on.  The first SUBR of the
pgm does various validations against all the possible fields, then
depending on that result does a SETLL for the appropriate logical.  
Then a second SUBR checks the fields again and depending on that does
a READ, READE or READP against the logical that was staged.

They want to select the data the first time through to a type of work
file, then as more information is filled in by the user, query or read
against that work file, drilling down further for each field that is
populated by the user.  They want to reduce CPU and the amount of logicals.
I was thinking of doing a query against the work file as it gets
drilled down.

Wondering how the super users that use this forum would take this on.  
Any input would be appreciated.


Thanks.


Sharon





Sharon Strippoli
Pilot Freight Services
IT Dept.
Phone (610) 891-8113
sharonstrippoli@xxxxxxxxxxxxxxxxx
http://www.pilotdelivers.com
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.