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

Thanks for the input everyone,

while doing my research through this program someone who didn't understand
logicals made a bunch of keys at the top of the pgm that list 3 fields then
the next key has those same 3 plus another field. I haven't checked the
section of code in detail where these are used, but I assume if there are
keys set up like that someone made logicals that match, instead of making
one with 6 possible hits and also use it when you only have the first 2 or 3
fields. So I figure I can get rid of lots of logicals, but not sure yet.

My Manager has researched the usage by user session and some folks input
lots of data to search against. We have discussed grouping some fields so a
query would have say at least 4 or 5 pieces of data to go against, and could
group query builds upon this predetermined set of fields. And then educate
the users to scroll up or down to find their record, if it was not a direct
hit.

But when thinking of how to set up the SQL statement, is there a streamline
way to set it up? I need to check if input fields are populated and
organize how to build based on how many pieces of info I have to select
against. I am looking at how many groups of logicals I have based on
repeating key fields.



Sharon Strippoli
Pilot Freight Services
IT Dept.
Phone (610) 891-8113
sharonstrippoli@xxxxxxxxxxxxxxxxx
http://www.pilotdelivers.com




On Fri, Jul 29, 2011 at 9:55 AM, Morgan, Paul <Paul.Morgan@xxxxxxxxxxx>wrote:

Sharon,

Use SQL as others have recommended but you might not be able to get rid of
all those logicals. SQL would still need them depending on your selection
and sorting.

I'd make sure all those logicals don't do any select/omits so SQL can take
full advantage of them.

See if some of those logicals are redundant. If you have a logical keyed
on fielda and a logical keyed on fielda and fieldb you could delete the
redundant logical with the shorter key.

If you are doing some sort of summarization and your data doesn't change
that much you could pre load a work file with the summaries then run your
queries against the summarized work file instead of the full data set.

Do they really select on all those fields and other criteria? Do they
really select on some fields then refine their selection on other fields and
criteria? I'd think about adding some logging to see what selection and
other criteria is actually being used and how they 'drill down'.

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Sharon Strippoli
Sent: Friday, July 29, 2011 8:11 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Reduce large amount of logicals in SUBFL pgm,take in another
direction

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.